SQL Server Interview Questions – A Complete Guide for Developers

SQL Server is a core skill for any .NET full-stack developer. Database questions test your ability to design efficient schemas, write optimized queries, and understand how SQL Server manages data internally. This guide covers the most important SQL interview questions you need to know.

Why SQL Questions Matter in Developer Interviews

Almost every application interacts with a database. Interviewers want to know you can write efficient queries, design normalized schemas, understand indexing strategies, and troubleshoot performance issues.

1. What Is the Difference Between WHERE and HAVING?

WHERE filters rows before grouping (applied to individual rows). HAVING filters groups after the GROUP BY operation (applied to aggregated results).

-- WHERE filters before grouping
SELECT Department, COUNT(*) as EmpCount
FROM Employees
WHERE Salary > 50000
GROUP BY Department;

-- HAVING filters after grouping
SELECT Department, COUNT(*) as EmpCount
FROM Employees
GROUP BY Department
HAVING COUNT(*) > 5;

Interview Tip: You cannot use aggregate functions in WHERE. Use HAVING for conditions on aggregated columns.

2. What Are Indexes and Why Are They Important?

An index is a data structure that improves the speed of data retrieval. It works like a book index, allowing SQL Server to find data without scanning every row.

Clustered Index determines the physical order of data in the table. A table can have only one clustered index (usually the primary key).

Non-Clustered Index creates a separate structure with pointers to the actual data. A table can have multiple non-clustered indexes.

Interview Tip: Too many indexes slow down INSERT, UPDATE, and DELETE operations. Always balance read performance with write performance.

3. What Is Normalization? Explain the Normal Forms.

Normalization is the process of organizing data to reduce redundancy and improve data integrity.

• 1NF — Each column contains atomic values; no repeating groups
• 2NF — Meets 1NF and all non-key columns depend on the entire primary key
• 3NF — Meets 2NF and no transitive dependencies (non-key columns depend only on the primary key)
• BCNF — Every determinant is a candidate key

In practice, most applications target 3NF. Denormalization may be used strategically for read-heavy reporting scenarios.

4. What Are Joins? Explain the Different Types.

Joins combine rows from two or more tables based on a related column.

• INNER JOIN — Returns only matching rows from both tables
• LEFT JOIN — Returns all rows from the left table plus matching rows from the right
• RIGHT JOIN — Returns all rows from the right table plus matching rows from the left
• FULL OUTER JOIN — Returns all rows from both tables, with NULLs where there is no match
• CROSS JOIN — Returns the Cartesian product of both tables

SELECT e.Name, d.DepartmentName
FROM Employees e
INNER JOIN Departments d ON e.DeptId = d.Id;

5. What Is a Stored Procedure vs a Function?

A stored procedure can perform actions (INSERT, UPDATE, DELETE), return multiple result sets, use output parameters, and cannot be used in a SELECT statement directly.

A function must return a value, can be used in SELECT statements, cannot modify data (in most cases), and can be scalar-valued or table-valued.

6. What Is a CTE (Common Table Expression)?

A CTE is a temporary named result set that exists only for the duration of a single query. It improves readability and is especially useful for recursive queries.

WITH EmployeeHierarchy AS (
    SELECT Id, Name, ManagerId, 1 as Level
    FROM Employees WHERE ManagerId IS NULL
    UNION ALL
    SELECT e.Id, e.Name, e.ManagerId, eh.Level + 1
    FROM Employees e
    INNER JOIN EmployeeHierarchy eh ON e.ManagerId = eh.Id
)
SELECT * FROM EmployeeHierarchy;

7. What Is the Difference Between DELETE, TRUNCATE, and DROP?

• DELETE — Removes specific rows, can use WHERE, logged operation, can be rolled back, fires triggers
• TRUNCATE — Removes all rows, cannot use WHERE, minimally logged, faster, resets identity
• DROP — Removes the entire table structure and data permanently

8. What Are Window Functions in SQL?

Window functions perform calculations across a set of rows related to the current row without collapsing them into a single output row (unlike GROUP BY).

SELECT Name, Department, Salary,
    ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) as Rank,
    AVG(Salary) OVER (PARTITION BY Department) as DeptAvgSalary
FROM Employees;

Common window functions include ROW_NUMBER(), RANK(), DENSE_RANK(), LEAD(), LAG(), SUM(), and AVG().

9. What Is a Deadlock and How Do You Prevent It?

A deadlock occurs when two or more transactions hold locks that the other needs, creating a circular wait. SQL Server detects deadlocks and terminates one transaction as the victim.

Prevention strategies include accessing tables in the same order, keeping transactions short, using appropriate isolation levels, and avoiding user interaction during transactions.

10. What Are Execution Plans and How Do You Optimize Queries?

An execution plan shows how SQL Server will execute a query. It reveals table scans, index usage, joins, and estimated costs. To optimize queries: avoid SELECT *, use proper indexes, avoid functions on indexed columns in WHERE clauses, use EXISTS instead of IN for subqueries, and keep statistics updated.

11. Normalization and Normal Forms

Q: What is Normalization and what are the Normal Forms?

Normalization organizes tables to reduce redundancy. 1NF removes repeating groups (atomic values). 2NF removes partial dependency. 3NF removes transitive dependency.

12. UNION vs UNION ALL

Q: What is the difference between UNION and UNION ALL?

UNION combines result sets removing duplicates. UNION ALL keeps all duplicates and is faster.

13. Clustered vs Non-Clustered Index

Q: What is the difference between Clustered and Non-Clustered Index?

Clustered Index physically sorts actual data rows (one per table). Non-Clustered Index creates a separate structure with pointers (multiple allowed).

14. Ranking Functions

Q: What are Ranking Functions in SQL Server?

ROW_NUMBER() assigns unique sequential numbers, RANK() assigns rank with gaps for ties, DENSE_RANK() assigns rank without gaps.

SELECT Name, Salary,
    ROW_NUMBER() OVER (ORDER BY Salary DESC) as RowNum,
    RANK() OVER (ORDER BY Salary DESC) as Rnk,
    DENSE_RANK() OVER (ORDER BY Salary DESC) as DenseRnk
FROM Employees;

15. WHERE vs HAVING

Q: What is the difference between WHERE and HAVING?

WHERE filters rows before grouping. HAVING filters grouped data after GROUP BY and can use aggregate functions.

16. Execution Plan

Q: What is an Execution Plan?

Shows how SQL Server processes a query — operations order, index usage, join types, and costs. Identifies bottlenecks like table scans or missing indexes.

17. Deadlock

Q: What is a Deadlock?

Two or more transactions block each other indefinitely. SQL Server detects and terminates one (the victim) to resolve it.

18. Transaction Isolation Levels

Q: What are Transaction Isolation Levels?

Five levels: Read Uncommitted (dirty reads allowed), Read Committed (default), Repeatable Read, Serializable (highest), and Snapshot (row versioning).

19. CTE vs Temp Table

Q: What is the difference between CTE and Temp Table?

CTE is a temporary named result set within a single query. Temp Table persists in tempdb for the session. Use CTE for readability; Temp Tables for reusing results or adding indexes.