Top SQL Server Interview Questions and Answers, by Topic
Not new material — every answer below traces back to a specific earlier lesson in this course, cited so you can go re-derive the full reasoning if a follow-up question digs deeper than the one-liner. A reference bank organized the way it actually gets asked in an interview room.
Fundamentals
Q: What’s the difference between DELETE, TRUNCATE, and DROP?
A: DELETE removes rows (optionally filtered with WHERE), is logged row-by-row, fires DELETE triggers, and can be rolled back mid-transaction. TRUNCATE removes all rows, deallocates pages directly, resets IDENTITY, doesn’t fire triggers, and can’t be filtered. DROP removes the entire table structure and data permanently. (Fundamentals Ch.2)
A: DELETE removes rows (optionally filtered with WHERE), is logged row-by-row, fires DELETE triggers, and can be rolled back mid-transaction. TRUNCATE removes all rows, deallocates pages directly, resets IDENTITY, doesn’t fire triggers, and can’t be filtered. DROP removes the entire table structure and data permanently. (Fundamentals Ch.2)
Q: What’s the difference between WHERE and HAVING?
A: WHERE filters rows before grouping; HAVING filters groups after GROUP BY — HAVING can reference aggregates, WHERE cannot, because at the point WHERE runs, no aggregate has been computed yet. (Fundamentals Ch.4)
A: WHERE filters rows before grouping; HAVING filters groups after GROUP BY — HAVING can reference aggregates, WHERE cannot, because at the point WHERE runs, no aggregate has been computed yet. (Fundamentals Ch.4)
Q: Why does WHERE column = NULL always return zero rows?
A: SQL uses three-valued logic — NULL means “unknown,” and unknown = unknown evaluates to unknown, not true. Use IS NULL instead. (Fundamentals Ch.3)
A: SQL uses three-valued logic — NULL means “unknown,” and unknown = unknown evaluates to unknown, not true. Use IS NULL instead. (Fundamentals Ch.3)
Joins & Sets
Q: When does a LEFT JOIN silently behave like an INNER JOIN?
A: When a filter on the right table’s column sits in WHERE instead of ON — NULL fails most WHERE comparisons, discarding the unmatched left rows LEFT JOIN was meant to preserve. (Fundamentals Ch.5)
A: When a filter on the right table’s column sits in WHERE instead of ON — NULL fails most WHERE comparisons, discarding the unmatched left rows LEFT JOIN was meant to preserve. (Fundamentals Ch.5)
Q: What’s the difference between UNION and UNION ALL?
A: UNION removes duplicate rows across the combined result (a real cost); UNION ALL keeps every row including duplicates and is faster since it skips the dedup pass. (Fundamentals Ch.5)
A: UNION removes duplicate rows across the combined result (a real cost); UNION ALL keeps every row including duplicates and is faster since it skips the dedup pass. (Fundamentals Ch.5)
Functions & Procedures
Q: When would you choose a stored procedure over a function?
A: When you need to modify data, manage explicit transactions, use TRY/CATCH, or return multiple result sets — none of which a function can do, by design (attempting DML inside a function throws “invalid use of a side-effecting operator”). (Ch.2, Ch.4)
A: When you need to modify data, manage explicit transactions, use TRY/CATCH, or return multiple result sets — none of which a function can do, by design (attempting DML inside a function throws “invalid use of a side-effecting operator”). (Ch.2, Ch.4)
Q: What’s the difference between a temp table and a table variable?
A: The behavioral difference that actually matters: a table variable’s contents survive a transaction ROLLBACK; a temp table’s contents are rolled back with the transaction. Table variables also historically carry weaker optimizer statistics. (Ch.3)
A: The behavioral difference that actually matters: a table variable’s contents survive a transaction ROLLBACK; a temp table’s contents are rolled back with the transaction. Table variables also historically carry weaker optimizer statistics. (Ch.3)
Performance
Q: What’s the difference between a clustered and nonclustered index?
A: A clustered index’s leaf level IS the actual data, physically ordered by the key — at most one per table. A nonclustered index’s leaf holds the key plus a pointer back to the clustered index, requiring a key lookup for any additional columns not covered. (Ch.8)
A: A clustered index’s leaf level IS the actual data, physically ordered by the key — at most one per table. A nonclustered index’s leaf holds the key plus a pointer back to the clustered index, requiring a key lookup for any additional columns not covered. (Ch.8)
Q: How would you diagnose a slow query in production?
A: Check sys.dm_exec_query_stats for cost, capture the actual execution plan, look for Table Scans/Key Lookups and Estimated-vs-Actual gaps, confirm with STATISTICS IO, then design a targeted (ideally covering) index and re-measure. (Ch.8)
A: Check sys.dm_exec_query_stats for cost, capture the actual execution plan, look for Table Scans/Key Lookups and Estimated-vs-Actual gaps, confirm with STATISTICS IO, then design a targeted (ideally covering) index and re-measure. (Ch.8)
Q: What is parameter sniffing?
A: A stored procedure’s execution plan is compiled once and cached based on the first parameter value seen; that plan gets reused for every later call regardless of whether the shape of the data matches, sometimes producing a fast plan for one caller and a terrible one for another. (Ch.4)
A: A stored procedure’s execution plan is compiled once and cached based on the first parameter value seen; that plan gets reused for every later call regardless of whether the shape of the data matches, sometimes producing a fast plan for one caller and a terrible one for another. (Ch.4)
Transactions & Security
Q: What causes a deadlock, and how do you prevent one?
A: Two transactions each holding a lock the other needs, in a circular wait. Prevent by always acquiring locks on shared resources in the same order across the entire application — a code-level fix, not a database configuration one. (Ch.9)
A: Two transactions each holding a lock the other needs, in a circular wait. Prevent by always acquiring locks on shared resources in the same order across the entire application — a code-level fix, not a database configuration one. (Ch.9)
Q: What’s the difference between TDE and Always Encrypted?
A: TDE protects data at rest (stolen files/backups) — an authorized query still sees plaintext. Always Encrypted keeps the server from ever seeing plaintext at all; decryption happens client-side, protecting the data even from a DBA with full query access. (Ch.10)
A: TDE protects data at rest (stolen files/backups) — an authorized query still sees plaintext. Always Encrypted keeps the server from ever seeing plaintext at all; decryption happens client-side, protecting the data even from a DBA with full query access. (Ch.10)
Q: Why shouldn’t an application’s service account be db_owner?
A: Least privilege — a compromised connection with db_owner can drop every table and read every row; the same compromise with a narrowly-scoped role can only do what that role explicitly permits. (Ch.10)
A: Least privilege — a compromised connection with db_owner can drop every table and read every row; the same compromise with a narrowly-scoped role can only do what that role explicitly permits. (Ch.10)
Enjoyed this?
Subscribe to get every new SQL Server lesson as soon as it’s published, and share it with a developer who’d find it useful.
📡 Subscribe via RSS |
Share on X |
Share on LinkedIn |
Share on Facebook
Want the full structured course with quizzes, projects, and 10+ exercises per chapter? Check out SQL Server for Developers & DBAs, coming soon on this site.