SQL Server (T-SQL) Interview Questions

T-SQL (Transact-SQL) is Microsoft's procedural dialect of SQL used by SQL Server and Azure SQL. It extends standard SQL with variables, control-of-flow (IF, WHILE), TRY...CATCH error handling, and dialect-specific features such as TOP, IDENTITY, the APPLY operators, and functions like GETDATE() and STRING_SPLIT().

DELETE removes rows (optionally filtered), is fully logged, fires triggers, and can be rolled back. TRUNCATE removes all rows fast with minimal logging, resets IDENTITY, but is still transactional in SQL Server. DROP removes the table definition and data entirely.

A primary key is NOT NULL and unique, and a table has at most one; by default SQL Server backs it with a clustered index. A unique constraint also enforces uniqueness but allows a single NULL, a table can have many, and it defaults to a non-clustered index.

Scalar functions (return one value), inline table-valued functions (return a table from a single SELECT — the most performant), and multi-statement table-valued functions (build a table with procedural code). Prefer inline TVFs; scalar and multi-statement functions can hurt performance when used per row.

A clustered index defines the physical storage order of the table's rows, so there can be only one; the table is the index's leaf level. A non-clustered index is a separate structure holding the key plus a pointer back to the row, and a table can have many. Non-key columns can be added with INCLUDE to make a covering index.

An index that contains every column a query needs — key columns for filtering/sorting plus extra columns via INCLUDE. The query is satisfied entirely from the index without a lookup back to the table (no "key lookup"), which can dramatically speed up hot queries.

A #temp table is a real table in tempdb with statistics and indexes — best for large intermediate sets. A @table variable is lighter, scoped to the batch, has no statistics (the optimizer assumes few rows), and isn't affected by rollback. A CTE is just a named subquery that exists for a single statement and stores nothing.

Use the estimated or actual execution plan in SSMS. Read right to left, top to bottom; watch for expensive operators like table/clustered-index scans on big tables, key lookups, and large discrepancies between estimated and actual row counts (a sign of stale statistics or parameter sniffing).

Statements go in a TRY block; if one throws, control jumps to the CATCH block, where functions like ERROR_MESSAGE(), ERROR_NUMBER(), and ERROR_LINE() describe the failure. Combine it with explicit transactions and ROLLBACK/COMMIT for safe multi-statement work.

BEGIN TRY
  BEGIN TRAN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
  COMMIT;
END TRY
BEGIN CATCH
  ROLLBACK;
  THROW;
END CATCH;

MERGE performs insert, update, and delete against a target table in one statement based on whether rows match a source — the standard "upsert" pattern for syncing a staging table into a target. Test carefully: MERGE has known edge cases, and many teams still prefer explicit separate statements for critical paths.

Both invoke a table-valued expression for each row of the left input (like a correlated join). CROSS APPLY keeps only left rows that produce at least one result (like an inner join); OUTER APPLY keeps all left rows, returning NULLs where the right side is empty (like a left join). They're the T-SQL way to do "top N per group".

All are window functions numbering rows in an ORDER BY within an optional PARTITION BY. ROW_NUMBER gives every row a unique sequential number; RANK gives ties the same number then skips (1,1,3); DENSE_RANK gives ties the same number with no gap (1,1,2).

Read Uncommitted, Read Committed (default), Repeatable Read, Serializable, plus the row-versioning Snapshot isolation. The NOLOCK hint is equivalent to Read Uncommitted on one table — it avoids blocking but permits dirty and inconsistent reads, so it should never be used where correctness matters.

A deadlock is a cycle where two sessions each hold a lock the other needs; SQL Server's monitor kills the cheaper session as the deadlock victim (error 1205), which the app should retry. Reduce them by accessing objects in a consistent order, keeping transactions short, adding the right indexes, and using an appropriate isolation level (or Snapshot).

IDENTITY is a table-column property that auto-increments on insert and is tied to that one table. A SEQUENCE (SQL Server 2012+) is a standalone schema object that generates numbers you can request with NEXT VALUE FOR — shareable across multiple tables and obtainable before the insert.

SQL Server compiles a stored procedure's plan using the parameter values from the first execution and caches it. If those values are atypical, later executions with very different values may get a poor plan. Mitigations include OPTION (RECOMPILE), OPTIMIZE FOR, or local variables to mask the sniffed value.