SQL Server interviews cover a strange amount of ground — from “what’s a primary key” all the way to “explain how the lock manager escalates row locks to a table lock, and why that matters for your specific schema.” Most candidates are strong somewhere in that range and shaky somewhere else. This list is built to cover the whole range honestly, the way you’d actually explain each answer out loud, not the way a textbook would define it.
Split into Beginner (1–30), Intermediate (31–70), and Advanced (71–100), every answer leans on reasoning over rote definition — because that’s what actually gets tested once an interview moves past the first few warm-up questions.
One framing worth carrying through the whole list: SQL Server questions almost always have a “textbook answer” and a “the person who’s actually run this in production” answer sitting right next to each other. Wherever that gap exists, these answers lean toward the second one.
Beginner Level
1. What is SQL Server, and what are its main components? A relational database management system from Microsoft. Its main components include the Database Engine (handles storage, processing, and security of data), SQL Server Agent (job scheduling), Reporting Services, Integration Services (ETL), and Analysis Services (OLAP/data mining) — most day-to-day DBA work centers on the Database Engine and Agent.
2. What’s the difference between SQL and T-SQL? SQL is the general, standardized query language for relational databases. T-SQL (Transact-SQL) is Microsoft’s proprietary extension of SQL, adding procedural programming constructs (variables, loops, error handling, TRY...CATCH) that standard SQL doesn’t include — every T-SQL statement is valid in the sense that it builds on SQL, but not every T-SQL feature is portable to another database platform.
3. What is a primary key, and what are its defining properties? A column (or combination of columns) that uniquely identifies each row in a table. It must be unique and cannot contain NULL values, and a table can have only one primary key, though that key can span multiple columns (a composite key).
4. What is a foreign key, and what purpose does it serve? A column that references the primary key (or a unique key) of another table, enforcing referential integrity — it ensures a value in the referencing table must correspond to an existing value in the referenced table, preventing orphaned records.
5. What’s the difference between DELETE, TRUNCATE, and DROP? DELETE removes specific rows based on a WHERE clause (or all rows if no filter is given), is logged row-by-row, and can be rolled back. TRUNCATE removes all rows from a table quickly with minimal logging, resets identity columns, and generally can’t target specific rows. DROP removes the entire table structure itself, not just the data.
6. What’s the difference between WHERE and HAVING? WHERE filters individual rows before any grouping happens. HAVING filters groups after a GROUP BY has been applied — you’d use HAVING specifically when your filter condition involves an aggregate function (like COUNT(*) > 5), since WHERE can’t reference aggregates.
7. What are the different types of joins in SQL Server? INNER JOIN (returns only matching rows from both tables), LEFT JOIN (all rows from the left table plus matches from the right, NULL where there’s no match), RIGHT JOIN (the mirror of LEFT), FULL OUTER JOIN (all rows from both tables, matched where possible), and CROSS JOIN (every combination of rows from both tables, a Cartesian product).
8. What is a subquery, and what’s the difference between a correlated and a non-correlated subquery? A subquery is a query nested inside another query. A non-correlated subquery runs independently and only once, with its result used by the outer query. A correlated subquery references a column from the outer query, meaning it effectively runs once per row of the outer query — generally more expensive as a result.
9. What is normalization, and why does it matter? The process of organizing a database’s tables and columns to minimize data redundancy and avoid update anomalies, typically achieved by breaking data into related tables connected by foreign keys — it matters because redundant data is prone to inconsistency (updating one copy of a fact but not another) and wastes storage.
10. What is denormalization, and when would you use it? The deliberate reintroduction of some redundancy into a normalized design, typically to improve read performance for a specific reporting or analytical query pattern by avoiding expensive joins — a trade-off accepting some write complexity and redundancy in exchange for faster reads, appropriate specifically when read performance for a known pattern matters more than strict normalization.
11. What is a stored procedure, and what are its benefits? A precompiled, saved collection of one or more T-SQL statements stored in the database, callable by name. Benefits include reduced network traffic (calling a procedure versus sending a full query), a degree of plan reuse, centralizing business logic, and providing a security boundary (granting execute permission without granting direct table access).
12. What is a view, and how is it different from a table? A view is a saved, named query that behaves like a virtual table — it doesn’t store data itself (with the exception of an indexed/materialized view), it just presents the result of its underlying query each time it’s referenced. It’s useful for simplifying complex queries, restricting access to specific columns, or providing a stable interface over a schema that might change underneath it.
13. What is an index, in simple terms? A separate data structure built on one or more columns that lets the engine find rows quickly without scanning the entire table — similar to a book’s index letting you jump to a specific page instead of reading the whole book to find something.
14. What’s the difference between a clustered and a non-clustered index? A clustered index determines the physical storage order of the table’s data — there can be only one per table. A non-clustered index is a separate structure with its own order, pointing back to the actual data — a table can have several.
15. What is a NULL value, and how is it different from zero or an empty string? NULL represents an unknown or missing value — it’s not the same as zero (a specific numeric value) or an empty string (a specific, defined string of zero length). This matters practically because comparisons involving NULL don’t behave like normal comparisons — NULL = NULL evaluates to unknown, not true, which is why you use IS NULL instead of = NULL.
16. What is a transaction, and what does ACID stand for? A transaction is a single logical unit of work, typically containing one or more statements that either all succeed or all fail together. ACID stands for Atomicity (all-or-nothing), Consistency (the database moves from one valid state to another), Isolation (concurrent transactions don’t interfere with each other inappropriately), and Durability (once committed, a change survives a crash).
17. What’s the difference between COMMIT and ROLLBACK? COMMIT permanently saves all changes made during a transaction. ROLLBACK undoes all changes made during a transaction, returning the database to the state it was in before the transaction began.
18. What is a constraint, and what are the main types? A rule enforced on a column or table to maintain data integrity. Main types include PRIMARY KEY, FOREIGN KEY, UNIQUE (ensures no duplicate values), CHECK (enforces a custom condition, like a value being positive), NOT NULL (disallows nulls), and DEFAULT (provides a value when none is specified).
19. What’s the difference between UNION and UNION ALL? UNION combines the results of two queries and removes duplicate rows, which requires an extra sorting/comparison step. UNION ALL combines the results without removing duplicates, which is faster since it skips that deduplication work — use UNION ALL whenever you know there won’t be duplicates, or don’t care about them, for the performance benefit.
20. What is a trigger, and what are the main types? A special kind of stored procedure that automatically executes in response to a specific event on a table — INSERT, UPDATE, or DELETE (DML triggers), or events like CREATE/ALTER/DROP (DDL triggers). They’re often used for auditing, enforcing complex business rules beyond what a constraint can express, or maintaining derived data automatically.
21. What’s the difference between a local and a global temporary table? A local temporary table (#TableName) is visible only to the session that created it and is dropped when that session ends. A global temporary table (##TableName) is visible to all sessions and is dropped when the session that created it ends and no other session is actively referencing it.
22. What is the difference between CHAR, VARCHAR, and NVARCHAR? CHAR is fixed-length storage (padded with spaces to its defined length). VARCHAR is variable-length, storing only the actual characters used, saving space for shorter values. NVARCHAR is variable-length Unicode storage, supporting a much broader character set (needed for non-English text), at roughly double the storage cost per character compared to VARCHAR.
23. What is an identity column? A column that automatically generates a sequential numeric value for each new row, commonly used for surrogate primary keys — configured with a starting seed value and an increment, so you don’t have to manually supply a unique value on every insert.
24. What’s the difference between GROUP BY and ORDER BY? GROUP BY collapses rows sharing common values in specified columns into summary rows, typically used with aggregate functions. ORDER BY simply sorts the final result set — they solve different problems and are often used together, but aren’t interchangeable.
25. What is a candidate key, and how is it different from a primary key? A candidate key is any column (or combination) that could serve as a unique identifier for a row — a table can have multiple candidate keys, but only one of them is actually chosen as the primary key; the others remain as alternate keys, often enforced with a UNIQUE constraint.
26. What’s the difference between IN and EXISTS? IN compares a value against a list or subquery result directly. EXISTS checks whether a subquery returns any rows at all, without caring about the actual values returned — EXISTS often performs better for large subquery result sets since the engine can stop as soon as it finds one matching row, rather than materializing the full list IN might need.
27. What is a self-join, and when would you use one? A join where a table is joined to itself, typically using table aliases to distinguish the two references — useful for hierarchical or relational data within a single table, like finding an employee’s manager when both employees and managers live in the same Employees table.
28. What is the SELECT INTO statement, and how is it different from INSERT INTO ... SELECT? SELECT INTO creates a brand-new table based on a query’s result set, copying structure and data together in one step. INSERT INTO ... SELECT inserts rows into an already-existing table — you’d use SELECT INTO when the target table doesn’t exist yet and you want it created automatically from the query shape.
29. What is a composite key? A primary key made up of two or more columns together, where no single column alone is unique, but the combination is — common in junction/bridge tables representing a many-to-many relationship.
30. What’s a simple way to explain the purpose of the GETDATE() function, and what’s a common gotcha with it? It returns the current date and time from the server. A common gotcha: it reflects the server’s time zone, which can cause confusion in a distributed application spanning multiple time zones if developers assume it reflects their own local time or UTC without checking.
Intermediate Level
31. What’s the difference between the four transaction isolation levels most commonly discussed — Read Uncommitted, Read Committed, Repeatable Read, and Serializable? Read Uncommitted allows dirty reads (seeing uncommitted changes from other transactions) — the least strict, most permissive for concurrency. Read Committed (the default) prevents dirty reads but allows non-repeatable reads (a value read twice in the same transaction could change between reads). Repeatable Read prevents that too, but still allows phantom reads (new rows matching a query’s criteria could appear between reads). Serializable is the strictest, preventing all of the above by effectively locking the range of data involved, at the cost of the most concurrency impact.
32. What is a deadlock, and how does SQL Server handle it? A deadlock occurs when two or more transactions each hold a lock the other needs, creating a cycle where neither can proceed. SQL Server automatically detects this cycle and resolves it by choosing one transaction as the “deadlock victim,” rolling it back and returning an error, allowing the other transaction(s) to proceed.
33. What’s the difference between blocking and a deadlock? Blocking is simply one transaction waiting for another to release a lock — it resolves on its own once the blocking transaction finishes, without any special intervention. A deadlock is a genuine cycle of mutual waiting that can’t resolve on its own, requiring the engine to forcibly kill one of the participants.
34. What is a covering index? A non-clustered index that contains every column a specific query needs, either as key columns or included columns, letting the engine satisfy the query entirely from the index without going back to the base table — eliminating a key lookup that would otherwise be needed.
35. What is the difference between a scalar function and a table-valued function? A scalar function returns a single value. A table-valued function returns a table (a result set), which can be used in a FROM clause like a regular table — table-valued functions generally perform better than scalar functions in query contexts, since scalar functions historically forced row-by-row execution that hurt performance at scale (though newer SQL Server versions have improved scalar UDF inlining for some cases).
36. What’s the difference between a stored procedure and a function? A stored procedure can perform actions with side effects (INSERT/UPDATE/DELETE, dynamic SQL, error handling via TRY/CATCH) and doesn’t have to return a value in the same way. A function must return a value (scalar or table), can’t modify database state in the same way, and can be used inline within a SELECT statement, which a stored procedure cannot.
37. What is the CTE (Common Table Expression), and how is it different from a subquery or temp table? A CTE (WITH ... AS) is a named, temporary result set defined for the scope of a single statement — more readable than a nested subquery, and unlike a temp table, it doesn’t persist beyond that statement or require separate cleanup. It’s especially useful for recursive queries (like traversing a hierarchy), which subqueries can’t do at all.
38. What is a recursive CTE, and what’s a common use case? A CTE that references itself, used for traversing hierarchical data — like an organizational chart, finding all employees under a given manager at any depth, by repeatedly joining the CTE’s own result back to the base table until no more matching rows are found.
39. What is the difference between ROW_NUMBER(), RANK(), and DENSE_RANK()? All three assign a sequential number to rows within a result set, typically partitioned and ordered. ROW_NUMBER() gives strictly sequential numbers with no ties (even identical values get different numbers). RANK() gives tied rows the same rank but skips subsequent rank numbers (1,1,3). DENSE_RANK() also ties rows but doesn’t skip numbers afterward (1,1,2).
40. What is index fragmentation, and how do you address it? Over time, as data changes, an index’s physical pages can become disordered or partially empty, degrading its efficiency. You address it via ALTER INDEX ... REORGANIZE (lighter, online, good for moderate fragmentation) or ALTER INDEX ... REBUILD (heavier, more thorough, good for severe fragmentation).
41. What’s the difference between UPDATE STATISTICS and rebuilding an index? UPDATE STATISTICS refreshes the distribution information the optimizer uses to estimate row counts, without touching the index’s physical structure at all. Rebuilding an index recreates the physical structure (defragmenting it) and, as a side effect, also updates statistics with a full scan — they solve related but distinct problems, and a stale-statistics issue doesn’t necessarily require a full index rebuild to fix.
42. What is a computed column? A column whose value is derived from an expression involving other columns in the same table, rather than being directly stored input — it can optionally be persisted (physically stored and even indexed) or left virtual (calculated on the fly whenever queried).
43. What’s the difference between EXEC and sp_executesql for running dynamic SQL? Both execute a dynamically constructed SQL string, but sp_executesql supports parameterization, which allows the engine to reuse cached execution plans for the same query shape with different parameter values, and helps protect against SQL injection when used correctly — EXEC on a concatenated string doesn’t offer either benefit as cleanly.
44. What is SQL injection, and how do parameterized queries prevent it? SQL injection is when untrusted input is concatenated directly into a query string, letting an attacker inject their own SQL logic. Parameterized queries treat input strictly as data, never as executable code, regardless of what characters the input contains — the query structure itself is fixed at compile time and can’t be altered by the parameter’s content.
45. What’s the difference between TRY...CATCH error handling and older @@ERROR-based handling? TRY...CATCH (introduced in SQL Server 2005) provides structured, block-based error handling similar to modern programming languages, capturing detailed error information (via functions like ERROR_MESSAGE(), ERROR_NUMBER()) inside the CATCH block. The older @@ERROR approach required checking after every single statement, was easy to miss, and provided less structured error detail.
46. What is a cursor, and why are cursors generally discouraged in favor of set-based operations? A cursor lets you process a result set row by row, imperatively — useful for genuinely row-by-row logic that can’t be expressed set-based, but generally much slower than an equivalent set-based operation, since SQL Server’s engine is optimized for processing sets of rows together, not iterating one at a time.
47. What’s the difference between MERGE and separate INSERT/UPDATE/DELETE statements for an upsert operation? MERGE combines insert, update, and delete logic into a single statement based on matching a source and target on a specified condition — potentially more efficient and atomic than running separate statements, though MERGE has had some historical edge-case bugs and concurrency considerations worth being aware of, which is why some teams still prefer explicit separate statements for critical upsert logic.
48. What is the difference between a Heap and a table with a clustered index? A heap is a table without a clustered index — its rows aren’t stored in any particular order, and finding a specific row generally requires a full scan unless a non-clustered index helps. A table with a clustered index has its data physically ordered by that index’s key, generally making range queries and lookups by that key more efficient.
49. What’s the purpose of the TOP clause, and how is it different from OFFSET/FETCH? TOP limits the number of rows returned, typically combined with ORDER BY for a meaningful “top N” result. OFFSET/FETCH (introduced in SQL Server 2012) additionally supports skipping a specified number of rows before returning the next batch — the standard mechanism for implementing pagination, which TOP alone doesn’t directly support.
50. What is a linked server, and what’s a common use case? A configuration that lets SQL Server query data from another SQL Server instance (or another data source entirely) as if it were local, using a four-part naming convention — commonly used for cross-database or cross-server reporting queries without needing a separate ETL process, though it comes with performance and security considerations worth understanding before relying on it heavily.
51. What’s the difference between sp_who2 and sys.dm_exec_requests/sys.dm_exec_sessions? sp_who2 is an older, simpler system procedure showing basic session and blocking information. The DMVs (sys.dm_exec_requests, sys.dm_exec_sessions) provide much richer, more granular, and joinable data — modern troubleshooting generally favors the DMVs for their flexibility, though sp_who2 remains a quick, familiar habit for many DBAs.
52. What is the difference between a schema and a database in SQL Server? A database is the top-level container holding tables, views, procedures, and other objects, along with its own security boundary and files. A schema is a logical namespace within a database that groups objects together (like dbo, or a custom schema like sales), primarily for organization and permission management, not a separate physical storage boundary.
53. What’s the significance of the dbo schema, and can you create objects in a different schema? dbo is the default schema every database starts with, and objects created without specifying a schema land there by default. Yes, you can (and in a well-organized database with multiple logical domains, often should) create custom schemas to group related objects and manage permissions at the schema level rather than granting access object by object.
54. What is a filtered index, and when would you use one? An index that includes only rows matching a specified WHERE predicate rather than the whole table — useful when queries consistently filter on the same condition (like WHERE IsActive = 1), since the filtered index is smaller and cheaper to maintain than a full-table index, at the cost of only being usable for queries whose filter matches the index’s predicate.
55. What’s the difference between IDENTITY and SEQUENCE? IDENTITY is tied directly to a specific column in a specific table, automatically generating the next value on insert. SEQUENCE is a standalone, table-independent object that generates sequential numbers you can use across multiple tables or even outside a direct insert context, offering more flexibility (like generating a value before an insert actually happens) than IDENTITY provides.
56. What is database mirroring, and why is it now considered a deprecated feature? An older SQL Server high-availability feature that maintained a synchronized copy of a database on a separate server, supporting automatic failover. It’s deprecated in favor of Always On Availability Groups, which offer more flexibility (multiple replicas, readable secondaries, more granular failover options) than mirroring’s more limited single-secondary model ever supported.
57. What’s the difference between a full backup, a differential backup, and a transaction log backup? A full backup captures the entire database at a point in time. A differential backup captures only the changes since the last full backup. A transaction log backup captures the log records since the last log backup, and is specifically what enables point-in-time restore and allows the log to be truncated (kept from growing indefinitely) in the full or bulk-logged recovery models.
58. What are the three recovery models, and what’s the practical difference? Simple (transaction log is automatically truncated, no point-in-time restore possible, minimal log management overhead), Full (log is retained until backed up, enabling point-in-time restore, requires regular log backups to control log growth), and Bulk-Logged (similar to Full but minimally logs certain bulk operations for performance, with some limitations on point-in-time restore during those bulk operations).
59. What is the difference between sp_addextendedproperty and a regular column comment approach some other databases support? SQL Server doesn’t have a native inline comment syntax for column/table metadata the way some other database systems do — sp_addextendedproperty (and related extended properties functions) is the mechanism for attaching custom metadata like descriptions to database objects, retrievable later via sys.extended_properties or the various fn_listextendedproperty functions.
60. What’s the difference between sys.objects and INFORMATION_SCHEMA views for querying database metadata? sys.objects (and the broader system catalog views) are SQL Server-specific, giving deep, engine-specific metadata. INFORMATION_SCHEMA views are ANSI-standard, more portable across different database systems if you need code that might run against multiple platforms, but generally expose less SQL-Server-specific detail than the native catalog views do.
61. What is the purpose of the WITH (NOLOCK) hint, and what’s the risk of using it? It tells the engine to read data without taking shared locks, avoiding being blocked by other transactions’ locks and avoiding blocking writers itself — but the risk is reading uncommitted, potentially inconsistent data (dirty reads), including the possibility of missing rows or reading rows twice during a concurrent modification, which makes it inappropriate for anything requiring guaranteed data accuracy.
62. What’s the difference between PIVOT/UNPIVOT and using conditional aggregation (CASE inside SUM/COUNT) to reshape data? PIVOT/UNPIVOT provide dedicated syntax for rotating rows into columns (or vice versa), which can be more concise for straightforward cases. Conditional aggregation using CASE inside aggregate functions is more flexible and often clearer for complex reshaping logic, and many developers prefer it specifically because PIVOT‘s syntax can become awkward for anything beyond the simplest pivot scenario.
63. What is the difference between an inner-only join and an anti-join pattern (like NOT EXISTS or LEFT JOIN ... WHERE right.col IS NULL)? An inner join returns only matching rows. An anti-join pattern specifically finds rows in one table that have no match in another — a common need (find customers who’ve never placed an order) that a straightforward join alone doesn’t directly express, requiring one of these specific patterns instead.
64. What’s the difference between DATEDIFF and DATEADD, and a common mistake when using DATEDIFF? DATEDIFF calculates the difference between two dates in a specified unit (days, months, years). DATEADD adds a specified interval to a date. A common mistake with DATEDIFF is assuming it calculates a precise elapsed duration — it actually counts the number of unit boundaries crossed, meaning DATEDIFF(year, '2023-12-31', '2024-01-01') returns 1 despite only one day having passed, which surprises people expecting a true elapsed-time calculation.
65. What is the purpose of SET NOCOUNT ON, and why is it commonly included at the start of stored procedures? It suppresses the “X rows affected” message that would otherwise be sent back after each statement — reducing unnecessary network traffic and, in some client libraries, avoiding subtle issues where that extra message interferes with result set processing, which is why it’s a very common best-practice default at the start of stored procedures.
66. What’s the difference between an equi-join and a non-equi-join? An equi-join uses an equality condition (table1.col = table2.col) as its join predicate — the vast majority of joins in practice. A non-equi-join uses a different comparison operator (<, >, BETWEEN) — less common, but useful for scenarios like joining a value against a date range table or a tiered pricing structure.
67. What is the purpose of a synonym in SQL Server? A synonym is an alias for another database object (a table, view, or procedure, potentially on a different server via a linked server), letting code reference the synonym name instead of the full object name — useful for abstracting away the actual location of an object, so if it moves, only the synonym’s definition needs updating rather than every piece of code referencing it directly.
68. What’s the difference between a materialized (indexed) view and a regular view? A regular view is just a saved query, recalculated every time it’s referenced. An indexed view actually has a unique clustered index created on it, causing the engine to physically store and maintain its result set — trading write overhead (the view must be kept in sync with underlying data changes) for potentially much faster reads, similar to the trade-off with any index.
69. What is the difference between CROSS APPLY and OUTER APPLY? Both let you invoke a table-valued function or a correlated subquery-like expression once per row of the outer query, referencing that outer row’s columns (something a plain join can’t do). CROSS APPLY behaves like an inner join, only returning outer rows that produce at least one row from the applied expression. OUTER APPLY behaves like a left join, keeping outer rows even when the applied expression returns nothing.
70. What’s a practical reason to prefer EXISTS over COUNT(*) > 0 when checking if any matching rows exist? EXISTS can stop as soon as it finds a single matching row, since it only needs to know whether at least one exists. COUNT(*) has to actually count every matching row before the comparison can even happen, doing unnecessary extra work when all you actually needed to know was yes-or-no.
Advanced Level
71. Explain how the SQL Server lock manager escalates row-level or page-level locks to a table-level lock, and why that matters for application design. When a single transaction acquires enough fine-grained locks on a table (the threshold is roughly 5,000 locks by default, though it’s dynamic), the engine escalates to a table-level lock to reduce the memory overhead of tracking so many individual locks — but this means a large batch operation can suddenly block many other concurrent transactions against that entire table, even ones touching completely different rows, which is a strong argument for processing very large batch updates/deletes in smaller chunks rather than one enormous transaction.
72. Explain the Halloween Protocol and why it can affect the plan and performance of an UPDATE statement. The Halloween Protocol refers to the risk of a row being processed more than once within the same statement, if an update changes a value that the statement’s own scan or seek is using to identify rows to process — the engine defends against this, sometimes by introducing a Spool operator or forcing a particular processing order in the plan, which adds overhead but is necessary for correctness; understanding this explains why some UPDATE plans look more complex than an equivalent SELECT would.
73. What’s the difference between optimistic and pessimistic concurrency control, and how does SQL Server’s Read Committed Snapshot Isolation (RCSI) relate to this? Pessimistic concurrency assumes conflicts are likely and uses locks to prevent them upfront (readers block writers and vice versa under standard locking). Optimistic concurrency assumes conflicts are rare and instead detects them (or avoids them) without blocking — RCSI implements a form of this by giving readers a consistent, versioned snapshot of data (via row versioning in tempdb) instead of taking locks, meaning readers don’t block writers and writers don’t block readers, at the cost of tempdb overhead for maintaining those versions.
74. Explain the difference between SNAPSHOT isolation level and READ COMMITTED SNAPSHOT (RCSI), since both use row versioning. RCSI changes the behavior of the default Read Committed isolation level itself — each individual statement sees a snapshot as of that statement’s start, still allowing non-repeatable reads within a transaction across multiple statements. SNAPSHOT isolation is a distinct, explicitly-requested isolation level where the entire transaction sees a consistent snapshot from its start, preventing non-repeatable reads entirely — and unlike RCSI, SNAPSHOT isolation can produce write-write conflicts that cause an error if two transactions try to modify the same row based on the same starting snapshot.
75. Explain how the query optimizer’s cost-based model influences plan selection, and what “Good Enough Plan Found” versus “Time Out” means for a specific plan. The optimizer searches a space of candidate plans, assigning each an estimated cost based on cardinality estimates and available access paths, and picks the lowest-cost plan found within a bounded search effort. “Good Enough Plan Found” means the search concluded normally, having found a plan meeting its internal threshold. “Time Out” means the optimizer’s search itself ran out of allotted time and settled for the best plan found so far, which may not be the true optimal plan — a useful thing to check (via the plan’s properties) when a genuinely complex query’s plan seems suboptimal, since the cause might be search-time exhaustion rather than a data or index problem.
76. What is parameter sniffing, and explain at least two distinct mitigation strategies with their respective trade-offs. Parameter sniffing is when a cached plan, compiled based on the first parameter value used, gets reused for later executions with very different parameter values that would have benefited from a different plan. OPTION (RECOMPILE) forces a fresh, parameter-specific plan every execution — always tuned to the actual parameters, but with real, repeated compilation overhead. OPTIMIZE FOR a specific representative value biases the optimizer toward a chosen “typical” case, cheap to apply but potentially still wrong for genuinely outlier parameter values. Automatic Tuning’s FORCE LAST GOOD PLAN reactively catches and reverts a regression after Query Store detects it, which is low-effort but inherently after-the-fact rather than preventive.
77. Explain the difference between Nested Loops, Hash Match, and Merge Join at a level that explains their memory and I/O characteristics, not just when each is chosen. Nested Loops iterates the outer input, probing the inner input (ideally via an efficient index seek) for each outer row — minimal memory overhead, but cost scales with outer row count times inner probe cost, meaning it degrades badly if the outer input turns out much larger than estimated. Hash Match builds an in-memory hash table from the smaller (build) input, then probes it with the other (probe) input — requires a memory grant sized for the build input, and spills to tempdb if that grant is insufficient, which is a common, specific cause of unexpectedly slow hash joins on large inputs. Merge Join walks two already-sorted inputs together in a single synchronized pass — very efficient with minimal memory needs, but entirely dependent on both inputs already being sorted on the join key, either via an index or an explicit (and potentially costly) sort operator feeding into it.
78. Explain how Intelligent Query Processing’s Memory Grant Feedback works, and a scenario where it wouldn’t fully solve a memory grant problem. Memory Grant Feedback observes a query’s actual memory usage across executions and adjusts the granted memory for future executions of the same cached plan accordingly, correcting for a compile-time estimate that was too generous (wasting memory) or too conservative (causing a spill). It wouldn’t fully solve the problem for a query whose actual memory need varies dramatically and legitimately between executions (due to genuinely different parameter-driven row counts) — feedback adjusts toward an average or recent pattern, which can still be wrong for a specific outlier execution, similar in spirit to how parameter sniffing affects plan choice.
79. Explain how tempdb contention manifests (PAGELATCH waits on specific system pages), and how multiple tempdb data files address it. Under heavy concurrent tempdb usage (many sessions creating/dropping temp objects, sorting, or spilling simultaneously), contention on tempdb’s internal allocation system pages (like PFS, GAM, SGAM pages) can become a bottleneck, visible as PAGELATCH_UP/PAGELATCH_EX waits on tempdb-specific page IDs. Configuring multiple tempdb data files (a long-standing best practice, with modern SQL Server increasingly auto-configuring this appropriately) distributes that allocation activity across multiple files, reducing contention on any single file’s allocation pages — though it doesn’t address tempdb contention caused by inefficient queries themselves generating excessive tempdb usage in the first place, which is a query-tuning problem, not a file-configuration one.
80. Explain the trade-offs of using SNAPSHOT isolation level broadly across an application versus using it selectively for specific problematic queries. Broad adoption gives consistent, blocking-free reads across the whole application, but requires tempdb capacity planning for the increased versioning overhead at scale, and introduces the possibility of write-write conflict errors application code needs to be prepared to catch and retry, which is a genuine application-design change, not just a database configuration flip. Selective use on specific known-problematic queries (like a long-running report currently causing blocking) limits the tempdb overhead and conflict-handling burden to just those cases, but requires identifying and updating each relevant query individually rather than getting a blanket fix.
81. Explain how Always On Availability Groups achieve high availability, and the difference between synchronous-commit and asynchronous-commit availability modes. An Availability Group maintains one or more secondary replicas of a set of databases, kept in sync via log record shipping and redo. In synchronous-commit mode, the primary waits for a secondary to confirm the log record has been hardened before considering the transaction committed, guaranteeing zero data loss on failover to that specific secondary at the cost of added commit latency. In asynchronous-commit mode, the primary doesn’t wait, offering better primary-side write performance but accepting potential data loss if a failover to that secondary occurs before it’s fully caught up — commonly used for a geographically distant DR replica where synchronous commit’s latency cost would be unacceptable.
82. Explain automatic versus manual failover in an Availability Group, and what conditions must be true for automatic failover to actually be available for a given replica. Automatic failover requires the replica to be configured in synchronous-commit mode with automatic failover mode enabled, and part of a Windows Server Failover Cluster (or the equivalent quorum mechanism) that can detect the primary’s failure and coordinate the role switch — asynchronous-commit replicas can never participate in automatic failover, only manual (with potential data loss) or forced failover, since automatic failover specifically depends on the guarantee that the target replica is fully caught up, which only synchronous commit provides.
83. Explain how you’d diagnose and resolve excessive log flush wait times (WRITELOG waits) on a high-throughput OLTP system. WRITELOG waits indicate transactions are waiting on the transaction log’s physical write (flush) to disk to complete before the transaction can be considered committed — I’d check the underlying storage’s actual write latency and throughput capability for the log file specifically (log writes are sequential and latency-sensitive, benefiting from fast, dedicated storage), consider whether the transaction log is on appropriately fast storage separate from data files, and look at whether the application is committing in very small, frequent transactions where batching several logical operations into fewer, larger transactions could reduce the total number of log flushes needed.
84. Explain the interaction between statistics, cardinality estimation, and the “ascending key problem,” and how Azure SQL/modern SQL Server addresses it. The ascending key problem occurs when a column’s values are continuously increasing (like an identity column or a timestamp) and new rows are inserted faster than statistics are updated — queries filtering on recent values (which didn’t exist when statistics were last sampled) get poor cardinality estimates, since the optimizer has no statistical knowledge of that new range. Modern SQL Server (since 2014, refined further since) improves this via trace flag behavior changes (later made default) that better estimate beyond the statistics histogram’s known range, plus more responsive auto-update statistics thresholds for large tables, reducing but not entirely eliminating the practical impact of this pattern.
85. Explain how you’d design a batching strategy for a large-scale DELETE operation on a table with millions of rows, minimizing lock escalation and transaction log growth. I’d delete in smaller batches (using TOP (n) ... DELETE in a loop, or a similar chunking pattern) with each batch as its own committed transaction, specifically keeping each batch’s row count comfortably under the lock escalation threshold to avoid escalating to a table lock that would block concurrent activity, and I’d monitor transaction log growth during the process (especially in Full recovery model) to ensure log backups are keeping pace, since a very large single-transaction delete can cause substantial log growth that a batched approach with intermediate commits avoids.
86. Explain the difference between a plan guide and Query Store’s forced plan feature, including a scenario where you might still need a plan guide. Both influence which plan the optimizer uses for a query, but Query Store’s forcing mechanism operates on plans Query Store has itself captured and requires that infrastructure to be active. Plan guides are a more general, longstanding mechanism that can apply a hint or an explicit plan to a query matched by its text, usable even in scenarios or SQL Server editions where Query Store isn’t available or hasn’t captured the specific plan you want to force — plan guides remain relevant specifically for those cases, or for applying a hint to a query you can’t directly modify (like inside a vendor application’s code).
87. Explain how the SQL Server buffer pool works, and how Page Life Expectancy (PLE) relates to genuine memory pressure diagnosis. The buffer pool is an in-memory cache of data pages, avoiding physical disk reads for frequently accessed data — PLE estimates how long, in seconds, a page would stay in the buffer pool before being evicted under current memory pressure. A low or sharply dropping PLE suggests pages are being evicted and re-read from disk more than ideal, but I’d treat it as one signal among several (alongside actual PAGEIOLATCH wait statistics and buffer pool hit ratio) rather than a standalone diagnosis, since PLE alone doesn’t tell you which query or process is driving the pressure — that requires further investigation into what’s actually consuming buffer pool space.
88. Explain the concept of a “residual predicate” in an execution plan, and why it matters for understanding a Filter operator’s true selectivity. A residual predicate is a filter condition applied by an operator (often a Filter, but sometimes embedded within a Seek/Scan’s properties as a non-seekable “predicate” versus a truly seekable “seek predicate”) after the initial access method has already retrieved rows — meaning the operator’s input row count and output row count can differ significantly even for what looks like a single operator, and understanding which portion of a predicate is genuinely driving the index access versus which portion is just filtering afterward is key to correctly diagnosing why a seemingly indexed query is still reading more rows than expected.
89. Explain how In-Memory OLTP (memory-optimized tables) fundamentally differs from disk-based tables with a well-tuned buffer pool cache, and where the real performance gain comes from. It’s not just “data lives in memory” — disk-based tables cached in the buffer pool are also effectively served from memory once warm. In-Memory OLTP’s real gain comes from a fundamentally different, lock-free and latch-free concurrency model (using optimistic, multi-version concurrency control at the row level) and natively compiled stored procedures that eliminate much of the interpretation overhead of normal T-SQL execution — the gain is specifically about eliminating locking/latching contention and execution overhead, not primarily about avoiding disk I/O, which a well-cached disk-based table has already largely avoided anyway.
90. Explain how you’d diagnose a scenario where a query’s actual execution time varies dramatically between runs, even with an identical cached plan and no apparent data changes. I’d suspect resource contention under concurrency (the plan itself isn’t the variable, real-time competition for CPU, memory grants, or locks is) — checking whether other concurrent activity, blocking, or tempdb pressure correlates with the slower runs via sys.dm_exec_requests and wait statistics captured during an actual slow execution, rather than assuming the plan itself must somehow be inconsistent, since an identical cached plan producing variable actual performance is a strong signal the variability lives in the runtime environment, not the plan.
91. Explain the difference between a covering index and index intersection, and why SQL Server generally favors the former where possible. A covering index satisfies an entire query from one single index. Index intersection is when the optimizer combines results from two or more separate, narrower indexes to satisfy a query, which requires an additional join/intersection operation between them — generally more expensive than a single covering index seek, which is why the optimizer (and a DBA designing indexes deliberately) generally prefers a well-designed covering index over relying on intersection, reserving intersection as a fallback the optimizer might use when no single covering index exists.
92. Explain how replication (transactional replication specifically) works at a mechanical level, and a scenario where it’s still the right tool despite Always On Availability Groups being available. Transactional replication captures committed transactions from the publisher’s transaction log (via a Log Reader Agent), stores them in a distribution database, and applies them to one or more subscribers via a Distribution Agent — fundamentally different from Always On’s log-shipping/redo mechanism, since replication operates at the logical (statement/row) level rather than the physical log-record level, which is exactly what allows subscribers to have a different schema, different indexing, or even receive only a filtered subset of the published data. This makes it the right tool when you need selective, transformed, or partial data distribution — reporting subscribers needing extra indexes the publisher doesn’t have, or feeding a subset of data to a downstream system — none of which Always On’s all-or-nothing, schema-identical replica model supports.
93. Explain the concept of “poison waits” and why certain wait types (like THREADPOOL or RESOURCE_SEMAPHORE) deserve escalated urgency compared to more routine waits. Certain wait types indicate the system is fundamentally starved of a resource needed for basic forward progress — THREADPOOL waits mean the server has run out of available worker threads, potentially preventing new connections or requests from being serviced at all, and RESOURCE_SEMAPHORE waits mean queries are queued waiting for memory grants that aren’t available, which can cascade into broader unavailability rather than just one slow query. These deserve escalated urgency over more routine waits (like ordinary PAGEIOLATCH I/O waits) because they threaten the system’s overall ability to function, not just one query’s individual performance.
94. Explain the difference between a Filtered Statistics object and a regular statistics object, and a scenario where filtered statistics genuinely improve cardinality estimation. A regular statistics object summarizes the distribution of an entire column’s values across the whole table. A filtered statistics object summarizes distribution only within a specific subset matching a predicate (similar in spirit to a filtered index) — genuinely useful when a column’s distribution differs meaningfully between a common subset and the whole table (for example, an OrderStatus column where 95% of rows are ‘Completed,’ but queries almost always filter for the much rarer ‘Pending’ status) — a filtered statistics object specific to that rare subset gives the optimizer a far more accurate cardinality estimate for that specific, common query pattern than the whole-table statistics object would.
95. Explain how you’d approach diagnosing and resolving a case where CHECKDB reports corruption on a production database, balancing data recovery against data loss risk. I’d first determine the corruption’s scope and severity from CHECKDB’s actual output (a single, isolated page versus something more structurally severe), then prioritize restoring from a known-good backup taken before the corruption occurred as the safest recovery path if the corruption’s onset time can be identified, since that avoids the risk inherent in REPAIR_ALLOW_DATA_LOSS — which, as its name states plainly, can itself delete data to resolve consistency errors and should generally be treated as a last resort after backup-based recovery options have been genuinely exhausted, not a routine first response.
96. Explain how Change Data Capture (CDC) differs from Change Tracking, and when you’d choose one over the other. Change Tracking records that a row changed (and its primary key), without capturing the actual historical values — lightweight, good for synchronization scenarios where you just need to know what changed since a given point to go re-fetch current values. Change Data Capture captures the actual historical before/after values of each change into change tables, using the transaction log via a capture job — heavier overhead, but appropriate when you genuinely need the historical detail of what changed, not just that something did, such as feeding a downstream ETL/auditing pipeline that needs the actual old and new values.
97. Explain a scenario where a seemingly well-indexed query still performs a full scan, due to a non-obvious sargability issue. A predicate wrapping an indexed column in a function or implicit conversion — WHERE CONVERT(varchar, OrderDate) = '2024-01-01' or WHERE UPPER(LastName) = 'SMITH' — prevents the optimizer from using an index seek on that column at all, even though the column itself is indexed, because the engine would have to evaluate that function against every row to know which ones match, which is functionally equivalent to a scan; the fix is rewriting the predicate to leave the indexed column bare and apply any necessary transformation to the comparison value instead.
98. Explain the concept of plan cache bloat from ad hoc, non-parameterized queries, and how “optimize for ad hoc workloads” addresses it. Every distinct, non-parameterized query text (even ones differing only by a literal value) gets its own separate cached plan entry, which for an application generating many slightly different ad hoc queries can bloat the plan cache with single-use plans that are never reused, consuming memory without benefit. The optimize for ad hoc workloads server-level setting changes behavior so a query’s first execution only stores a lightweight “plan stub” rather than the full compiled plan, only creating and caching the full plan on a second execution of that exact same query text — reducing memory waste from genuinely one-off queries while still fully caching queries that do recur.
99. Explain how you’d diagnose a scenario where restoring a database from backup takes far longer than expected, given database size alone doesn’t explain it. I’d check the number and size of VLFs (Virtual Log Files) in the transaction log, since an excessively fragmented log (many small VLFs, often from historically undersized log auto-growth settings) can meaningfully slow both backup and restore operations; I’d also check the underlying storage’s actual throughput during the restore versus its theoretical capability, and whether the restore is competing with other concurrent I/O-heavy activity on the same storage, since restore time genuinely isn’t just a function of logical database size — physical layout and current system load both matter.
100. Walk through a genuinely difficult SQL Server problem you’ve solved or would expect to solve, demonstrating your diagnostic process end to end. Intentionally open-ended, since interviewers are evaluating process and depth, not a memorized answer. A strong structure to demonstrate regardless of the specific problem: (1) gather objective evidence first — DMVs, wait statistics, actual execution plans — rather than acting on a vague symptom description, (2) form a specific, testable hypothesis grounded in that evidence rather than jumping to a familiar-feeling fix, (3) validate the hypothesis cheaply before committing to a change, (4) apply the narrowest fix that addresses the confirmed root cause, and (5) confirm afterward, with the same objective tools, that the fix actually worked. Interviewers at every level of this list consistently favor candidates who can point to a specific number, wait type, or operator and explain precisely why it mattered, over candidates who can only describe SQL Server concepts in the abstract without grounding them in something they’ve actually measured.
A Closing Thought
The thread running through this whole list, from the beginner definitions to the deepest internals questions: SQL Server rewards people who reach for actual evidence — a wait type, a row count, a plan operator — over people who reach for a remembered rule of thumb. The rule of thumb gets you through the first ten questions of an interview; the evidence-based habit is what gets you through the rest of a real career.
Discover more from Technology with Vivek Johari
Subscribe to get the latest posts sent to your email.
