SQL Server interview questions covering select, filtering, sorting & built-in functions.
Open a question for the full answer, code and interview guidance.
SELECT retrieves data from one or more tables, views, table-valued functions, or derived result sets. A production query should normally request only the columns actually needed rather than using SELECT *. Selecting fewer columns reduces unnecessary data transfer, can reduce memory and I/O requirements, and makes application contracts clearer.
SELECT * returns every column from the source. It can transfer unnecessary data, make application code dependent on schema changes, prevent some covering-index opportunities, and make the intent of a query less clear. It is convenient for quick investigation, but application queries should normally list required columns explicitly.
WHERE filters rows before they are returned or processed by later logical query stages. It accepts a predicate that evaluates according to SQL's three-valued logic. WHERE is used with SELECT, UPDATE, and DELETE. An incorrect predicate in UPDATE or DELETE can modify far more data than intended, so it is especially important in production operations.
Comparison operators compare expressions and are commonly used in WHERE, HAVING, JOIN conditions, CASE expressions, and other predicates. Common operators include =, <>, !=, >, <, >=, and <=. SQL Server supports both <> and != for 'not equal', although <> is the standard SQL form. Comparisons involving NULL generally produce UNKNOWN rather than TRUE or FALSE, so NULL should normally be tested with IS NULL or IS NOT NULL.
AND, OR, and NOT are logical operators used to combine or negate predicates. AND requires both conditions to be true. OR requires at least one condition to be true. NOT reverses a Boolean predicate. Operator precedence matters: NOT is evaluated before AND, and AND before OR. Parentheses should be used when necessary to make business logic explicit and prevent subtle filtering errors.
BETWEEN tests whether an expression falls within an inclusive range. Both boundary values are included. For date/time filtering, BETWEEN can be risky when the upper boundary is intended to represent an entire day but the column also contains time. A half-open range such as >= start and < next day is often safer for DATETIME2 values.
IN tests whether an expression matches any value in a specified list or subquery. It often provides clearer syntax than writing many OR comparisons. For very large or dynamic sets, a table, temporary table, table-valued parameter, or join may be more appropriate than constructing a huge literal IN list.
LIKE performs pattern matching on character data. The percent sign (%) represents zero or more characters, while underscore (_) represents one character. Whether comparisons are case-sensitive depends primarily on the applicable collation. Patterns beginning with a wildcard, such as '%camera', often make efficient index seeking more difficult than prefix searches such as 'camera%'.
The % wildcard matches zero or more characters. The _ wildcard matches exactly one character. For example, 'A%' matches values beginning with A regardless of the remaining length, while 'A_' requires exactly one character after A. Bracket expressions can also be used in SQL Server LIKE patterns for character sets and ranges.
Use IS NULL to find NULL values and IS NOT NULL to find non-NULL values. Expressions such as Column = NULL or Column <> NULL do not behave like ordinary equality comparisons because comparisons with NULL generally evaluate to UNKNOWN. Understanding this behavior is important when writing filters, joins, CASE expressions, and anti-join queries.
ORDER BY sorts the final query result. ASC sorts ascending and is the default; DESC sorts descending. Without ORDER BY, SQL Server does not guarantee row order, even if results appear consistently ordered during testing. For deterministic paging, ORDER BY should use a key or combination of columns that provides a stable ordering.
DISTINCT removes duplicate rows from the selected result based on the complete set of expressions in the SELECT list. It is useful when uniqueness is genuinely required, but it should not be added merely to hide duplicate rows caused by an incorrect join. DISTINCT can require additional sorting or hashing work, so the underlying query logic should first be correct.
TOP limits the number or percentage of rows returned or affected. When TOP is used to obtain a meaningful first or last set of rows, it should normally be paired with ORDER BY. TOP can also use PERCENT and WITH TIES. WITH TIES can return additional rows that have the same ORDER BY value as the last selected row.
OFFSET skips a specified number of rows and FETCH NEXT returns the next number of rows. It requires ORDER BY and is commonly used for application pagination. For very large page numbers, offset pagination can become expensive because SQL Server may still need to process many preceding rows. Keyset or seek pagination can be more efficient for large datasets when the UI and ordering requirements allow it.
An alias gives a temporary name to a column expression or data source within a query. Column aliases improve result readability, while table aliases make joins and long object names easier to reference. AS is optional for many aliases, but using clear, short aliases improves maintainability.
CASE is a conditional expression that returns a value based on one or more conditions. It can be used in SELECT, ORDER BY, aggregates, updates, and other expressions. SQL Server supports searched CASE, which evaluates Boolean conditions, and simple CASE, which compares one expression against multiple values.
ISNULL(check_expression, replacement_value) is a SQL Server function that returns the replacement when the first expression is NULL; otherwise it returns the original value. It is convenient for SQL Server-specific code, but data-type behavior matters because the returned type is generally based on the first argument. It should not be used blindly in predicates because wrapping an indexed column in a function can affect SARGability.
Both can provide fallback values for NULL, but they are not identical. ISNULL is a SQL Server-specific function with two arguments. COALESCE is a standard SQL expression and can accept multiple arguments, returning the first non-NULL expression. Their data-type precedence, nullability metadata, and evaluation semantics can differ. Therefore, they should not be considered perfectly interchangeable merely because simple examples return the same value.
Frequently used string functions include LEN, DATALENGTH, UPPER, LOWER, LTRIM, RTRIM, TRIM, LEFT, RIGHT, SUBSTRING, REPLACE, CHARINDEX, CONCAT, STRING_AGG, and others. LEN returns the number of characters excluding trailing spaces, while DATALENGTH returns the number of bytes used by the expression. Knowing that distinction is useful in troubleshooting storage and string-length issues.
Common date/time functions include GETDATE, SYSDATETIME, GETUTCDATE, SYSUTCDATETIME, DATEADD, DATEDIFF, DATEPART, DATENAME, EOMONTH, and DATEFROMPARTS. GETDATE returns the current server date/time as DATETIME, while SYSDATETIME provides higher precision as DATETIME2. Applications that operate across time zones commonly store timestamps in UTC and convert them appropriately at system boundaries.