SQL Server Interview Questions and Answers (2026)

SQL Server interviews often separate candidates by whether they can actually reason about an execution plan and an indexing strategy, not just write correct SQL. Here are the questions that come up most often.

1. What's the difference between a clustered and non-clustered index?

   A clustered index determines the physical storage order of the table's data — the leaf level of the index *is* the data itself, so a table can have only one clustered index (usually on the primary key). A non-clustered index is a separate structure with its own B-tree, storing the indexed column(s) plus a pointer back to the clustered index key (or a row locator/RID if the table is a heap with no clustered index). Because of this, clustered index lookups are naturally fast for range scans on the clustering key, while non-clustered indexes are best for selective lookups on other columns and can include extra columns via INCLUDE to create a covering index that avoids a key lookup back to the base table:

   CREATE NONCLUSTERED INDEX IX_Orders_CustomerId
   ON Orders(CustomerId) INCLUDE (OrderDate, TotalAmount);

2. What's the difference between normalization and denormalization, and when would you denormalize?

   Normalization organizes data to eliminate redundancy and update anomalies, typically following normal forms (1NF through 3NF/BCNF) — splitting data into related tables so each fact is stored once (e.g., separating Customers from Orders rather than repeating customer address on every order row). Denormalization intentionally reintroduces redundancy — duplicating or pre-aggregating data — to reduce the number of joins needed for read-heavy queries, at the cost of update complexity and potential inconsistency. You'd denormalize in reporting/analytics schemas (star schemas), high-traffic read paths where join cost dominates latency budgets, or caching layers/materialized views. A pragmatic approach: design normalized for OLTP correctness, then denormalize selectively (via indexed views, computed columns, or ETL into a reporting store) once profiling shows joins are the actual bottleneck — don't denormalize prematurely.

3. Explain the four transaction isolation levels most commonly discussed and their tradeoffs.

   Read Uncommitted allows dirty reads — you can see another transaction's uncommitted changes, fastest but least safe, rarely appropriate. Read Committed (SQL Server's default) prevents dirty reads by only seeing committed data, but allows non-repeatable reads and phantom reads since the same query can return different rows if re-run within a transaction. Repeatable Read locks the rows it has read to prevent them from changing until the transaction ends, avoiding non-repeatable reads but still permitting phantoms (new rows matching a range predicate can still appear). Serializable is the strictest — it fully prevents phantoms by range-locking, guaranteeing transactions behave as if executed one at a time, but at the highest cost in concurrency/blocking. SQL Server also offers Snapshot Isolation, which uses row versioning instead of locks to give a consistent point-in-time view without blocking readers/writers, trading storage overhead (tempdb) for concurrency.

4. What does ACID stand for and why does it matter for database design?

   Atomicity means a transaction's operations either all succeed or all roll back — no partial updates, enforced via BEGIN TRAN/COMMIT/ROLLBACK. Consistency means a transaction moves the database from one valid state to another, respecting constraints, triggers, and cascades. Isolation means concurrent transactions don't interfere with each other in ways that violate the chosen isolation level. Durability means once a transaction commits, it survives crashes — SQL Server guarantees this via the write-ahead transaction log, which is flushed to disk before a commit is acknowledged. ACID matters because it's the contract that lets application developers reason about correctness under concurrency and failure without manually coordinating every edge case — for example, a funds transfer between two accounts must be atomic so you never end up debiting one account without crediting the other, even if the server crashes mid-operation.

5. Explain the different join types and when each is appropriate.

   INNER JOIN returns only rows with matches in both tables — use it when you only care about records that exist on both sides, e.g., orders that have a valid customer. LEFT JOIN returns all rows from the left table plus matched rows from the right (NULLs where no match) — useful for "show all X, with Y if it exists," like listing all customers including those with zero orders. RIGHT JOIN is the mirror image and rarely used in practice since you can just flip table order and use LEFT JOIN for readability. FULL OUTER JOIN returns all rows from both sides, matched where possible, useful for reconciliation queries (finding mismatches between two datasets).

   SELECT c.CustomerId, c.Name, o.OrderId
   FROM Customers c
   LEFT JOIN Orders o ON o.CustomerId = c.CustomerId
   WHERE o.OrderId IS NULL;  -- customers with no orders

   Also worth mentioning: CROSS JOIN for cartesian products (rare, deliberate) and self-joins for hierarchical data.

6. What's the difference between stored procedures and user-defined functions?

   Stored procedures can perform DML (INSERT/UPDATE/DELETE), manage transactions, use dynamic SQL, return multiple result sets, and have output parameters — they're invoked with EXEC and can't be used inline in a SELECT. Functions (scalar or table-valued) must return a value, cannot modify database state (no side effects) or manage transactions, and can be used directly inside a query, e.g., SELECT dbo.CalculateTax(Amount) FROM Orders or SELECT * FROM dbo.GetOrdersByCustomer(@Id). Scalar functions historically had a major performance pitfall — they often force row-by-row execution and defeat the optimizer's ability to parallelize, so table-valued functions or inline SQL are usually preferred for anything performance-sensitive. Choose a procedure when you need control flow, transactions, or multiple outputs; choose a function when you need a composable, reusable piece of logic embeddable in other queries.

7. A query is running slowly — walk through how you'd approach indexing to fix it.

   First, get the actual execution plan (SET STATISTICS IO, TIME ON plus the graphical plan) to see where time and I/O are going — look for table scans/clustered index scans on large tables, high-cost operators, and large estimated-vs-actual row discrepancies. Check the WHERE, JOIN, and ORDER BY clauses to identify candidate columns for indexes: equality predicates and join keys benefit most from being leading index columns, followed by columns used in range filters or sorts. Consider a covering index that includes all selected columns to avoid key lookups:

   CREATE NONCLUSTERED INDEX IX_Orders_Status_Date
   ON Orders(Status, OrderDate) INCLUDE (CustomerId, TotalAmount);

   Also check for missing statistics or stale statistics (UPDATE STATISTICS), parameter sniffing issues, and implicit conversions (mismatched data types preventing index seeks). Avoid over-indexing, since every index adds write overhead — validate the fix actually reduced logical reads, not just wall-clock time on one run.

8. What causes deadlocks in SQL Server and how would you diagnose one?

   A deadlock occurs when two or more transactions each hold a lock the other needs, forming a cycle with no way to proceed — SQL Server detects this and kills one transaction (the "deadlock victim," usually the cheaper one to roll back) to break the cycle, returning error 1205 to the caller. Common causes are transactions accessing tables in different orders, long-running transactions holding locks too long, or missing indexes causing broader lock scopes (table/page locks instead of row locks). To diagnose, enable trace flag 1222 or use Extended Events with the xml_deadlock_report event to capture the deadlock graph, which shows exactly which resources and queries were involved. The fix usually involves ensuring consistent access order across transactions, keeping transactions short, adding appropriate indexes to reduce lock footprint, and considering snapshot isolation to reduce blocking-based deadlocks altogether.

9. What are window functions and how do they differ from GROUP BY aggregation?

   Window functions compute values across a set of rows related to the current row (a "window") without collapsing the result set into one row per group, unlike GROUP BY which reduces rows. They're defined with OVER (PARTITION BY ... ORDER BY ...). Common examples: ROW_NUMBER(), RANK(), DENSE_RANK() for ranking within groups, and SUM()/AVG() OVER (...) for running totals or per-group aggregates alongside detail rows.

   SELECT CustomerId, OrderDate, TotalAmount,
          SUM(TotalAmount) OVER (PARTITION BY CustomerId ORDER BY OrderDate) AS RunningTotal
   FROM Orders;

   This is invaluable for things like top-N-per-group queries, running totals, and period-over-period comparisons (LAG/LEAD) — all without the self-joins or correlated subqueries that were previously needed, and generally with better performance since the engine can do it in a single pass.

10. Compare CTEs, subqueries, and temp tables — when would you use each?

    A CTE (WITH x AS (...)) improves readability by naming a query block for reuse within the same statement, and is required for recursive queries (e.g., traversing an org chart), but it's not materialized/cached — SQL Server may re-evaluate it each time it's referenced, and it doesn't support its own indexes. A subquery is similar in spirit but often less readable when nested deeply; scalar/correlated subqueries can be a performance trap if they execute row-by-row. A temp table (#temp) is a real, materialized object in tempdb — you can index it, put statistics on it, and reference it multiple times across multiple statements within a batch, making it the right choice for complex multi-step logic or when an intermediate result set is large and reused heavily. Rule of thumb: CTE for readability/recursion within one statement, temp table when you need indexing, reuse across statements, or the optimizer needs help via materialization.

11. What is an execution plan and what are the key things to look for when reading one?

    An execution plan shows how SQL Server's optimizer decided to physically execute a query — which indexes it used, join algorithms chosen (nested loops, hash match, merge join), and the estimated cost of each operator. Key red flags: table scans or clustered index scans on large tables (often means a missing or unused index), large gaps between estimated and actual row counts (stale statistics or parameter sniffing), key lookups appearing many times (missing covering index), and implicit conversions shown as warnings (data type mismatches preventing seeks). Nested loops are efficient for small row counts with an indexed inner input but degrade badly at scale; hash match is typical for large unsorted joins; merge join is efficient when both inputs are already sorted on the join key. You'd pull the actual (not estimated) plan via SET STATISTICS XML ON or SSMS's "Include Actual Execution Plan" to validate real row counts against estimates.

12. What is table partitioning and when does it help?

    Partitioning splits a large table's rows across multiple physical storage units based on a partition function (commonly a date range), while the table still appears logically as one object to queries. It helps primarily with manageability and performance at large scale: partition switching allows near-instant bulk load/archive operations (e.g., swapping out last month's data) without logged row-by-row deletes, and partition elimination lets the optimizer skip scanning irrelevant partitions when the query filters on the partition key.

    CREATE PARTITION FUNCTION PF_OrderDate (DATETIME)
    AS RANGE RIGHT FOR VALUES ('2024-01-01', '2025-01-01', '2026-01-01');

    Partitioning is not a substitute for good indexing — it's most valuable on very large tables (tens of millions+ rows) with a natural time-based or range-based access pattern, such as sliding-window archival of historical data, and it adds administrative complexity that isn't worth it for smaller tables.

More prep guides on the AI Interview Bot blog.