SQL (5 Years Experience) Interview Questions & Answers (2026)

Explain how you would identify and resolve a slow‑running query in production.

First, capture the execution plan using EXPLAIN (or SET STATISTICS IO/TIME). Look for scans, missing indexes, and high cost operators. Next, verify statistics are up‑to‑date; if not, run UPDATE STATISTICS. Add or adjust indexes focusing on the WHERE and JOIN columns, but watch for write‑overhead. Finally, rewrite the query to reduce row‑wise operations—use set‑based logic, CTEs, or window functions where appropriate. A strong candidate also mentions monitoring tools (e.g., Query Store) and the importance of testing changes in a staging environment before production rollout.

EXPLAIN ANALYZE SELECT ...

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

A clustered index determines the physical order of rows in the table; there can be only one per table because the data can be sorted in only one way. A non‑clustered index stores a separate structure with pointers to the data rows, allowing many such indexes. Interviewers expect you to discuss impact on range scans (clustered is optimal) and write overhead (non‑clustered adds extra page writes). A strong answer also notes that primary keys often become clustered by default, but you can override that for performance reasons.

What are the ACID properties and how does SQL Server enforce them?

ACID stands for Atomicity, Consistency, Isolation, Durability. SQL Server enforces atomicity with transaction logs, ensuring all statements succeed or roll back. Consistency is maintained via constraints (FKs, CHECK) and triggers. Isolation levels (READ COMMITTED, SNAPSHOT, etc.) control visibility of intermediate states, implemented through locking or row versioning. Durability is achieved by writing committed transactions to disk before acknowledging success. A strong candidate may discuss trade‑offs of higher isolation (e.g., SERIALIZABLE) versus concurrency.

What is the difference between UNION ALL and UNION, and when should you prefer each?

UNION removes duplicate rows by performing a distinct sort, which adds overhead; UNION ALL simply concatenates result sets, preserving duplicates and being faster. Prefer UNION ALL when you know data sets are distinct or duplicates are acceptable, especially in large data pipelines. Use UNION when you need a deduplicated result. Interviewers look for awareness of the extra sorting cost and potential impact on execution plans.

How do you handle NULL values in aggregate functions?

Aggregate functions ignore NULLs by default; COUNT(*) counts rows, while COUNT(column) excludes NULLs. To include NULLs as a specific value, use COALESCE or ISNULL inside the aggregate, e.g., SUM(ISNULL(sales,0)). Interviewers may ask you to demonstrate this with a query and explain why ignoring NULLs matters for accurate business metrics.

SELECT SUM(ISNULL(sales,0)) FROM Transactions;

Explain the difference between a LEFT JOIN and a LEFT OUTER JOIN.

There is no functional difference; LEFT JOIN is shorthand for LEFT OUTER JOIN. Both return all rows from the left table and matching rows from the right, with NULLs when no match exists. Interviewers ask this to confirm you know that OUTER is optional and that the semantics are identical.

How do you convert a string to a date in SQL Server safely?

Use TRY_CONVERT or TRY_CAST to attempt conversion without raising an error: SELECT TRY_CONVERT(date, date_string, 112) FROM table. If conversion fails, NULL is returned, allowing you to filter or handle bad data. Interviewers want to see that you avoid implicit conversion errors and handle format variations explicitly.

SELECT TRY_CONVERT(date, '20230115', 112);

How do window functions differ from aggregate functions?

Aggregate functions collapse rows into a single result per group, losing row‑level detail. Window functions compute a value across a defined window while preserving each row, using OVER() with PARTITION BY and ORDER BY. Interviewers look for examples like ROW_NUMBER() for pagination or SUM() OVER (PARTITION BY dept) to show departmental totals alongside individual rows. Emphasize that window functions avoid costly self‑joins and can be more readable, but they still require careful indexing to avoid full scans.

SELECT employee_id, salary, SUM(salary) OVER (PARTITION BY department_id) AS dept_total FROM employees;

Describe a scenario where you would use a CTE versus a derived table.

Use a CTE when the subquery is recursive or when you need to reference the same derived set multiple times for readability. For example, building an organizational hierarchy with a recursive CTE clarifies the logic. A derived table is appropriate for a one‑off inline view, especially if the optimizer can materialize it efficiently. Interviewers appreciate that CTEs can sometimes inhibit push‑down predicates, so you should mention testing performance and possibly switching to a derived table if the plan shows a spool.

WITH RECURSIVE Org AS (SELECT ... UNION ALL SELECT ... FROM Org)

How would you write a query to find the second highest salary in an employee table?

Use a window function to rank salaries and then filter: SELECT employee_id, salary FROM (SELECT employee_id, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk FROM employees) t WHERE rnk = 2. This avoids self‑joins and works even with duplicate salaries. Interviewers expect you to explain why DENSE_RANK is preferred over ROW_NUMBER when duplicates exist, and that the sub‑query isolates the ranking before the outer filter.

SELECT employee_id, salary FROM (SELECT employee_id, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk FROM employees) AS sub WHERE rnk = 2;

How would you implement pagination in SQL Server efficiently?

Use OFFSET‑FETCH with an ORDER BY clause: SELECT columns FROM table ORDER BY key OFFSET @PageSize * (@PageNumber-1) ROWS FETCH NEXT @PageSize ROWS ONLY. Ensure the ORDER BY column is indexed to avoid scanning the entire table. For older versions, use ROW_NUMBER() in a CTE and filter on the row number. A strong answer also mentions avoiding large OFFSET values by using keyset pagination (WHERE key > last_key) for better performance on very large tables.

SELECT * FROM Orders ORDER BY OrderID OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

What are common table expressions (CTEs) and how do they differ from temporary tables?

CTEs are logical, temporary result sets defined within a single query using WITH. They exist only for the duration of that statement and are not materialized unless the optimizer decides to spool them. Temporary tables (#temp) are physical objects stored in tempdb, can be indexed, and persist across multiple statements in a session. Use CTEs for readability and recursion; use temp tables when you need indexing, statistics, or to reuse the data across multiple queries. Interviewers look for awareness of optimizer behavior and performance implications.

How would you write a query to calculate a running total per department?

Use a window function with ORDER BY and ROWS UNBOUNDED PRECEDING: SELECT department_id, employee_id, salary, SUM(salary) OVER (PARTITION BY department_id ORDER BY employee_id ROWS UNBOUNDED PRECEDING) AS running_total FROM employees. This computes the cumulative sum efficiently without self‑joins. Explain that the optimizer can stream the rows, and that proper indexing on department_id and employee_id improves performance.

SELECT dept_id, emp_id, salary, SUM(salary) OVER (PARTITION BY dept_id ORDER BY emp_id ROWS UNBOUNDED PRECEDING) AS running_total FROM emp;

What is a foreign key cascade delete and when might you avoid it?

Cascade delete automatically removes child rows when a parent row is deleted, defined with ON DELETE CASCADE. It simplifies cleanup but can cause large, unintended data loss if a parent row is mistakenly removed, and can lead to performance spikes due to massive cascades. Avoid it in high‑transaction tables or when you need audit trails; instead, handle deletions in application logic or use soft deletes.

How would you rewrite a correlated subquery as a JOIN for better performance?

Correlated subqueries execute once per outer row, which can be costly. Convert it to a LEFT JOIN with aggregation: SELECT t.id, agg.value FROM main t LEFT JOIN (SELECT foreign_id, MAX(value) AS value FROM related GROUP BY foreign_id) r ON t.id = r.foreign_id. This allows the optimizer to compute the aggregation once and reuse it, often resulting in a hash or merge join rather than nested loops.

Explain the purpose of the SQL Server Query Store and when you would use it.

Query Store captures query texts, plans, and runtime statistics over time, enabling you to detect regressions and force stable plans. It is valuable in production where ad‑hoc changes cause plan drift. You would enable it on a busy OLTP database, monitor high‑cost queries, and use ALTER DATABASE … SET QUERY_STORE (OPERATION_MODE = READ_WRITE) to start collecting data. A strong answer also mentions the ability to set plan forcing, view regressions, and the overhead considerations.

What is a covering index and when should you create one?

A covering index includes all columns referenced by a query, allowing the optimizer to satisfy the query from the index alone without touching the base table. Create it when a frequent read‑heavy query selects a small set of columns but filters on others, reducing I/O. Discuss the trade‑off: extra storage and write overhead versus read performance gains. Mention INCLUDE clause to add non‑key columns without affecting index order.

CREATE INDEX IX_Orders_Covering ON Orders (CustomerID, OrderDate) INCLUDE (TotalAmount);

How does the MERGE statement work and what pitfalls should you avoid?

MERGE combines INSERT, UPDATE, and DELETE in a single atomic operation based on a source‑target match condition. It is useful for upserts. Pitfalls include hidden bugs when the source contains duplicate keys—SQL Server may raise an error or produce unpredictable results. Also, MERGE can cause performance issues if not indexed properly, and bugs in older versions required workarounds. A strong candidate mentions using WHEN NOT MATCHED BY TARGET THEN INSERT and WHEN MATCHED THEN UPDATE, and validates uniqueness before execution.

MERGE INTO Target t USING Source s ON (t.id = s.id) WHEN MATCHED THEN UPDATE SET ... WHEN NOT MATCHED THEN INSERT (...);

Explain how parameter sniffing can affect query performance and how to mitigate it.

Parameter sniffing occurs when the optimizer caches a plan based on the first parameter values it sees, which may be suboptimal for later calls with different values. This can cause slow performance for atypical parameters. Mitigation strategies include using OPTION (RECOMPILE) to force a fresh plan per execution, creating separate parameterized queries for distinct value ranges, or using OPTIMIZE FOR UNKNOWN to generate a generic plan. Interviewers expect you to discuss the trade‑off between compile cost and consistent performance.

What is a deadlock and how can you prevent it in SQL Server?

A deadlock occurs when two sessions hold locks that each needs to complete their transaction, causing a cycle. SQL Server detects deadlocks and kills one victim. Prevention includes acquiring locks in a consistent order, keeping transactions short, using appropriate isolation levels (e.g., READ COMMITTED SNAPSHOT), and adding indexes to reduce lock duration. A strong candidate also mentions using deadlock graphs for analysis and applying TRY…CATCH to handle victims gracefully.

What is the purpose of the WITH (NOLOCK) hint and why is it risky?

WITH (NOLOCK) tells SQL Server to perform a dirty read, ignoring shared locks and allowing reading uncommitted data. It can improve concurrency but risks returning inconsistent or phantom rows, leading to inaccurate reports. Interviewers expect you to discuss scenarios where it might be acceptable (e.g., reporting on a replica) and why you would prefer READ UNCOMMITTED isolation level or snapshot isolation instead of ad‑hoc hints.

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 a performance penalty because it cannot be inlined. An inline table‑valued function (TVF) returns a table defined by a single SELECT statement and can be merged into the outer query, allowing the optimizer to treat it like a view. Interviewers look for you to mention that inline TVFs are usually faster and that scalar UDFs may cause hidden scans.

What are the benefits and drawbacks of using stored procedures versus ad‑hoc SQL?

Stored procedures encapsulate logic, provide compile‑time validation, reduce network traffic, and enable permission granularity. They also allow plan reuse, which can improve performance. Drawbacks include tighter coupling to the database, harder version control, and potential for hidden business logic. Interviewers expect you to weigh maintainability against performance, and to mention that modern ORMs often favor parameterized ad‑hoc queries for flexibility.