SQL Server Interview Questions & Answers (2026)

What is the difference between a clustered index and a non‑clustered index?

A clustered index determines the physical order of rows in the table, so the table can have only one. It stores the data leaf nodes directly in the index, making range scans efficient. A non‑clustered index has a separate structure that contains the indexed columns and a pointer to the data row (the clustering key). It can be created multiple times, allowing different access paths. Interviewers expect you to discuss storage impact, when to use each, and how the choice affects query performance and maintenance.

CREATE CLUSTERED INDEX IX_Orders ON Orders(OrderID);
CREATE NONCLUSTERED INDEX IX_CustomerName ON Orders(CustomerName);

Explain how SQL Server uses execution plans to optimize a query.

When a query is submitted, the optimizer generates multiple potential execution plans, estimating cost based on statistics, indexes, and server configuration. It selects the plan with the lowest estimated cost. The plan details operators (e.g., scans, joins) and their order, which you can view in SSMS. Interviewers look for you to describe cardinality estimation, the role of statistics, and how you can influence the plan using hints or index changes to improve performance.

What are covering indexes and when should you use them?

A covering index includes all columns referenced by a query, so the optimizer can satisfy the query using only the index without touching the base table. This reduces I/O and improves latency, especially for read‑heavy workloads. Use them when a query selects a small set of columns repeatedly and the index size remains manageable. Interviewers expect you to discuss trade‑offs like increased storage and slower DML operations.

CREATE NONCLUSTERED INDEX IX_Covering ON Sales(ProductID) INCLUDE (Quantity, SaleDate);

Explain the difference between a left outer join and a right outer join.

Both return all rows from one side and matching rows from the other. A LEFT OUTER JOIN keeps all rows from the left table, inserting NULLs for non‑matching right‑table rows. A RIGHT OUTER JOIN does the opposite, preserving all rows from the right table. Interviewers look for clarity on result sets, ordering of tables, and typical use cases, such as preserving a master list while optionally adding detail rows.

What is the role of statistics in query optimization and how often should they be updated?

Statistics provide cardinality estimates for column values, guiding the optimizer in choosing join types and index usage. Out‑of‑date statistics can cause inaccurate estimates, leading to poor plans. Auto‑create and auto‑update statistics are enabled by default, but for high‑change tables you may need manual UPDATE STATISTICS or FULLSCAN to keep estimates accurate. Interviewers want you to stress the importance of up‑to‑date statistics for reliable performance.

Explain the difference between a full backup, differential backup, and transaction log backup.

A full backup captures the entire database at a point in time. A differential backup records changes since the last full backup, reducing restore time. A transaction log backup records all log records since the last log backup, enabling point‑in‑time recovery. Interviewers expect you to discuss backup chains, recovery models, and how each type fits into a comprehensive disaster‑recovery strategy.

What is the purpose of the sys.dm_exec_query_stats DMV?

sys.dm_exec_query_stats aggregates runtime statistics for cached query plans, including execution count, total CPU time, and average duration. It helps identify high‑impact queries for tuning. Interviewers look for you to demonstrate how to join it with sys.dm_exec_sql_text to retrieve the actual query text and prioritize optimization efforts based on resource consumption.

How does SQL Server handle deadlocks and how can you prevent them?

SQL Server detects deadlocks by building a wait‑for graph; when a cycle is found, it chooses a victim based on cost and rolls back its transaction. Prevention strategies include ordering accesses consistently, using the lowest possible isolation level, adding appropriate indexes to reduce lock duration, and employing row‑versioning (READ COMMITTED SNAPSHOT). Interviewers want you to explain detection, victim selection, and proactive design choices that minimize deadlock frequency.

What is the purpose of the tempdb database and how should it be configured?

Tempdb stores temporary objects such as table variables, worktables for sorts, and row versioning data. It is recreated on each server start, so its size and file layout affect performance. Best practice: allocate multiple data files (one per CPU core up to 8) of equal size, place them on fast storage, and set an appropriate autogrowth increment. Interviewers look for awareness of contention, allocation bottlenecks, and maintenance considerations.

Describe the differences between READ COMMITTED and SNAPSHOT isolation levels.

READ COMMITTED acquires shared locks on rows during reads, blocking writers and being blocked by exclusive locks. SNAPSHOT uses row versioning; readers see a consistent snapshot without acquiring shared locks, reducing blocking but increasing tempdb usage. SNAPSHOT provides statement‑level consistency, while READ COMMITTED can suffer non‑repeatable reads. Interviewers expect you to discuss concurrency impact, tempdb overhead, and scenarios where each level is appropriate.

How would you identify and resolve a performance bottleneck caused by missing indexes?

Start by examining the execution plan for scans and high‑cost operators. Use DMVs such as sys.dm_db_missing_index_details to find candidate indexes. Validate suggestions by testing in a non‑production environment, ensuring the index improves the target queries without causing excessive write overhead. Explain the trade‑off between read performance gains and increased maintenance cost, and mention index maintenance tasks like reorganize or rebuild.

SELECT * FROM sys.dm_db_missing_index_details;

What are the benefits and drawbacks of partitioning a large table?

Partitioning splits a large table into manageable pieces based on a column (often date), allowing queries to scan only relevant partitions (partition elimination) and simplifying maintenance (e.g., switching out old partitions). Benefits include improved query performance and easier data archiving. Drawbacks are added complexity, potential for uneven data distribution, and increased index management overhead. Interviewers want you to discuss when partitioning is justified and how to monitor its effectiveness.

Explain the difference between a scalar UDF and an inline table‑valued function.

A scalar UDF returns a single value and is executed row‑by‑row, often causing performance penalties because it cannot be inlined. An inline table‑valued function (TVF) is essentially a parameterized view; its definition is merged into the outer query, allowing the optimizer to produce a single plan. Interviewers want you to highlight the impact on execution plans and why inline TVFs are preferred for set‑based logic.

What are the main differences between SQL Server on-premises and Azure SQL Managed Instance regarding backup and restore?

On‑premises backups are manual or scheduled using BACKUP DATABASE, stored on disk or tape, and restores require file access. Azure SQL Managed Instance provides automated backups with point‑in‑time restore, geo‑redundancy, and no need for manual file management. Interviewers look for understanding of backup retention, recovery models, and how managed services simplify DR while still offering control over restore points.

When would you choose a columnstore index over a traditional rowstore index?

Columnstore indexes store data column‑wise, offering high compression and massive performance gains for analytical workloads that scan large tables but retrieve few columns. They are ideal for data warehousing, reporting, and batch processing. However, they incur overhead for frequent DML operations. Interviewers expect you to discuss the read‑heavy nature of columnstores, their impact on insert/update/delete, and scenarios where a hybrid approach (rowstore + columnstore) is beneficial.

How does SQL Server enforce referential integrity, and what are the performance implications of cascading actions?

Referential integrity is enforced via foreign key constraints, which the engine checks on DML operations. Cascading actions (ON DELETE/UPDATE CASCADE) automatically propagate changes, simplifying application logic but potentially causing large, recursive operations that can degrade performance and increase lock duration. Interviewers want you to explain the trade‑off between data integrity convenience and the cost of cascading operations, recommending careful use and monitoring.

How does SQL Server implement row-level locking, and when might you see lock escalation?

SQL Server acquires row‑level locks (X or S) during DML operations, tracked in the lock manager. When many row locks accumulate, the engine may escalate to a page or table lock to reduce overhead. Escalation thresholds are dynamic but can be forced with trace flags. Interviewers expect you to discuss lock granularity, escalation triggers, and mitigation techniques like indexing, query refactoring, or using the ROWLOCK hint sparingly.

What is a query hint and when should you use OPTION (RECOMPILE)?

A query hint forces the optimizer to use a specific plan or behavior, overriding its default cost‑based decisions. OPTION (RECOMPILE) forces a fresh compilation for each execution, useful when parameter sniffing causes suboptimal plans for varying parameter values. Use it judiciously because recompilation adds CPU overhead. Interviewers look for understanding of when plan stability is harmful and how recompilation can improve performance for highly variable queries.

SELECT * FROM Orders WHERE OrderDate = @date OPTION (RECOMPILE);

Describe how to implement and monitor a high‑availability solution using Always On Availability Groups.

Always On Availability Groups replicate databases across secondary replicas, providing automatic failover and read‑only routing. Set up a primary replica, configure synchronous or asynchronous commit, and define a listener for client connections. Monitoring involves checking dm_hadr_* DMVs for replica health, redo latency, and failover readiness. Interviewers expect you to discuss quorum, replica roles, and the trade‑offs between synchronous (high safety) and asynchronous (higher performance) modes.

What is the purpose of the Query Store and how can it help troubleshoot performance regressions?

Query Store captures query texts, plans, and runtime statistics over time, allowing you to compare current performance with historical baselines. By forcing a known good plan or identifying regressions, you can quickly remediate issues caused by plan changes or parameter sniffing. Interviewers look for you to explain enabling Query Store, interpreting its reports, and using plan forcing to stabilize performance.

How do you calculate and interpret the Database Engine Tuning Advisor (DTA) recommendations?

Run DTA against a workload (trace file or extended events) to receive index, statistics, and partitioning suggestions. Evaluate each recommendation by reviewing its estimated cost savings versus added maintenance overhead. Prioritize high‑impact indexes that reduce expensive scans, and test them in a staging environment before production. Interviewers expect you to discuss the balance between performance gains and the long‑term cost of additional indexes.

How does parameter sniffing affect query performance and how can you mitigate it?

When a stored procedure is first compiled, SQL Server uses the initial parameter values to generate an execution plan. If subsequent calls use atypical values, the plan may be suboptimal, leading to slow performance. Mitigation techniques include using OPTION (RECOMPILE), creating local variables, or using OPTIMIZE FOR UNKNOWN to generate a more generic plan. Interviewers expect you to explain the phenomenon and demonstrate practical fixes.

How would you troubleshoot a query that is running slowly due to parameter sniffing?

First, capture the execution plan and identify high‑cost operators. Verify if the plan was generated with atypical parameter values. Then, test alternatives: add OPTION (RECOMPILE), use local variables, or apply OPTIMIZE FOR UNKNOWN. Compare runtimes to confirm improvement. Explain the reasoning behind each step and how you would validate the fix in a test environment before deploying to production.

What is the difference between a deadlock graph and a lock wait graph?

A deadlock graph visualizes a cycle of mutually waiting processes that cause a deadlock, showing the victim and resources involved. A lock wait graph shows individual lock requests and owners without necessarily forming a cycle, useful for diagnosing lock contention before a deadlock occurs. Interviewers look for you to differentiate the two, explain how each is captured (Profiler vs. Extended Events), and discuss remediation strategies.