T-SQL interviews should reveal whether a candidate can predict what a SQL Server batch will do, not merely recognize familiar query syntax. Small choices about variable assignment, implicit conversion, window ordering or transaction handling can change a result or leave an application in an unexpected state. Recruiters should ask candidates to explain the rows produced, the assumptions that make them correct and the behavior when those assumptions fail.
These 100+ T-SQL Interview Questions and Answers cover 120 questions across beginner, intermediate and experienced levels. The focus is Transact-SQL development: batches, types, queries, procedures, data changes, errors and execution behavior. Examples progress from small expressions to multirow operations and concurrent application scenarios, giving interviewers ways to assess language fluency and practical reasoning.
How can recruiters distinguish genuine T-SQL reasoning from a copied query that happens to return the expected rows?
Ask the candidate to predict the result for a small dataset, then introduce a duplicate key, a NULL, a tied timestamp or a failed statement. A strong explanation identifies which part of the query establishes correctness and which guarantee belongs to a constraint or transaction. Follow-up changes also reveal whether the candidate can reason about batch scope and error paths rather than rely on a memorized pattern.
Sherlock AI helps hiring teams assess signals of potential AI cheating, deepfakes, impersonation and unauthorized assistance during remote interviews. Combine those signals with query walkthroughs and practical questions about expected rows and failure cases. Use them as context for a fair review, alongside demonstrated understanding, rather than as conclusive proof of misconduct.
T-SQL Interview Questions
Choose questions that reflect the role’s query and procedure responsibilities. Give candidates the relevant schema, data and transaction assumptions, and ask them to explain their expected result before changing the example. The complete bank appears first, followed by identically numbered answers.
T-SQL Interview Questions for Beginners
Check the candidate’s command of batches, variables, types and basic query meaning. Ask for a concrete result and a simple counterexample, especially around NULL, conversion and ordering.
What is Transact-SQL, and how does it relate to SQL?
What is a T-SQL batch, and what does GO actually do?
Why should T-SQL statements be terminated with semicolons?
Why use schema-qualified object names such as dbo.Orders?
When are square brackets needed around T-SQL identifiers?
How do you declare and initialize a local variable?
How does SET variable assignment differ from SELECT assignment?
Why should SELECT variable assignment not be used to concatenate rows?
How does NULL affect comparisons in a WHERE clause?
How do AND and OR interact with NULL predicates?
Why should string lengths be explicit in declarations and casts?
Why use the N prefix with Unicode string literals?
How can implicit data-type conversion cause a query to fail?
Why does integer division sometimes produce an unexpected result?
What do decimal precision and scale mean in T-SQL?
How do datetime2 and datetimeoffset serve different needs?
How can you avoid ambiguous date strings in T-SQL?
Why use a half-open interval when filtering a day of timestamp data?
What does CASE do, and what does it not do?
How do COALESCE and ISNULL differ in T-SQL?
When is TRY_CAST useful, and what are its limits?
How does NULLIF help express a zero-denominator rule?
Why is TOP without ORDER BY not a reliable selection rule?
How does TOP WITH TIES change the result?
How do INNER JOIN and LEFT JOIN differ when a match is missing?
Why can a WHERE filter undo the effect of a LEFT JOIN?
How do WHERE and HAVING differ in a grouped query?
How do COUNT(*), COUNT(column) and COUNT(DISTINCT column) differ?
Why can DISTINCT hide a join problem?
What does EXISTS test, and why is its SELECT list usually unimportant?
Why is NOT IN dangerous when its subquery can return NULL?
How do UNION and UNION ALL differ?
What is a common table expression, and how long can it be referenced?
Why should INSERT statements name their target columns?
What does the OUTPUT clause provide during a basic data change?
How do you make a basic UPDATE target the intended rows?
What is a stored procedure parameter, and how is a default used?
How do result sets, OUTPUT parameters and procedure return codes differ?
What does SET NOCOUNT ON change in a stored procedure?
Why should an IF or ELSE branch use BEGIN...END when it contains multiple statements?
T-SQL Interview Questions for Intermediate
Use these questions to explore multirow query patterns and reusable procedures. Focus on ties, empty inputs, grouping boundaries and the effect of a failed statement on the surrounding operation.
How would you choose exactly one latest row per customer deterministically?
How do ROW_NUMBER, RANK and DENSE_RANK differ when scores tie?
Why should a running total specify ROWS and a unique order?
Why can LAST_VALUE return the current row instead of the last value in a partition?
How would you calculate a difference from the previous observed row?
What changes when LAG or LAST_VALUE uses IGNORE NULLS?
How would you find the top two orders for every customer while retaining customers with no orders?
When can CROSS APPLY make a query clearer than repeating an expression?
How would you filter on a window-function result in T-SQL?
How would you distinguish a moving average over rows from one over calendar days?
How would you build a recursive CTE to walk an employee hierarchy?
Why can a recursive CTE fail even when the anchor and recursive expressions look similar?
When should a repeatedly reused intermediate result become a temporary table?
How do a local temporary table and a table variable differ in a stored procedure?
How would you produce detail subtotals and a grand total in one grouped query?
When is conditional aggregation simpler than PIVOT?
What determines the grouping columns of a T-SQL PIVOT?
How would you create an ordered comma-separated list with STRING_AGG?
How do you preserve token positions when using STRING_SPLIT?
How would you parse a JSON array into typed relational rows?
When should you use JSON_VALUE, JSON_QUERY or OPENJSON?
How would you expand nested JSON arrays while preserving the parent identifier?
How do DATETRUNC(month) and EOMONTH differ when building monthly reporting keys?
Why does DATEDIFF(day) not measure elapsed 24-hour periods?
What happens when DATEADD adds a month to a date near month-end?
How would you prevent an UPDATE with a join from using an ambiguous source row?
How would you capture old and new values from an UPDATE?
Why is SCOPE_IDENTITY usually safer than @@IDENTITY after a single-row insert?
How do you capture the affected-row count reliably after DML?
How would you update a bounded oldest batch rather than arbitrary TOP rows?
How would you pass a set of IDs into a stored procedure without building an IN string?
How do named and positional procedure arguments affect call compatibility?
Why should stored-procedure parameters have explicit types and a clear NULL contract?
How does an inline table-valued function differ from a multi-statement table-valued function?
When should reusable logic remain a procedure rather than become a user-defined function?
What does TRY...CATCH catch, and what should you preserve when rethrowing?
How do @@TRANCOUNT and XACT_STATE answer different transaction questions?
Does committing an inner transaction make its changes durable independently?
When is rolling back to a savepoint useful, and what limits it?
What is a basic TRY...CATCH transaction pattern for a routine that owns its transaction?
T-SQL Interview Questions for Experienced
Probe the candidate’s judgment about dynamic statements, concurrent changes, transaction ownership and parameter-dependent execution. Ask which assumptions can be enforced and what evidence would justify a change.
Why does using sp_executesql not automatically make dynamic SQL safe?
How would you safely support a user-selected sort column in dynamic SQL?
What edge cases matter when using QUOTENAME?
How do variable and temporary-table scopes affect dynamic SQL?
How can dynamic SQL return a scalar value to its caller?
How would you investigate a parameterized query that is fast for some values and slow for others?
What does Parameter Sensitive Plan optimization change in SQL Server 2022?
When is OPTION (RECOMPILE) a reasonable choice?
Why can copying a parameter into a local variable make performance less predictable?
Why can a weekday filter change results between sessions?
How would you validate a string length when trailing spaces and Unicode matter?
What should you check when a query behaves differently under another collation?
Why is IF NOT EXISTS followed by INSERT vulnerable to a race?
How can an update-then-insert pattern protect an absent key under concurrency?
What makes a retried T-SQL operation idempotent?
Why can a sequence-generated business number have gaps after rollback?
Does rolling back a transaction restore local variables and table variables?
How would you expose a stable custom error contract with THROW?
Why can TRY...CATCH miss an error that appears inside its TRY block?
What contract must a procedure satisfy when its results are captured with INSERT...EXEC?
What should you review before using MERGE for synchronization?
Why must a DML trigger handle multiple rows?
Why can stored-procedure SET options differ from the caller’s current settings?
How can OFFSET pagination produce missing or repeated rows while data changes?
How would you paginate by a nonunique timestamp deterministically?
Does ORDER BY inside a view guarantee the order returned to its consumers?
How can an unqualified subquery column silently bind to an outer query?
What should you verify before assuming a scalar UDF will be inlined?
How can replacing a scalar subquery with APPLY change cardinality?
How would you design a restartable set-based update in batches?
How can rowversion guard an optimistic update, and why is it not a timestamp?
How do EXCEPT and INTERSECT treat duplicates and NULL values?
When is IS DISTINCT FROM useful for nullable comparisons?
How would you delete duplicate rows while preserving a deterministic survivor?
Why can concatenation truncate before assignment to nvarchar(max)?
Why should a query not depend on predicate evaluation order to avoid conversion errors?
Why can dynamic SQL inside a procedure fail permissions checks that static SQL passes?
How would you validate a query rewrite that is faster but returns slightly different results?
How would you debug a complex T-SQL query that returns the wrong total?
How can error logging accidentally hide the original T-SQL failure?
T-SQL Interview Questions and Answers
Examples target the SQL Server Database Engine with a SQL Server 2022-or-later baseline and database compatibility level 160 unless a question states otherwise. Newer or feature-specific behavior is qualified separately. Small table variables and temporary fixtures make the examples self-contained where useful; named application tables illustrate a stated schema. A query’s row order is guaranteed only by the appropriate final ORDER BY, and execution plans can vary without changing required results.
T-SQL Interview Questions and Answers for Beginners
These answers explain foundational Transact-SQL behavior through small examples. Distinguish language semantics from client-tool commands, and make lengths, numeric types and expected rows explicit.
1. What is Transact-SQL, and how does it relate to SQL?
Transact-SQL, usually called T-SQL, is Microsoft’s SQL dialect for querying and programming SQL Server and related services. Alongside relational statements, it provides variables, procedural control flow, error handling and programmable objects such as stored procedures. SQL concepts transfer across products, but syntax and behavior are not automatically portable.
For example, a SQL Server query can use TOP (5), while code copied from another database may use LIMIT 5, which is not the equivalent T-SQL syntax. I would identify the target engine, version and database compatibility context before selecting features. A script that works on SQL Server is not necessarily supported unchanged by every Microsoft analytics service that accepts some T-SQL.
2. What is a T-SQL batch, and what does GO actually do?
A batch is a group of statements submitted to SQL Server together. GO is a batch separator recognized by tools such as SSMS and sqlcmd; it is not a statement executed by the database engine. Sending a script containing GO directly through an ordinary database driver can therefore fail.
In a client that recognizes GO, this produces one result from each batch. The local variable is not available in the second batch. GO also does not commit a transaction by itself: batch boundaries and transaction boundaries are different concepts. Application code should send appropriate batches rather than blindly replace transaction logic with separators.
3. Why should T-SQL statements be terminated with semicolons?
A semicolon terminates a statement; it does not end a client batch. Consistent termination makes statement boundaries clear and avoids syntax problems in constructs that require the preceding statement to be terminated, notably a common table expression introduced by WITH.
The result is 15. A leading ;WITH is a common defensive style when the previous statement might lack a terminator, but it does not make the CTE a separate reusable object. I would distinguish the statement terminator from GO, which is interpreted by the client and ends the batch.
4. Why use schema-qualified object names such as dbo.Orders?
A schema-qualified name identifies the namespace containing the object. sales.Orders and archive.Orders can be different objects in the same database. Naming the intended schema makes code clearer and reduces dependence on the caller’s default schema and name-resolution behavior.
The schema is not simply a synonym for a user account. It groups objects and participates in ownership and permission design. For application SQL, I would reference the intended two-part name consistently and use a database-qualified name only when cross-database access is actually required. Hardcoding a server name into every query can create unnecessary deployment coupling.
5. When are square brackets needed around T-SQL identifiers?
Square brackets delimit identifiers that would otherwise be ambiguous or invalid, such as reserved words or names containing spaces. They identify an object or column name; single quotes delimit a character value. The two forms are not interchangeable.
This returns a column named Sales Region containing North. I would generally choose uncomplicated object names instead of depending on unusual punctuation everywhere. Brackets also do not make arbitrary dynamic SQL safe: quoting identifiers and parameterizing data values solve different problems. A closing bracket inside an identifier needs proper escaping when constructing a delimited name.
6. How do you declare and initialize a local variable?
Use DECLARE with an @-prefixed name and a concrete type, optionally supplying an initial value. An uninitialized local variable starts as NULL. Its lifetime is limited to the relevant batch or stored-program scope, so it is not session-persistent storage.
The numeric result is 37.50, with the expression’s result type derived from SQL Server’s arithmetic rules. Choosing the variable type is part of correctness: using an integer price would lose the fractional amount before multiplication. A variable holds one value; a result set with many rows requires a different representation.
7. How does SET variable assignment differ from SELECT assignment?
SET is the clearer default for assigning one scalar value. A scalar subquery used by SET returns NULL when it has no row and errors if it returns more than one row. Direct SELECT @variable = column FROM ... instead retains the previous value when no rows match and assigns from returned rows when several match.
That difference can leave a stale value in loop-based code. I would also avoid relying on which row “wins” in a multirow SELECT assignment. If one row is required, enforce or explicitly select the intended single-row result rather than hide unexpected multiplicity.
8. Why should SELECT variable assignment not be used to concatenate rows?
Assigning a variable repeatedly within a multirow SELECT is not a reliable ordered accumulation mechanism. The optimizer is free to choose an execution strategy, and the apparent row-by-row behavior of a small example does not establish a stable contract.
If the requirement is one string containing several values, use an appropriate set-based aggregate such as STRING_AGG, with an explicit ordering clause when order matters and with its documented size and compatibility constraints. If the requirement is a single selected value, define which row should supply it. I would not defend recursive variable assignment on the basis that it happened to work in one execution.
9. How does NULL affect comparisons in a WHERE clause?
A comparison involving NULL usually yields UNKNOWN rather than TRUE or FALSE. A WHERE clause retains rows for which its predicate is TRUE, so column = NULL does not identify missing values under normal modern SQL Server null semantics. Use IS NULL or IS NOT NULL.
This returns the row whose value is NULL. A predicate such as n <> 1 would exclude that row as well, because UNKNOWN is not TRUE. If missing values should be included, state the rule explicitly with an additional null condition rather than assume “not equal” means “everything else.”
10. How do AND and OR interact with NULL predicates?
T-SQL logical expressions use three-valued logic when a condition can be UNKNOWN. FALSE AND UNKNOWN is FALSE, while TRUE OR UNKNOWN is TRUE; other combinations can remain UNKNOWN. Parentheses are also essential when combining intended business alternatives because AND binds more tightly than OR.
For example, is_active = 1 AND (region = N'West' OR region IS NULL) includes active rows with West or missing region. Without the parentheses, a different expression can accidentally admit inactive rows whose region is null. I would test a small truth table with missing values instead of treating SQL predicates as ordinary two-valued application booleans.
11. Why should string lengths be explicit in declarations and casts?
T-SQL string type defaults vary by context, so omitting a length can silently produce a shorter value than intended. A declaration such as varchar without a length defaults to length 1, while CAST and CONVERT use a different default length when it is omitted.
The values are A and AB. I would specify a justified length for variables, parameters and conversions, then check source-to-target length compatibility. Choosing varchar(max) everywhere avoids some truncation risks but changes storage and processing considerations; it is not a substitute for understanding the data contract.
12. Why use the N prefix with Unicode string literals?
The N prefix makes a string literal Unicode, which is important when assigning text to nvarchar or nchar without first losing characters through a non-Unicode literal’s code-page interpretation. Unicode storage and literal interpretation are separate steps.
The intended result preserves the Japanese characters. SQL Server also supports UTF-8 collations for varchar, so “varchar can never hold Unicode” is too broad. I would identify the column type and collation and use the appropriate literal representation. The length of nvarchar is measured in UTF-16 code units, so it is not always the same as a count of displayed characters.
13. How can implicit data-type conversion cause a query to fail?
When an expression mixes types, SQL Server applies type-precedence and conversion rules. A value that looks harmless as text can fail when SQL Server attempts to convert it to a higher-precedence numeric type. The query’s syntax can be valid while its data makes the conversion impossible.
The first expression performs numeric addition rather than producing the text 123. I would convert explicitly when a boundary accepts text and keep typed columns and parameters aligned. Applying a conversion to every row of an indexed column can also change access behavior, so correctness and query performance should both inform the design.
14. Why does integer division sometimes produce an unexpected result?
When both operands are integer types, division produces an integer result with the fractional part discarded. Converting the result afterward cannot restore the fraction; one operand must be converted before division.
The numeric results are 2 and 2.5 respectively, with the decimal expression’s scale determined by SQL Server. This matters for percentages: completed / total * 100 can become zero for most partial completion values. I would also define the zero-denominator behavior instead of treating conversion as the only issue.
15. What do decimal precision and scale mean in T-SQL?
Precision is the total number of decimal digits and scale is the number to the right of the decimal point. A decimal(7,2) value therefore permits five digits before the point and two after it, subject to sign. Arithmetic expressions derive new precision and scale rather than simply inheriting the left operand’s declaration.
For a value such as 12345.67, decimal(7,2) is sufficient; 123456.78 is not. I would size stored amounts and intermediate calculations separately and define where rounding belongs. SQL Server caps decimal precision at 38 and may reduce scale in derived expressions, so a long chain of multiplications and divisions should be checked rather than assumed exact at any magnitude.
16. How do datetime2 and datetimeoffset serve different needs?
datetime2 stores a date and time without an offset, while datetimeoffset also stores an offset from UTC. Both support configurable fractional-second precision. The type alone does not establish the business meaning of a datetime2 value; the application must agree whether it represents UTC or local wall time.
For an observed event, an instant with an explicit offset or a documented UTC convention avoids ambiguity. For a future appointment, an offset is still not a full time-zone rule set: daylight-saving rules can change. I would retain the intended zone separately when it matters and avoid implying that datetimeoffset stores a named zone such as America/New_York.
17. How can you avoid ambiguous date strings in T-SQL?
Use typed parameters or unambiguous constructors and formats instead of relying on session language or date-format settings. A string such as 03/04/2026 can mean different dates depending on the interpretation.
The intended values are April 3 and 14:30 on that date. Constructors make numeric components explicit; ISO-style timestamp literals use the T separator. In application code I would normally bind a date/time parameter rather than format a date into SQL text, which also avoids quoting and localization mistakes.
18. Why use a half-open interval when filtering a day of timestamp data?
A half-open interval includes the starting instant and excludes the next boundary: timestamp >= start AND timestamp < next_start. It captures the entire day without guessing the final representable fractional second for a particular timestamp type.
The first two rows qualify and the next day’s midnight does not. For a local business day stored as UTC instants, compute the appropriate UTC boundaries from the relevant zone first. A calendar day is not always exactly 24 elapsed hours.
19. What does CASE do, and what does it not do?
CASE produces a scalar expression result based on conditions; it is not a procedural replacement for IF that executes arbitrary statement blocks. Its result expressions must resolve to a compatible result type.
The classifications are negative, zero and positive. For nullable input, add an explicit null case if missing is not meant to fall through to ELSE. I would also avoid assuming CASE protects every dangerous expression from early evaluation; some expressions can be evaluated before CASE receives their results.
20. How do COALESCE and ISNULL differ in T-SQL?
Both can supply a fallback for NULL, but their type rules differ. ISNULL generally returns the first argument’s type, while COALESCE resolves a result type through data-type precedence. COALESCE accepts multiple alternatives and can involve repeated evaluation of an expression; ISNULL is a two-argument function.
The expected strings are unk and unknown. The fallback in ISNULL is converted to the first argument’s short type. I would choose based on the required type and semantics rather than treat the names as interchangeable. Computed-column nullability metadata can differ too, which matters when defining keys or constraints.
21. When is TRY_CAST useful, and what are its limits?
TRY_CAST returns NULL for many conversions that fail because the input value cannot be represented in the target type. It is useful at a text-to-typed-data boundary, but NULL alone does not tell whether the source was missing or invalid, and explicitly disallowed conversions can still raise an error.
The parsed values are 12, NULL and NULL respectively; output row order is not guaranteed without ORDER BY. I would preserve the original value and an explicit validation status when invalid input needs investigation. A tolerant cast should not silently transform corrupt input into an apparently legitimate missing value.
22. How does NULLIF help express a zero-denominator rule?
NULLIF(a, b) returns NULL when its arguments compare equal and otherwise returns the first expression. It can turn a zero denominator into NULL, allowing a ratio to be unknown rather than raising a divide-by-zero error. That is a business choice, not a universally correct fix.
The ratio is NULL. If zero total means no activity, a report might display “not applicable”; converting it to 0% would assert something different. I would document the intended handling and ensure the numerator is converted before division when a fractional result is required.
23. Why is TOP without ORDER BY not a reliable selection rule?
TOP limits the number of rows but does not define which rows should come first. Without ORDER BY, a chosen row can change with the plan or data organization. A stable result also needs an ordering that resolves ties when the requirement is exactly a fixed number of rows.
This selects IDs 1 and 2 in that order. The ID tie-breaker is part of the selection contract. I would not rely on insertion order, a clustered index or a previously observed plan to supply an ordering that the statement did not request.
24. How does TOP WITH TIES change the result?
WITH TIES includes additional rows whose ORDER BY values tie with the last selected row. Consequently, TOP (2) WITH TIES can return more than two rows. The tie is defined by the full ordering expression, so adding a unique key changes which rows are considered tied.
The returned IDs are 1, 2 and 3, although the order between IDs 2 and 3 is unspecified. Adding item_id to this ORDER BY would break the score tie and return only two rows. If the requirement is “all items tied at the cutoff score, displayed by ID,” separate the selection from the final presentation ordering.
25. How do INNER JOIN and LEFT JOIN differ when a match is missing?
An INNER JOIN retains only matching combinations. A LEFT JOIN also retains each unmatched left-side row, with NULL values for the right-side columns. Both can multiply a left row when several right rows match; neither inherently means one result per left row.
The rows are (1,10), (1,11) and (2,NULL). With an inner join, the final row disappears. I would establish the relationship’s expected cardinality before aggregating: two orders for customer 1 are valid matching rows, not duplicate rows to erase with DISTINCT.
26. Why can a WHERE filter undo the effect of a LEFT JOIN?
A WHERE predicate on a right-side column can reject the NULL-extended rows produced by a LEFT JOIN. If the goal is to retain every left row and match only qualifying right rows, that qualification belongs in the ON condition.
This retains customer 2 with no order. Moving o.status = N'open' to WHERE would remove that row because its status is NULL. The right placement follows the business question: “customers with open orders” and “all customers with any open-order details” are different results.
27. How do WHERE and HAVING differ in a grouped query?
WHERE filters input rows before grouping, while HAVING filters the resulting groups. This distinction affects the data included in an aggregate: removing a row before SUM is not the same as calculating the sum and then deciding whether to retain the group.
The result is customer 1 with total 50. A SELECT alias such as total is not generally available to HAVING in the same query block, so repeat the expression or introduce another query layer. I would describe the logical processing distinction without assuming it dictates the optimizer’s physical execution order.
28. How do COUNT(*), COUNT(column) and COUNT(DISTINCT column) differ?
COUNT(*) counts rows, COUNT(column) counts non-null values and COUNT(DISTINCT column) counts distinct non-null values. Choosing the wrong form can quietly change the meaning of a report, especially after an outer join.
The results are 3, 2 and 1. After a left join, COUNT(*) also counts a retained parent row with no match, while counting a non-nullable child key counts actual matching children. For counts that can exceed the int range, SQL Server provides COUNT_BIG, whose result is bigint.
29. Why can DISTINCT hide a join problem?
DISTINCT removes duplicate result rows after the selected expressions are evaluated; it does not repair an incorrect relationship or explain why rows multiplied. It is appropriate when distinctness is the actual requested result, but can conceal a missing join condition.
If order lines are joined to product versions using product ID alone, each line may match several versions. Selecting only order ID and applying DISTINCT can make the row count look reasonable while amounts were already multiplied in another calculation. I would inspect the join keys and expected multiplicity first. If the requirement is only whether a match exists, EXISTS often expresses that directly without creating combinations to discard.
30. What does EXISTS test, and why is its SELECT list usually unimportant?
EXISTS tests whether its subquery returns at least one row. It does not return the selected values, so SELECT 1 is a conventional way to make that intent clear. A matching row whose selected value is NULL still makes EXISTS true.
The result is customer 1 once, even though two orders match. A join would return two customer rows unless another operation removed them. I would use EXISTS when the question is existence rather than requiring details from every matching child.
31. Why is NOT IN dangerous when its subquery can return NULL?
If a NOT IN list includes NULL, a nonmatching comparison can remain UNKNOWN rather than TRUE, so rows that appear absent may be filtered out. This follows SQL’s null logic, not a random optimizer issue.
This returns no rows. For an anti-match requirement, a correlated NOT EXISTS with an explicit equality condition is often clearer. Its treatment of a null outer value is not automatically identical to NOT IN, so I would define whether missing keys should be retained or rejected rather than mechanically replace one form with the other.
32. How do UNION and UNION ALL differ?
UNION combines compatible result sets and removes duplicate rows; UNION ALL preserves all rows. Duplicate removal applies to the complete selected row, not one preferred key, and it can add processing work. The choice should reflect whether repeated observations are meaningful.
This returns 1, 2, 2 and 3. Replacing UNION ALL with UNION returns 1, 2 and 3. The branches must have compatible column counts and types, and the final ORDER BY orders the combined result. A branch’s apparent input order is not a promise about the final output.
33. What is a common table expression, and how long can it be referenced?
A CTE is a named query expression scoped to the single statement that immediately follows its definition. It can improve readability and support recursive queries, but it is not a persistent table and does not guarantee materialization or one-time execution.
This returns (1,50). A later separate statement cannot reference Totals. If several statements need the same stored intermediate result, a temporary table may be appropriate. I would choose the CTE for expression structure, not claim it is inherently faster than an equivalent derived table.
34. Why should INSERT statements name their target columns?
An explicit target-column list defines how supplied values map to the table and makes the statement easier to review when the schema changes. Omitting the list couples the insert to the table’s implicit column order and can produce errors or incorrect assignments.
The row is (1,Ada) even though the insert list names the columns in a different order from the declaration. In a real table, also understand which omitted columns have defaults, allow NULL or are generated. Explicit columns improve clarity but do not override those constraints.
35. What does the OUTPUT clause provide during a basic data change?
OUTPUT returns values associated with rows affected by a DML statement, using INSERTED for new values and DELETED for old values as appropriate. It can capture generated keys or show which values changed without issuing an unrelated follow-up lookup.
This fixture returns the generated ID 1 and title Prepare report. OUTPUT row order is not guaranteed for a multirow change, and returned output is not proof that the transaction committed; output can be produced even when the operation later errors or rolls back. Trigger behavior and OUTPUT restrictions also need checking in the actual target schema.
36. How do you make a basic UPDATE target the intended rows?
Use a predicate that expresses the intended identity or scope, and verify that scope before making the change. Without WHERE, UPDATE affects every qualifying row of the target, which can be correct for a deliberate whole-table operation but disastrous for a single-record edit.
The output is ID 1, old quantity 2 and new quantity 3; ID 2 is untouched. This isolated fixture is safe to inspect. In application code, parameterize the identifier and consider the expected affected-row count. Joined updates require additional care when several source rows can supply different values for one target.
37. What is a stored procedure parameter, and how is a default used?
A procedure parameter is a typed value supplied to the procedure’s interface. A default allows a caller to omit that argument; passing NULL explicitly is different from omitting it and does not automatically select the default. Named arguments make calls easier to understand and less dependent on parameter order.
This definition belongs in an authorized disposable database and its own creation batch. GO is a client separator, so an application would send the definition and calls separately. Parameter types and accepted ranges are part of the interface; a default is not a substitute for validating values the business rule disallows.
38. How do result sets, OUTPUT parameters and procedure return codes differ?
A result set returns rows to the caller; an OUTPUT parameter passes a scalar value back through the parameter interface; RETURN provides an integer status value. They are separate channels, so a caller must consume the one the procedure actually uses.
The expected values are answer 14 and status 0. The caller includes OUTPUT to receive the parameter’s changed value. This script creates a procedure, so use an authorized disposable database. A return code convention must be documented; errors are not automatically transformed into a useful application status simply because RETURN exists.
39. What does SET NOCOUNT ON change in a stored procedure?
SET NOCOUNT ON suppresses row-count messages sent for statements; it does not suppress SELECT result sets or stop SQL Server from updating @@ROWCOUNT. It is commonly used in procedures to keep their client-facing output focused and reduce unnecessary message traffic.
For a procedure that updates a row and then returns a summary SELECT, the summary still reaches the caller. If application logic depends on an affected-row count, it should use an explicit supported mechanism rather than parse incidental messages. I would also capture @@ROWCOUNT immediately after the relevant statement, because later statements can change it. NOCOUNT controls reporting, not whether the underlying change occurred.
40. Why should an IF or ELSE branch use BEGIN...END when it contains multiple statements?
Without a BEGIN...END block, an IF or ELSE branch controls only the single statement that follows it. Indentation is for readers and does not change T-SQL control flow. Grouping the intended statements makes the branch’s scope explicit and prevents a later added statement from running unconditionally.
The expected values are without_block 1 and with_block 0. In the first case, only the assignment to 10 belongs to IF, so the increment runs despite the false condition. In the second case, neither assignment runs. BEGIN...END groups statements; it does not start or commit a transaction. I would use explicit blocks for multi-statement branches even when the indentation already looks clear.
T-SQL Interview Questions and Answers for Intermediate
The intermediate answers develop practical window, APPLY, JSON and data-modification patterns. The examples show why ordering, cardinality and type choices matter, with expected results and limitations stated alongside the code.
41. How would you choose exactly one latest row per customer deterministically?
Use ROW_NUMBER partitioned by customer and order by the business timestamp plus a unique tie-breaker. Ordering by timestamp alone leaves the chosen row unspecified when two events share that timestamp.
If customer 7 has event IDs 11 and 12 at the same latest time, this chooses 12. That is deterministic only if EventId is unique within the partition, and it is the right answer only if the tie-breaking rule is acceptable. If all simultaneous latest events matter, a one-row requirement is wrong and a tie-preserving ranking is more appropriate.
42. How do ROW_NUMBER, RANK and DENSE_RANK differ when scores tie?
ROW_NUMBER assigns a distinct sequence position to every row; RANK assigns tied rows the same position and leaves gaps; DENSE_RANK assigns the same rank to ties without gaps. Choose based on whether the request means individual rows, competition positions or distinct score levels.
Score descending | RANK | DENSE_RANK |
|---|---|---|
90 | 1 | 1 |
80 | 2 | 2 |
80 | 2 | 2 |
70 | 4 | 3 |
ROW_NUMBER for these rows is 1, 2, 3, 4, but the two 80-point rows need a unique tie-breaker to receive stable individual positions. Adding that unique key to RANK’s ordering would remove the score tie, so do not reuse the same ordering blindly. A “top three scores” requirement could include four people, while “top three people” requires a rule for selecting among ties.
43. Why should a running total specify ROWS and a unique order?
An explicit ROWS frame accumulates through the current row in the chosen sequence. The default frame for many ordered window aggregates is RANGE through the current peer group, which can make tied sort values advance together. A unique sequence also prevents arbitrary ordering among individual transactions.
For amounts 10, 20 and 5 in that order, the totals are 10, 30 and 35. If the first two timestamps tie and the ordering contains only PaidAt, a RANGE frame can show 30 for both. Neither frame is universally wrong; decide whether the result is per transaction or per timestamp group and express that choice explicitly.
44. Why can LAST_VALUE return the current row instead of the last value in a partition?
LAST_VALUE returns the last value in its window frame, not automatically the final value of the whole partition. With an ordered default frame ending at the current row’s peer group, its result can therefore match the current value.
For one account with sequence balances 10, 30 and 25, the full-partition frame returns 25 on all three rows. SequenceNo must define the intended order. Also decide whether a final NULL balance should remain NULL; frame choice and NULL treatment are separate questions.
45. How would you calculate a difference from the previous observed row?
Use LAG over an explicitly defined sequence, then subtract the returned value. LAG moves by row position in that sequence; it does not automatically mean the previous calendar day.
For readings 100, 108 and 105, changes are NULL, 8 and -3. The first NULL signals that no predecessor exists. A missing day still compares with the previous available reading; if the requirement is day-over-day change, generate or join a complete calendar and define missing-reading behavior first.
46. What changes when LAG or LAST_VALUE uses IGNORE NULLS?
IGNORE NULLS skips NULL values when locating the relevant value, while RESPECT NULLS retains them in the calculation. SQL Server 2022 introduced this option for these analytic functions; confirm the deployed build because Microsoft documented an IGNORE NULLS fix for LAG and LEAD in SQL Server 2022 CU4.
For readings 5, NULL and 9 in a deterministic order, the preceding reading for the third row is NULL with ordinary LAG and 5 with LAG IGNORE NULLS. That can represent the previous known measurement, but it does not establish that the missing measurement should be imputed.
Use the option only when skipping unknown values matches the business meaning. It can conceal a gap that should trigger a quality warning. The article’s SQL Server 2022 baseline supports the syntax, but deployment patch level remains relevant for behavior.
47. How would you find the top two orders for every customer while retaining customers with no orders?
Use OUTER APPLY with a correlated TOP query and a deterministic ORDER BY. OUTER APPLY preserves a customer when the right-side query produces no rows, filling its columns with NULL; CROSS APPLY would remove that customer.
A customer with three orders contributes two rows; one with no orders contributes one NULL-extended row. The outer ORDER BY controls presentation independently of the inner TOP selection. An appropriate customer-and-ordering index may help, but compare actual plans rather than assuming APPLY is inherently faster than a window-function alternative.
48. When can CROSS APPLY make a query clearer than repeating an expression?
CROSS APPLY can name a derived expression or correlated rowset so later clauses can refer to it clearly. This is a logical organization technique, not a guarantee that the optimizer evaluates an expression exactly once.
For a list price of 100 and discount rate 0.20, NetPrice is 80 and the row passes. The one-row VALUES expression does not filter out its input row merely because the expression is NULL, although the WHERE comparison would then be unknown and exclude it. Distinguish this from applying a subquery that can return zero or several rows.
49. How would you filter on a window-function result in T-SQL?
Calculate the window value in a CTE or derived table, then filter it in the outer query. A window function is not available to the WHERE clause of the same query block.
This returns employees in the two highest distinct salary levels per department, including ties. It does not guarantee only two employees. Put any population filter in the correct query block: filtering employees before ranking changes who participates, while filtering afterward preserves their rank within the original population.
50. How would you distinguish a moving average over rows from one over calendar days?
A ROWS frame counts records, not elapsed time. A three-row moving average uses the current row and two preceding rows even if the dates are far apart. A three-calendar-day measure requires an explicit date interval and a policy for days without observations.
With dates January 1, 2 and 10 and values 3, 6 and 12, the January 10 result is 7 over observed rows. It is not an average for January 8–10. The example assumes one row per date; otherwise add a unique ordering key or aggregate to daily grain. Do not invent unsupported interval-RANGE syntax in T-SQL to solve the calendar requirement.
51. How would you build a recursive CTE to walk an employee hierarchy?
Use an anchor for the starting employee and a recursive member that joins children to the previous level. Include a termination strategy and a finite MAXRECURSION limit appropriate to the expected hierarchy.
For 1 managing 2 and 2 managing 3, depths are 0, 1 and 2. A cycle reachable from the root can keep producing rows; the recursion limit is a failure guard, not a cycle detector or data repair. If cycles are possible, validate the hierarchy or track visited identities with a carefully designed path rule.
52. Why can a recursive CTE fail even when the anchor and recursive expressions look similar?
The anchor and recursive members must have the same column count and matching data types for corresponding columns. String expressions are a common trap because concatenation can produce a different inferred length from the anchor literal.
If the anchor uses a short path literal and the recursive member appends another identifier, explicitly cast both expressions to the intended path type and size. A matching cast solves type compatibility, but it does not solve path overflow or cycle handling.
Check each column’s type, precision, scale and string length rather than only its displayed value. For a numeric running calculation, a division or multiplication can also change the inferred decimal type. Define the representation deliberately and test the deepest supported case.
53. When should a repeatedly reused intermediate result become a temporary table?
Consider a temporary table when several statements need the same materialized intermediate rows, or when a deliberate phase boundary and indexing improve the workload. A CTE is scoped to one statement and does not promise that its result is stored for reuse.
For example, a batch may stage eligible customer IDs once, validate the count, then use them in two separate reports. A local temporary table can make that shared population explicit. A CTE repeated in two statements instead reruns its defining logic and may observe a different population depending on the surrounding transaction and isolation.
The temporary table adds creation, storage and maintenance cost. Materializing every small expression is not an optimization. Compare results and plans with representative data, and document whether the intermediate population is intended to remain fixed across statements.
54. How do a local temporary table and a table variable differ in a stored procedure?
Both hold tabular intermediate data, but they have different scope, statistics and optimization behavior. A local temporary table can support statistics and flexible indexing; a table variable is declared within its batch or routine and has more limited statistics support.
SQL Server 2019 introduced table-variable deferred compilation under compatibility level 150 or later, so the old claim that a table variable is always estimated as one row is too broad. Deferred compilation still does not make it identical to a temporary table or guarantee a good plan for every execution.
Choose using row counts, reuse, joins and observed plan behavior rather than a rule that small tables “belong in memory.” Table variables can also use tempdb resources. Keep the question at the query level: the appropriate structure is the one whose behavior fits the procedure’s workload.
55. How would you produce detail subtotals and a grand total in one grouped query?
Use GROUPING SETS to state the required aggregation grains explicitly. The empty grouping set represents the grand total; it is not an ordinary row whose grouping column happened to be NULL.
For two rows in East with categories A and B worth 10 and 20, the result includes both detail groups, an East subtotal of 30 and a grand total of 30. Use GROUPING or GROUPING_ID to label subtotal placeholders, because a genuine NULL Region can also occur in the source. Add an outer ORDER BY when report order matters.
56. When is conditional aggregation simpler than PIVOT?
Conditional aggregation is often clearer when a report has a small fixed set of categories or several measures. It makes the aggregation and missing-category behavior explicit without introducing a separate PIVOT operator.
For a customer with Open amounts 10 and 15 and a Closed amount 7, the row shows 25 and 7. The ELSE 0 choice treats nonmatching rows as zero; an all-NULL matching amount still needs a defined interpretation. PIVOT can be concise for one aggregate, but neither approach should hide duplicate source rows that already violate the intended grain.
57. What determines the grouping columns of a T-SQL PIVOT?
PIVOT groups by source columns that are neither the pivot column nor the aggregated value. Extra columns in the input can therefore split rows unexpectedly, even when they are not mentioned in the pivot’s IN list.
Project only the columns needed for the desired output grain before pivoting. If InvoiceId were left in the input, each invoice could become a separate grouping row rather than one row per customer. A missing category produces NULL under this SUM pivot; convert it to zero only when the report’s semantics require that.
58. How would you create an ordered comma-separated list with STRING_AGG?
Use STRING_AGG with WITHIN GROUP for the required order, and choose a sufficiently large input string type when the combined result can be long. The function does not infer an unlimited result simply because many rows are combined.
For names Ada, Bo and Cy, this yields “Ada, Bo, Cy” in the specified collation order. NULL inputs are omitted without an extra separator; use an explicit replacement if missing names must be visible. Deduplicate in a preceding query when required, because T-SQL STRING_AGG does not accept a DISTINCT argument in this syntax. STRING_AGG requires SQL Server 2017 or later.
59. How do you preserve token positions when using STRING_SPLIT?
On SQL Server 2022 or later, pass the constant third argument 1 to return the one-based ordinal column, then order the outer query by ordinal. The presence of ordinal does not itself guarantee output row order.
The result contains blue at position 1, an empty token at 2 and green at 3. Removing empty tokens may be appropriate for tags but would change the meaning of positional input. STRING_SPLIT requires compatibility level 130 or higher on the baseline deployment, and the separator is one character. It is not a general CSV parser for quoted delimiters.
60. How would you parse a JSON array into typed relational rows?
Use OPENJSON with an explicit WITH schema so the expected paths and SQL types are visible. Validate the document contract separately from merely shredding it into rows.
This produces (1,2) and (2,5). In the default lax path mode, a missing property can become NULL, which is not the same as satisfying a required-field rule. Explicit conversion also does not enforce positive quantities or unique IDs. OPENJSON needs compatibility level 130 or higher; use a staged validation step before inserting untrusted rows into constrained business tables.
61. When should you use JSON_VALUE, JSON_QUERY or OPENJSON?
Use JSON_VALUE for one scalar, JSON_QUERY for an object or array fragment, and OPENJSON when a document needs to become rows and columns. Choosing the wrong function can produce NULL in lax mode even when the path exists but refers to the wrong kind of value.
Requirement | Function | Example result |
|---|---|---|
Extract a customer name | JSON_VALUE | Ada |
Retain an items array as JSON | JSON_QUERY | A JSON array fragment |
Create one relational row per item | OPENJSON | ItemId and Quantity columns |
On the SQL Server 2022 baseline, JSON_VALUE returns nvarchar(4000). For a scalar longer than that limit, lax mode returns NULL and strict mode errors; use OPENJSON with an appropriate explicit type when long scalar content must be preserved. Syntax involving newer native JSON features should not be assumed available merely because current documentation includes it.
62. How would you expand nested JSON arrays while preserving the parent identifier?
Parse the parent objects first, retain the nested array with AS JSON, then APPLY another OPENJSON to that fragment. This keeps the relationship between parent and child explicit.
The result is (7,A) and (7,B). CROSS APPLY omits a parent with no child rows; use OUTER APPLY if such parents must remain as NULL-extended rows. Neither choice validates that items is a required nonempty array, so apply the domain rules separately rather than interpreting any produced rowset as valid input.
63. How do DATETRUNC(month) and EOMONTH differ when building monthly reporting keys?
DATETRUNC(month, value) returns the input truncated to the start of its month, while EOMONTH(value) returns the calendar date of the month’s last day. Choose the representation that matches the report key rather than treating the functions as interchangeable date formatting.
The results are 2024-02-01 00:00:00.000, 2024-02-29 and 2024-03-31. DATETRUNC preserves the input type and applicable fractional scale, so MonthStart is datetime2(3); EOMONTH returns date. The last calendar date is not the last representable timestamp of that day.
DATETRUNC was introduced in SQL Server 2022; EOMONTH is available from SQL Server 2012. A reporting dimension that uses a date key should convert the start representation deliberately. Also establish which time zone defines the reporting month before truncating an instant: truncation does not select a business time zone for you.
64. Why does DATEDIFF(day) not measure elapsed 24-hour periods?
DATEDIFF counts boundaries of the specified datepart crossed. Crossing midnight can produce a day difference of one even when only a fraction of a second elapsed.
The result is 1 although the elapsed duration is 0.2 seconds. For elapsed durations, select an appropriate fine-grained unit and account for its precision and range; DATEDIFF_BIG avoids the int result limit for supported calculations. For calendar anniversaries such as age, a raw year boundary count also needs adjustment when the anniversary has not occurred.
65. What happens when DATEADD adds a month to a date near month-end?
If the target month lacks the original day number, DATEADD returns the last day of the target month. This is calendar arithmetic, not adding a fixed number of days.
The results are February 29 and March 31. Adding one month twice starting from January 31 instead goes through February 29 and then March 29. Therefore, a recurring “last day of month” schedule should express that rule explicitly, for example with EOMONTH, rather than repeatedly adding a month to the previously adjusted date. The business recurrence rule determines the correct behavior.
66. How would you prevent an UPDATE with a join from using an ambiguous source row?
Ensure that the source supplies at most one intended value for each target row. If multiple source rows match and provide different values, an UPDATE FROM can produce an undefined choice rather than the business result you intended.
For two adjustments of +3 and -1 for the same product, the source aggregate supplies +2 once. This assumes the batch itself is valid and not applied twice; grouping fixes source multiplicity but not retry idempotency. If the rule is “latest adjustment,” select that row with an explicit timestamp and unique tie-breaker instead of summing.
67. How would you capture old and new values from an UPDATE?
Use OUTPUT with deleted for the pre-update values and inserted for the post-update values. Capture identifiers alongside the values so callers do not depend on row order.
A row moving from 10.00 to 11.00 records both values. OUTPUT is statement evidence, not a commit receipt: SQL Server can return output rows even when an error later rolls the statement back, and an outer transaction may still roll back. Values also reflect the DML operation before AFTER triggers execute, so account for trigger effects when interpreting final stored state.
68. Why is SCOPE_IDENTITY usually safer than @@IDENTITY after a single-row insert?
SCOPE_IDENTITY returns the last identity generated in the current scope, while @@IDENTITY can include an identity generated by a trigger in another scope on the same session. A trigger inserting an audit row can therefore make @@IDENTITY return the audit ID instead of the business row ID.
This assumes CustomerId is an int identity and that the insert generated one row successfully. For multiple inserted rows, use OUTPUT inserted.CustomerId rather than expecting one scalar identity value to describe the batch. Identity values can have gaps, and an allocated value does not by itself prove the transaction committed.
69. How do you capture the affected-row count reliably after DML?
Read @@ROWCOUNT immediately after the statement whose count matters and store it before another statement changes it. Logging, assignments and other statements can reset or replace the value.
If TaskId is unique, the result is zero or one. Zero means no row matched the predicate; it does not distinguish “missing task” from “already complete” without additional evidence. This example deliberately filters unchanged tasks. In general, an UPDATE can count matched rows even when the assigned value is the same, so define what the caller means by “changed.”
70. How would you update a bounded oldest batch rather than arbitrary TOP rows?
Select the target set with TOP and an ORDER BY in a CTE or subquery, then update that set. TOP on an UPDATE statement alone does not define which rows are the oldest.
This chooses up to 100 pending tasks by timestamp and unique ID under the statement’s read behavior. It is not a complete concurrent-worker queue claim: multiple sessions require an appropriate concurrency design and transaction policy. Keep the ordering requirement separate from the locking and retry requirements rather than presenting one TOP query as a universal queue solution.
71. How would you pass a set of IDs into a stored procedure without building an IN string?
Define a user-defined table type and accept a table-valued parameter declared READONLY. This gives the procedure typed rows that can participate in joins without parsing a comma-separated SQL fragment.
The primary key rejects duplicate input IDs for this contract. READONLY means the routine cannot modify the parameter rows directly. TVPs do not have ordinary column statistics, so very large or highly variable inputs may need a measured staging strategy. GO is a client batch separator used by tools such as SSMS, not a T-SQL statement sent inside one API command.
72. How do named and positional procedure arguments affect call compatibility?
Positional arguments bind according to the procedure’s declared parameter order, while named arguments bind by parameter name and can be supplied in a different order. Named calls make intent visible and avoid accidental rebinding if declarations are reordered, but renaming a parameter can still break those callers.
Both calls return customer 7, region All and IncludeClosed 1. The positional call needs DEFAULT to skip the middle parameter; the named call can omit it. Once a named argument is used in a T-SQL EXEC call, subsequent arguments must also use the named form. Supplying values in a different unnamed order can silently change meaning when their types remain convertible.
Treat the parameter list as a public interface. Appending an optional parameter can preserve existing calls, while inserting a parameter in the middle can rebind positional calls. Renaming breaks named calls, and changing a default can alter behavior even when calls still execute. Here GO separates client batches; an application API should submit the definition and calls as appropriate separate commands.
73. Why should stored-procedure parameters have explicit types and a clear NULL contract?
Parameter types define conversion, range and comparison behavior at the routine boundary. A nullable parameter also needs a meaning: NULL might mean “no filter,” “unknown value,” or “invalid request,” and those are not interchangeable.
For example, a procedure accepting @CustomerId int can reject malformed text before its business query, while a string parameter may introduce conversion behavior later. A @StartAt datetime2 parameter avoids depending on the caller’s regional date string format when the client binds it as a typed value.
Document defaults separately from NULL. Omitting a parameter can use its default, while explicitly passing NULL supplies NULL. Validate required combinations, such as start being before end, and avoid silently widening a query to all customers when a missing customer ID should have been an error.
74. How does an inline table-valued function differ from a multi-statement table-valued function?
An inline TVF expresses its result as one SELECT query, allowing it to compose naturally with the caller’s query. A multi-statement TVF populates a declared return table through several statements, which introduces different optimization and execution considerations.
This is an inline TVF and can be used in FROM or APPLY. Do not claim that every multi-statement TVF always has one fixed poor estimate: interleaved execution can improve eligible cases on supported versions and compatibility levels. Prefer the clearest correct representation, then inspect plans and workload behavior; neither function form is a universal performance guarantee.
75. When should reusable logic remain a procedure rather than become a user-defined function?
Keep logic that performs business changes or coordinates procedural operations in a stored procedure. A T-SQL user-defined function is intended to return a value or table and has restrictions on side effects and supported statements.
For example, “calculate eligible discount rows” can be a composable query or TVF. “Apply discounts, write audit records and return a status” belongs in a procedure with an explicit transaction contract. Trying to hide writes inside a function called from SELECT conflicts with that boundary.
Also distinguish scalar UDF syntax from performance behavior: eligible scalar UDF inlining on supported SQL Server versions can help, but not every function or call site qualifies. Do not choose a function solely to reduce typing if it obscures set-based logic or unexpectedly repeats expensive work per row.
76. What does TRY...CATCH catch, and what should you preserve when rethrowing?
TRY...CATCH handles many execution errors raised inside the TRY block, but it is not a blanket handler for every failure. Same-level compilation errors, certain connection-terminating conditions and client attentions are examples that require separate consideration.
A parameterless THROW inside CATCH rethrows the caught error rather than replacing it with a generic success-like result. Error logging must not mask the original error, and its ability to write depends on transaction state. Handling an error and deciding who rolls back are related but separate responsibilities.
77. How do @@TRANCOUNT and XACT_STATE answer different transaction questions?
@@TRANCOUNT reports the nesting count of BEGIN TRANSACTION statements, while XACT_STATE reports whether a user transaction exists and whether it is committable. A positive transaction count does not prove that COMMIT is legal.
XACT_STATE | Meaning | Consequence |
|---|---|---|
0 | No active user transaction | There is no transaction to commit or roll back |
1 | Active and committable | Commit or rollback depends on ownership and business outcome |
-1 | Active but uncommittable | A full rollback is required; writes and commit are not allowed |
For example, after an error leaves the transaction uncommittable, @@TRANCOUNT may still be positive. A CATCH block that checks only the count and attempts COMMIT is therefore unsafe. The procedure must also respect whether it started the transaction or inherited one from its caller.
78. Does committing an inner transaction make its changes durable independently?
No. SQL Server does not provide independently committable nested transactions through repeated BEGIN TRANSACTION statements. An inner COMMIT reduces @@TRANCOUNT, but the outermost commit determines whether the transaction’s changes commit.
If a caller begins a transaction, a child routine begins another and commits once, the caller can still roll back the work. Conversely, an unqualified full ROLLBACK inside the child can roll back the caller’s whole transaction, not merely the child’s apparent nesting level.
Write procedure contracts that state transaction ownership. A routine should not casually commit or roll back a caller-owned transaction just because its own step finished. Savepoints can provide a limited rollback boundary, but they do not create an independent transaction.
79. When is rolling back to a savepoint useful, and what limits it?
A savepoint can mark a recoverable boundary inside an existing transaction, allowing a routine to undo its own subsequent work without normally ending the whole transaction. It is useful only when the transaction remains in a state that permits that rollback.
The shown statements illustrate the boundary, not a complete error handler. When XACT_STATE is -1, rollback to a savepoint is not allowed because it requires log writes; a full rollback is needed. Savepoints are also unsupported in distributed transactions. The caller and callee must agree who performs the full rollback when partial recovery is impossible.
80. What is a basic TRY...CATCH transaction pattern for a routine that owns its transaction?
For a routine explicitly called without an ambient transaction, begin the transaction inside TRY, commit only after all required work succeeds, and roll back any remaining transaction in CATCH before rethrowing. State that ownership assumption so the pattern is not copied into a caller-owned transaction unnoticed.
ApplyRequiredChanges must respect the enclosing transaction and propagate failure. SET XACT_ABORT ON changes how many runtime errors affect the transaction, but it does not eliminate the need to handle errors or make compilation failures catchable. A procedure that may run inside an existing transaction needs an ownership-aware design rather than blindly using this full-rollback template.
T-SQL Interview Questions and Answers for Experienced
Experienced answers examine correctness under failure and changing workloads. They separate query semantics from plan choices and explain the trade-offs of retries, locking, dynamic SQL and optimization without presenting one pattern as universally safe.
81. Why does using sp_executesql not automatically make dynamic SQL safe?
It is safe against value injection only when untrusted values remain parameters rather than being concatenated into executable text. Calling sp_executesql on an already assembled string does not undo unsafe construction. Parameter types and lengths should also match the intended columns and input contract.
This returns the literal value O'Brien; its apostrophe is data, not query syntax. For a real search, keep the predicate text fixed and bind the search value. Parameters do not stand in for table names, column names or keywords, so those parts need a separate constrained construction policy.
82. How would you safely support a user-selected sort column in dynamic SQL?
Map the requested sort option to a small allowlist of known identifiers, delimit the chosen identifier and bind all ordinary values separately. Do not accept an arbitrary SQL expression as a column name. QUOTENAME escapes an identifier; it does not decide whether that identifier should be accessible.
This fragment assumes the stated dbo.Orders columns exist. The fixed OrderId tie-breaker gives a stable order for equal sort values. Validate sort direction through an allowlist too. Quote schema and object names separately when constructing multipart names; quoting dbo.Orders as one identifier changes its meaning.
83. What edge cases matter when using QUOTENAME?
QUOTENAME accepts an identifier-sized input and returns a delimited identifier, doubling an embedded closing delimiter. Inputs longer than 128 characters return NULL. It is therefore unsuitable as a general-purpose escaping function for long string values.
The first result is [a]]b]; the second is NULL. Validate length before narrowing an input into sysname, because premature truncation can turn the requested name into another valid name. Check the allowed object or column and the final constructed text. For values, use parameters instead of attempting to solve injection by applying identifier quoting.
84. How do variable and temporary-table scopes affect dynamic SQL?
A batch executed by sp_executesql does not automatically see local variables from its calling batch; pass them as parameters. A local temporary table created in the outer scope can be used by the inner dynamic batch, while a local temporary table created only inside that inner scope should not be relied on afterward.
Create the shared temporary structure in the owning scope when later statements need it. Avoid using the same temporary-table name in nested scopes: name resolution and lifetime become difficult to reason about. A global temporary table is not a safe shortcut because it introduces cross-session naming and lifecycle concerns.
85. How can dynamic SQL return a scalar value to its caller?
Declare an output parameter in the dynamic batch’s parameter definition and mark the caller binding as OUTPUT too. A variable assigned only inside the dynamic batch otherwise remains local to that execution scope.
For a real procedure, define what no matching row and multiple matching rows mean rather than returning an accidentally retained variable value. Use a result set for naturally tabular output and an output parameter for a documented scalar contract. The parameter’s type, precision and length are part of that contract, not implementation details.
86. How would you investigate a parameterized query that is fast for some values and slow for others?
Compare representative parameter values, actual row counts, estimates and plans before assuming that parameter sniffing is the cause. Skew can make one reused plan poor for a different cardinality, but blocking, stale estimates or a changed result size can produce similar symptoms.
For example, a customer with ten orders and one with ten million orders may benefit from different access strategies. Capture the compiled and runtime parameter context where available and compare logical reads and elapsed work under comparable conditions. The solution may involve query shape, indexing, statistics or a parameter-sensitive optimization, not disabling plan reuse everywhere. Preserve the query’s semantics while evaluating performance fixes.
87. What does Parameter Sensitive Plan optimization change in SQL Server 2022?
At compatibility level 160, eligible parameterized queries can use a dispatcher and multiple cached plan variants rather than relying on one plan for all parameter ranges. This can help when data distribution makes different strategies appropriate. Eligibility and behavior depend on the engine version, configuration and statement shape.
Confirm that the affected query actually receives the optimization by inspecting its plans and execution evidence. Do not assume every optional-filter query is solved by SQL Server 2022 PSP. Later versions add capabilities, and optional-parameter optimization is a separate feature with its own requirements. Keep version and compatibility level explicit when explaining an observed plan.
88. When is OPTION (RECOMPILE) a reasonable choice?
It can be reasonable for a statement whose best plan varies substantially with current parameter values and whose execution benefit outweighs compilation cost. Statement-level recompilation narrows the change to that statement. It is not a universal cure for slow procedures or poor indexing.
A report executed a few times per hour with highly variable filters may justify a fresh plan, while a tiny query called thousands of times per second may spend too much CPU compiling. Compare representative values and workload frequency, including compile cost. Document the reason and revisit it when data or query shape changes; a hint that once helped can become unnecessary or harmful.
89. Why can copying a parameter into a local variable make performance less predictable?
It can change which value information the optimizer uses during compilation, sometimes replacing a value-specific estimate with a more generic estimate. That may avoid one bad reused plan but also lose useful selectivity information. It masks the symptom without establishing that the resulting plan fits the workload.
If an extreme parameter first produced an unsuitable plan, a local-variable rewrite may appear to fix one test while degrading others. Compare small, typical and large result sets and inspect estimates. Prefer a deliberate optimization strategy with measured trade-offs over a coding habit presented as “turning off parameter sniffing.” The exact estimation behavior also depends on compilation context and query options.
90. Why can a weekday filter change results between sessions?
A predicate based on DATEPART(weekday, date) depends on the session’s DATEFIRST setting. The number 1 does not universally mean Sunday or Monday. A report that silently assumes one setting can misclassify working days when run through a different connection or language configuration.
November 2, 2026 is Monday. The fixture restores the previous setting after demonstrating the difference; production cleanup must also account for errors. Make the session contract explicit or use a calendar dimension whose business-day classification is stored independently of session numbering. A weekend rule still cannot identify local holidays without that additional calendar policy. DATEDIFF(week, ...) does not follow DATEFIRST: its week-boundary behavior uses Sunday, so mixing the two calculations can introduce another inconsistency.
91. How would you validate a string length when trailing spaces and Unicode matter?
First decide whether the limit is stored bytes, UTF-16 units, or user-perceived characters. LEN excludes trailing spaces, while DATALENGTH returns bytes. Neither is a universal validator for the other measure. Validate before narrowing the value into a shorter parameter or variable, otherwise truncation may already have destroyed the evidence.
Here a rule requiring exactly three stored characters including the trailing space cannot be checked with LEN = 3. Under supplementary-character collations, LEN counts a surrogate pair as one character, while its nvarchar storage still uses two UTF-16 units. UTF-8 varchar has variable bytes per character as well. A byte limit must be checked in the intended target encoding; a displayed-character limit may require application-level grapheme handling. Include trailing spaces, supplementary characters and combining sequences in the boundary fixtures rather than deriving every limit from ASCII examples.
92. What should you check when a query behaves differently under another collation?
Check case and accent sensitivity, comparison rules and expression-level collation conflicts. Collation affects equality and ordering, so a query can return different matches without any change to its SQL text. Temporary objects and cross-database queries can expose mismatched defaults.
If product codes must be case-sensitive, define that contract explicitly rather than relying on the developer database’s default. An explicit COLLATE can resolve a comparison requirement, but applying it to a column can affect optimization and should be tested. Use representative accented and case-varying values. A collation change is a semantic change, not merely a way to suppress an error message.
93. Why is IF NOT EXISTS followed by INSERT vulnerable to a race?
Two sessions can both observe that a key is absent before either inserts it. A unique constraint remains essential, but the existence check alone does not reserve the missing key. The procedure must define how concurrent attempts are serialized or how a duplicate-key conflict is handled.
In a disposable fixture, pause two sessions after their reads, then let both attempt the same key. Without a uniqueness rule, duplicates can be stored; with one, one attempt must fail or be resolved. A transaction at ordinary read committed isolation does not automatically protect the gap. Choose locking, an atomic insert-and-handle-conflict design, or another documented strategy based on the operation’s semantics.
94. How can an update-then-insert pattern protect an absent key under concurrency?
One approach uses an explicit transaction and a serializable key-range search, while retaining a unique key. The example below is a fragment for a table whose ItemId is a primary key; it owns its transaction and assumes no ambient transaction. UPDLOCK and HOLDLOCK keep the existence decision protected until completion.
Test this with two controlled sessions and the actual indexes and isolation settings. Stronger locking can increase blocking and does not eliminate every deadlock. Keep transactions short, use consistent access order and define retry behavior. This is a concurrency design to validate, not a claim that a hint makes every upsert workload safe or fast.
95. What makes a retried T-SQL operation idempotent?
The operation needs a stable business request key and an atomic rule that prevents the same request from applying its effect twice. Repeating SET Balance = Balance + @amount is not idempotent. A timeout can leave the caller uncertain whether the first attempt committed.
Within one transaction, record a unique RequestId together with the effect and a durable result. On a duplicate request, return the prior outcome after checking that the payload agrees. Reusing the same key with a different amount is a conflict, not a retry. Also define key retention and handling of concurrent retries. An application retry loop cannot provide this guarantee if the database treats every attempt as a new business action.
96. Why can a sequence-generated business number have gaps after rollback?
Sequence allocation occurs outside the consuming transaction. Requesting a value consumes it even if no row is inserted or the later transaction rolls back. A sequence also serves callers independently of any one table, so an absent number is not by itself evidence of a missing committed record.
The stated values assume no other consumer. NO CACHE does not make rollback return the allocation; caching introduces an additional possible source of gaps after unexpected shutdown. Nor does allocation order prove commit order when transactions overlap. Keep a unique constraint on the stored business key: sequence generation alone does not police manual inserts or inappropriate restart/cycle choices. If the business requires a gap-free finalized numbering process, design a serialized assignment and reconciliation policy with its blocking and failure costs instead of promising that a sequence provides that guarantee.
97. Does rolling back a transaction restore local variables and table variables?
No. A transaction rollback reverses the affected transactional data changes, but changes to local variables and table variables are not erased by that rollback. Code that records “work completed” in those variables can therefore retain that state even though the corresponding table changes did not commit.
This fixture shows surviving in-batch diagnostic state, not durable logging: the variables still disappear with their scope. Do not use a populated table variable as proof that the business transaction committed. After an error, distinguish attempted work, returned intermediate results and durable outcome, then reset or reinterpret local state before continuing.
98. How would you expose a stable custom error contract with THROW?
Give application validation failures documented error numbers and states, while keeping human-readable messages useful without making clients parse them. A new THROW requires an error number from 50000 through 2147483647 and a state from 0 through 255. Its message is limited to nvarchar(2048); it does not interpolate printf-style arguments.
The fixture raises error 50010, state 2, with the supplied quantity in the message. The preceding statement is terminated with a semicolon. A newly raised THROW uses severity 16; a bare THROW inside CATCH instead preserves the original exception. If a message includes a literal percent sign, follow THROW’s percent-escaping rule. Separate domain rejection from transient retryable failures in the client contract, and avoid replacing every database error with this one number: doing so would erase the distinction between invalid input, deadlock and infrastructure failure.
99. Why can TRY...CATCH miss an error that appears inside its TRY block?
Not every error is catchable at the same execution level. Same-level compilation or statement-recompilation errors, such as some name-resolution failures, can escape the local catch. Some failures that terminate the connection also cannot be handled as an ordinary recoverable error.
Assuming that object does not exist, executing the failing statement at the lower dynamic execution level allows the outer catch to receive its name-resolution error, typically 208. This illustrates scope, not a reason to wrap all code in dynamic SQL. Keep compile-time validation, application error handling and transaction cleanup in the overall design.
100. What contract must a procedure satisfy when its results are captured with INSERT...EXEC?
INSERT...EXEC requires the returned result sets to match the receiving columns in number, order and compatible types. Column aliases do not map by name. An added diagnostic SELECT can therefore break a caller or, if its shape is compatible, add unintended rows. A procedure used this way needs a stable result contract across every branch.
This self-contained fixture captures one row. Test empty results, nullability, conversion failures and every result-producing branch of a real routine; NOCOUNT does not suppress an extra SELECT result set. An INSERT...EXEC call cannot be nested in the same execution call stack, so inspect callees before wrapping an existing procedure. EXECUTE WITH RESULT SETS is not permitted in INSERT...EXEC as a way to reshape it. If composable relational logic is the requirement, consider an inline table-valued function or an explicitly designed staging interface instead of accumulating hidden result-set dependencies.
101. What should you review before using MERGE for synchronization?
Review source-key uniqueness, target constraints, match semantics and concurrent execution. Multiple source rows that try to update the same target row can fail, and a source subset must not be mistaken for a complete authoritative snapshot when deleting target rows. One statement does not remove the need to reason about isolation.
For a straightforward upsert, separate statements with a clear transaction and concurrency strategy may be easier to validate. If MERGE is justified, test inserts, updates, deletes, duplicate source keys and competing sessions on the exact supported SQL Server build. Avoid treating TOP as an ordered batching mechanism. Microsoft documents concurrency cautions, so the decision should rest on the actual workload rather than syntax compactness.
102. Why must a DML trigger handle multiple rows?
A trigger fires for a statement and can receive multiple rows in inserted and deleted. Assigning one value from inserted to a scalar variable silently assumes a single-row operation. Bulk inserts and ordinary set-based updates can violate that assumption.
This groups inserted amounts per header instead of selecting an arbitrary line. It assumes a non-null existing total and an insert-only maintenance contract; updates and deletes need their own correctly defined deltas. Test several lines for one order and lines for different orders. Trigger side effects also run in the surrounding transaction and can cause the original statement to fail.
103. Why can stored-procedure SET options differ from the caller’s current settings?
SQL Server captures QUOTED_IDENTIFIER and ANSI_NULLS settings when a stored procedure is created or altered. In particular, static SQL in the procedure is parsed using its captured quoted-identifier setting, so changing that option inside the body is not a way to reinterpret the already-defined static statements.
Use this metadata query for an existing procedure when diagnosing deployment differences. Keep deployment scripts explicit about expected settings. On the SQL Server 2022+ baseline, ANSI_NULLS is always ON; its OFF behavior is deprecated and should not be a design dependency. Use IS NULL for null tests. Dynamic nested batches have additional parse-context rules, so do not generalize static-procedure behavior to every constructed statement.
104. How can OFFSET pagination produce missing or repeated rows while data changes?
Offsets count positions in the current result, so inserts or deletes before the next offset shift those positions. A unique ordering resolves ties but does not freeze the dataset across separate requests. The consistency contract must address both ordering and concurrent changes.
If page one returns IDs 10 and 20, deleting ID 10 before requesting offset two can cause ID 30 to be skipped. Keyset pagination instead asks for rows after the last ordered key, such as WHERE Id > @last_id ORDER BY Id, when that key is immutable and matches the required order. For a reproducible snapshot, use a suitable snapshot or materialized result strategy. Keyset pagination alone does not freeze updates to the displayed data.
105. How would you paginate by a nonunique timestamp deterministically?
Use a unique tie-breaker and carry the complete ordering key in the continuation token. A timestamp alone can place several rows at the boundary, causing missing or repeated results. The predicate must mirror the sort direction and null policy.
This fragment assumes non-null timestamps, a unique immutable EventId, and compatible parameter types. If rows at the boundary are (10:00,7) and (10:00,8), continuing after the first includes the second. Changes to the ordering timestamp can still move rows across the boundary; define whether the application accepts that or requires a stable snapshot.
106. Does ORDER BY inside a view guarantee the order returned to its consumers?
No. A consumer must specify its own ORDER BY when output order matters. Where an order clause is permitted in a view with a row-limiting construct, it helps define which rows are selected; it does not establish a persistent presentation order for all later queries.
For example, a view selecting the ten highest amounts can define that subset with TOP (10) ... ORDER BY Amount DESC, OrderId. A consumer still needs ORDER BY Amount DESC, OrderId to request that display order. Adding TOP (100) PERCENT to smuggle an order clause into a view is not an ordering contract. Separate selection semantics from final presentation and use a deterministic tie-breaker where the requirement calls for one.
107. How can an unqualified subquery column silently bind to an outer query?
If an unqualified column is not found in the subquery’s own scope but exists in an outer scope, SQL Server can resolve it to that outer column. A misspelled or wrongly assumed inner column can therefore create unintended correlation instead of an error. Qualifying references makes the intended scope explicit.
In the first query, each outer key is compared with itself for the one inner row, so both keys qualify. Writing the mistaken reference as i.KeyId would instead expose the missing column as an error. The example is deliberately read-only: the same scope mistake in a deletion predicate could affect far more rows than intended. Review aliases and use a small known fixture before applying any corrected predicate to DML.
108. What should you verify before assuming a scalar UDF will be inlined?
Check the database compatibility level, configuration, function eligibility and the actual calling query plan. Scalar UDF inlining is available from SQL Server 2019 at compatibility level 150 or higher, but not every function or invocation qualifies. Eligibility rules and fixes vary with supported builds.
An inlineable metadata flag is not proof that a particular execution was inlined. Compare the plan and performance on representative inputs, including error and null behavior. Keep the function’s logic simple where appropriate, but do not redesign solely to satisfy an optimization checklist. If a performance diagnosis depends on inlining, record the engine build and compatibility level so the claim is reproducible.
109. How can replacing a scalar subquery with APPLY change cardinality?
A scalar subquery in an expression must produce at most one value: zero rows yield null, and more than one row raises an error. APPLY returns a relational row set, so multiple matching rows can expand the outer row. A rewrite may therefore turn a useful cardinality error into silent duplication.
This returns (1,10), (1,20) and (2,NULL). Replacing the applied expression with a scalar lookup of Value would fail for parent 1. If the contract requires one child, enforce uniqueness or define a meaningful selection rule; adding an arbitrary TOP (1) merely hides the ambiguity.
110. How would you design a restartable set-based update in batches?
Choose a stable key range or durable work list, make each batch atomic and checkpoint only committed progress. A restart must neither skip uncommitted rows nor reapply a non-idempotent effect. Batching by an arbitrary TOP without a stable progress rule is difficult to recover safely.
For a backfill, record a bounded high-water key and update ordered ranges with an idempotent assignment, such as deriving a new column from immutable inputs. Commit the batch and its checkpoint together when they share a transaction boundary. If concurrent inserts or updates can occur behind the checkpoint, define a separate reconciliation pass or version rule. Small batches reduce transaction size, but they change atomicity and require an explicit partial-completion contract.
111. How can rowversion guard an optimistic update, and why is it not a timestamp?
rowversion is an automatically generated binary version token, not a date or elapsed-time value. Save the token read with a row and include it in the update predicate. If another update changes that row first, the old token no longer matches and the caller can handle a conflict rather than overwrite unseen work.
Bind the original token with the correct binary type and obtain a fresh token after a successful update. Zero affected rows can mean deletion as well as a version conflict, so the message should not pretend to distinguish them without further evidence. Even an update that assigns an existing value can advance the token. Keep an actual date-time column separately when the application needs modification time.
112. How do EXCEPT and INTERSECT treat duplicates and NULL values?
They return distinct rows under set-comparison semantics. EXCEPT returns left-side rows absent from the right side, while INTERSECT returns rows present on both sides. When determining distinct rows for these operators, two null values are considered equal, unlike an ordinary = predicate’s null behavior.
The second result’s order is unspecified. These operators do not compare multiplicities: two copies of 1 on the left and one on the right are not reported as a count discrepancy. For reconciliation where duplicate counts matter, compare grouped counts as part of the result. Also check compatible types and collation because comparison conversions can affect meaning.
113. When is IS DISTINCT FROM useful for nullable comparisons?
In SQL Server 2022 and later, IS DISTINCT FROM and IS NOT DISTINCT FROM provide comparisons that return TRUE or FALSE even when either operand is null. They are useful when null represents a value-state that should participate in change detection: two nulls are not distinct, while null and a non-null value are distinct.
For the listed pairs, the changed flags are 0, 1, 1 and 0; row order is not guaranteed without an outer order clause. This avoids sentinel substitutions such as treating null as -1, which can collide with valid data. The predicate does not decide what null means in the domain; confirm that “both unknown” should count as unchanged for the particular operation.
114. How would you delete duplicate rows while preserving a deterministic survivor?
First define the business duplicate key and the survival rule. A row-number expression can rank candidates within each duplicate group, but its order must resolve ties. The example keeps the lowest ingestion ID for each external key; that is correct only if earliest-ingested is the agreed rule.
The survivors are (1,A,10) and (3,B,20). If duplicates disagree on payload, investigate whether they are revisions or conflicts before deleting them. For real tables, preview affected keys, protect the operation against concurrent changes and add an appropriate uniqueness rule when future duplicates are invalid. Deterministic deletion is not automatically a correct reconciliation policy.
115. Why can concatenation truncate before assignment to nvarchar(max)?
The expression’s intermediate type controls how much data survives before assignment. Assigning a bounded concatenation into an nvarchar(max) variable does not recover characters already truncated. Introduce a large-value operand before the potentially oversized concatenation is evaluated.
The fixture contains no trailing spaces, so LEN is appropriate for these counts. In longer chains, casting only a later operand can be too late if an earlier bounded pair has already been evaluated. Review construction types, not just the destination declaration, especially when assembling long dynamic statements or export text.
116. Why should a query not depend on predicate evaluation order to avoid conversion errors?
T-SQL describes a result, and the optimizer can reorder or transform expressions. A predicate that appears to filter bad strings before a cast is not a reliable procedural guard. Use an expression with the intended safe-conversion semantics and retain evidence of invalid input.
The values parse to 12, null and null: the last exceeds the int range. This handles conversions that are permitted but fail for the data; explicitly disallowed conversions can still raise errors. If null is also a valid source state, track conversion failure separately so malformed input is not silently treated as missing data.
117. Why can dynamic SQL inside a procedure fail permissions checks that static SQL passes?
Dynamic SQL does not inherit the same ownership-chain shortcut as a statically referenced statement inside a module. Under the default caller context, permissions on objects referenced by the dynamic batch may be checked against the caller even when that caller can execute the enclosing procedure.
Diagnose the effective execution context and the exact denied object before changing permissions. A deliberately designed EXECUTE AS module or certificate-signed procedure can support a controlled access boundary, but they have different identity and permission behavior. Impersonation can affect auditing and row-security logic; certificate signing preserves caller context while adding scoped permissions. Avoid resolving a narrow failure by granting broad table access, and keep value parameterization and identifier allowlisting even when execution permissions are correct.
118. How would you validate a query rewrite that is faster but returns slightly different results?
Treat the difference as a correctness issue until it is explained by an approved semantic change. Compare keys, duplicates, null handling, ties, ordering requirements and numeric precision, not only total row counts. A faster query that drops unmatched rows or collapses duplicates is not an optimization of the original contract.
Build a small adversarial fixture containing duplicate values, missing matches, nulls and boundary timestamps, then compare both result multisets. Set-based difference operators alone can hide duplicate-count changes, so group by the complete result and compare multiplicities when duplicates matter. Once equivalence is established for the contract, evaluate performance on representative data. Keep correctness evidence separate from timing evidence.
119. How would you debug a complex T-SQL query that returns the wrong total?
Identify the intended output grain and check row multiplicity at each join and aggregation boundary. Reduce the problem to a small set of known entities, then trace their expected contributions. Do not start by adding DISTINCT or changing join types until the discrepancy disappears.
For an order total, two lines worth 20 and 30 joined to three status-history rows become six rows and sum to 150. The correct total is 50 if status history is only a filter or annotation. Use an existence predicate, a unique as-of match or preaggregation according to the requirement. Preserve the failing fixture as a regression example and explain the precise semantic error, rather than attributing incorrect output to “SQL being inconsistent.”
120. How can error logging accidentally hide the original T-SQL failure?
A catch block can raise a second error while trying to write diagnostics, especially when the transaction is uncommittable. The new failure can obscure the original error and leave the caller with misleading evidence. Capture the original error details and transaction state before attempting cleanup or secondary work.
For a procedure that owns the transaction, roll back as required before writing a separate durable log record, and protect the logging path so its failure does not replace the original exception. A procedure inside a caller-owned transaction cannot assume that it may roll back or start an independent logging transaction on the same connection. Preserve the original error with a bare THROW; in the catch and let the owning boundary coordinate recovery and durable diagnostics. Do not report success merely because logging completed.
T-SQL Documentation and Further Reading
These Microsoft references cover the language rules and runtime features discussed above. Documentation links use the SQL Server 2025 reference view, whose applicability notes also identify earlier supported versions. Check the target engine build, compatibility level and database settings before using a version-dependent feature; Azure services and Microsoft Fabric do not necessarily support every SQL Server behavior.
Similar Posts
