SQL Server Interview Questions and Answers (T-SQL)

Twenty-five questions that SQL Server and T-SQL interviews actually ask, from beginner basics to indexes, transactions and performance. Each answer is short enough to say out loud, with a T-SQL example where it helps.

By Matthew Rathbone · October 28, 2026

SQL Server interviews mix general SQL (joins, aggregates, subqueries) with Microsoft-specific topics: T-SQL syntax, clustered indexes, stored procedures, isolation levels and execution plans. Interviewers usually start with definitions, then ask you to write a query or explain why one is slow.

This page covers 25 questions in that order. For the database-neutral list (keys, normalization, DELETE vs TRUNCATE), see our 30 SQL interview questions and answers. Applying for an analyst role? Read SQL interview questions for data analysts too.

Jump to: basics · T-SQL queries · indexes and performance · transactions · programmability · interview tips · FAQ

Examples use a small Employees table (EmployeeID, Name, DepartmentID, Salary) and a Departments table (DepartmentID, DepartmentName). You can run T-SQL against a free SQL Server Express or Developer edition, or any client that connects to SQL Server such as Beekeeper Studio.

SQL Server basics

1. What is SQL Server, and what is T-SQL?

SQL Server is Microsoft's relational database management system (RDBMS). T-SQL (Transact-SQL) is its dialect of SQL. It adds procedural features on top of standard SQL: variables, IF...ELSE, loops, TRY...CATCH error handling and stored procedures. If you are new to the language itself, start with what is SQL.

2. What is the difference between SQL and T-SQL?

SQL is the standard query language shared by most databases. T-SQL is Microsoft's extension of it, used by SQL Server and Azure SQL. Standard queries like SELECT ... FROM ... WHERE work in both. T-SQL-only features include TOP, ISNULL, GETDATE(), @@ROWCOUNT and table variables.

3. What are the main editions of SQL Server?

Express (free, with size and resource limits), Developer (free, full features, for non-production use), Standard and Enterprise (paid, production). Azure SQL Database is the managed cloud version. Knowing that Developer is free and feature-complete is useful when you are asked how you would practice.

4. What is the difference between a primary key and a unique key?

Both enforce uniqueness. A table has only one primary key, and it cannot contain NULL. A table can have many unique constraints, and each one allows a single NULL in SQL Server. By default a primary key creates a clustered index and a unique constraint creates a nonclustered one.

5. What is the difference between `DELETE`, `TRUNCATE` and `DROP`?

DELETE removes rows one at a time, accepts a WHERE clause, fires triggers and is fully logged.
TRUNCATE TABLE removes all rows quickly with minimal logging, cannot take a WHERE, resets identity values and cannot run on a table referenced by a foreign key.
DROP TABLE removes the table itself, including its data, indexes and permissions.

In SQL Server, TRUNCATE can be rolled back inside a transaction. A common wrong answer is that it cannot. See how to delete a row in SQL for the safe way to use DELETE.

6. What is the difference between `WHERE` and `HAVING`?

WHERE filters rows before grouping. HAVING filters groups after aggregation. You cannot use an aggregate like COUNT(*) in WHERE.

SELECT DepartmentID, COUNT(*) AS Headcount
FROM Employees
WHERE Salary > 40000
GROUP BY DepartmentID
HAVING COUNT(*) > 5;

7. What are the types of joins in SQL Server?

INNER JOIN returns matching rows only. LEFT and RIGHT OUTER JOIN keep every row from one side. FULL OUTER JOIN keeps every row from both. CROSS JOIN returns every combination. A self join joins a table to itself, for example employees to their managers. Practice them in our interactive joins, LEFT JOIN and self join lessons.

8. How does SQL Server treat `NULL`?

NULL means unknown, so NULL = NULL is not true. Use IS NULL and IS NOT NULL. Aggregates other than COUNT(*) ignore nulls. Replace nulls with ISNULL(col, 0) (T-SQL only, two arguments) or COALESCE(col1, col2, 0) (standard, any number of arguments). Practice with the NULL and COALESCE lessons.

T-SQL queries interviewers ask for

9. How do you return only the first N rows in SQL Server?

SQL Server uses TOP, not LIMIT. Newer code often uses OFFSET ... FETCH, which also supports paging and requires an ORDER BY.

-- top 5 highest paid
SELECT TOP (5) Name, Salary
FROM Employees
ORDER BY Salary DESC;

-- page 3 with 10 rows per page
SELECT Name, Salary
FROM Employees
ORDER BY Salary DESC
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

More detail in how to limit results in T-SQL.

10. How do you find the second highest salary?

The classic question. Three common answers:

-- 1. OFFSET / FETCH on distinct salaries
SELECT DISTINCT Salary
FROM Employees
ORDER BY Salary DESC
OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY;

-- 2. Subquery
SELECT MAX(Salary) AS SecondHighest
FROM Employees
WHERE Salary < (SELECT MAX(Salary) FROM Employees);

-- 3. DENSE_RANK (handles ties and works for any Nth)
SELECT Salary
FROM (
  SELECT Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS rnk
  FROM Employees
) ranked
WHERE rnk = 2
GROUP BY Salary;

Mention ties: ROW_NUMBER would give the wrong answer if two people share the top salary, which is why DENSE_RANK is the safest.

11. What is a CTE and when would you use one?

A common table expression is a named temporary result set defined with WITH and valid for one statement. It makes complex queries readable and is the only way to write recursive queries without a loop.

WITH DeptAvg AS (
  SELECT DepartmentID, AVG(Salary) AS AvgSalary
  FROM Employees
  GROUP BY DepartmentID
)
SELECT e.Name, e.Salary, d.AvgSalary
FROM Employees e
JOIN DeptAvg d ON d.DepartmentID = e.DepartmentID
WHERE e.Salary > d.AvgSalary;

A CTE is not materialized or cached by default in SQL Server. If you reference it twice, it can run twice.

12. What are window functions?

Window functions calculate across a set of related rows without collapsing them like GROUP BY. Use ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD() and aggregates with OVER (PARTITION BY ... ORDER BY ...).

SELECT Name, DepartmentID, Salary,
       RANK() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS DeptRank
FROM Employees;

See how to use LAG and LEAD for more.

13. What is the difference between `ROW_NUMBER`, `RANK` and `DENSE_RANK`?

For salaries 100, 100, 90: ROW_NUMBER gives 1, 2, 3 (always unique). RANK gives 1, 1, 3 (ties share a rank and leave a gap). DENSE_RANK gives 1, 1, 2 (ties share a rank, no gap).

14. How do you find duplicate rows?

SELECT Name, DepartmentID, COUNT(*) AS Copies
FROM Employees
GROUP BY Name, DepartmentID
HAVING COUNT(*) > 1;

To delete duplicates while keeping one, number the rows in a CTE and delete the extras. SQL Server lets you delete through a CTE:

WITH Numbered AS (
  SELECT ROW_NUMBER() OVER (PARTITION BY Name, DepartmentID ORDER BY EmployeeID) AS rn
  FROM Employees
)
DELETE FROM Numbered WHERE rn > 1;

15. What is the difference between `UNION` and `UNION ALL`?

UNION combines results and removes duplicates, which costs a sort or hash step. UNION ALL keeps everything and is faster. Use UNION ALL unless you specifically need de-duplication.

16. What is the difference between a subquery and a join, and which is faster?

Usually the optimizer produces the same plan, so correctness and readability decide. Use EXISTS for "does a related row exist" checks (it stops at the first match). Beware NOT IN with a subquery that can return NULL: it returns no rows at all. Prefer NOT EXISTS or a LEFT JOIN ... WHERE right.id IS NULL.

Indexes and performance

17. What is the difference between a clustered and a nonclustered index?

A clustered index defines the physical order of the table's rows, so there can be only one per table (the table's data is the index). A nonclustered index is a separate structure with key values and pointers to the rows, and a table can have many. An index on a table without a clustered index points to a heap row identifier; with a clustered index it points to the clustering key. Learn the basics in how to create an index in SQL.

CREATE CLUSTERED INDEX IX_Employees_EmployeeID ON Employees (EmployeeID);
CREATE NONCLUSTERED INDEX IX_Employees_Dept ON Employees (DepartmentID) INCLUDE (Salary);

18. What is a covering index?

A nonclustered index that contains every column a query needs, either as key columns or in the INCLUDE list, so SQL Server never has to look up the base table (a key lookup). The index above covers SELECT Salary FROM Employees WHERE DepartmentID = 3.

19. Why is a query slow, and how do you investigate it?

Start with the actual execution plan (in SSMS, Include Actual Execution Plan). Look for table or index scans where you expect seeks, key lookups, big gaps between estimated and actual row counts, and expensive sorts. Common causes:

Missing or unused indexes, or functions on indexed columns (WHERE YEAR(OrderDate) = 2025 prevents an index seek; use a date range instead).
Implicit conversions from mismatched data types.
Parameter sniffing, where a cached plan suits one parameter value but not others.
SELECT * pulling more columns than needed.

Our guide on how to improve SQL query performance covers general techniques.

20. What is the difference between an index seek and an index scan?

A seek navigates the B-tree straight to the matching rows, which is efficient for selective filters. A scan reads the whole index or table. Scans are not always bad: on small tables, or when you need most rows, a scan is the right plan.

Transactions and concurrency

21. What are ACID properties?

Atomicity (all or nothing), Consistency (data stays valid under the rules), Isolation (concurrent transactions do not see each other's partial work) and Durability (committed data survives a crash, via the transaction log).

BEGIN TRY
  BEGIN TRANSACTION;
  UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1;
  UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 2;
  COMMIT TRANSACTION;
END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
  THROW;
END CATCH;

22. What are the transaction isolation levels in SQL Server?

From least to most strict: READ UNCOMMITTED (allows dirty reads, same as NOLOCK), READ COMMITTED (the default), REPEATABLE READ, SERIALIZABLE, and SNAPSHOT (row versioning, readers do not block writers). Azure SQL Database uses READ COMMITTED SNAPSHOT by default. A strong answer names the anomalies each level allows: dirty reads, non-repeatable reads and phantom reads.

23. What is a deadlock and how do you handle it?

Two sessions each hold a lock the other needs, so neither can proceed. SQL Server detects it, picks a victim (error 1205) and rolls that transaction back. Prevent deadlocks by accessing tables in the same order, keeping transactions short, using appropriate indexes and retrying the victim transaction in application code.

Stored procedures, views and triggers

24. What is the difference between a stored procedure and a function?

A stored procedure can modify data, return multiple result sets, use output parameters and manage transactions, and is called with EXEC. A user-defined function must return a value (scalar or table), generally cannot change database state, and can be used inside a SELECT. Scalar functions in older versions can hurt performance on large tables, so mention that you check the plan.

CREATE PROCEDURE dbo.GetEmployeesByDept @DepartmentID INT
AS
BEGIN
  SET NOCOUNT ON;
  SELECT EmployeeID, Name, Salary
  FROM Employees
  WHERE DepartmentID = @DepartmentID;
END;

EXEC dbo.GetEmployeesByDept @DepartmentID = 3;

25. What is a view, and what is an indexed view?

A view is a saved SELECT that behaves like a virtual table and stores no data. It simplifies queries and restricts access to columns. An indexed view (a unique clustered index created on the view with SCHEMABINDING) materializes the result on disk, which speeds up repeated aggregations but slows writes to the underlying tables. See how to create views in SQL.

How to prepare for a SQL Server interview

Know your T-SQL differences. TOP instead of LIMIT, ISNULL, GETDATE(), DATEDIFF, + or CONCAT for strings. See current date in T-SQL and date differences in T-SQL.
Practice writing queries by hand. Many interviews use a shared editor with no autocomplete. Rehearse joins, GROUP BY, CTEs and ranking with SQL practice problems.
Talk through assumptions. Ask about duplicates, nulls and ties before you write the query.
Know one performance story. Be ready to describe a slow query you fixed, what the plan showed and what you changed.
Install a local instance. Developer edition is free. Having broken and fixed something yourself beats memorized answers. Here is how to connect.

Where to go next

General SQL concepts: 30 SQL interview questions and answers.
Using PostgreSQL instead? See PostgreSQL interview questions and answers.
Role-specific prep: SQL interview questions for data analysts.
Keep the SQL cheat sheet open while you study, and see more worked queries in 38 SQL query examples.
Learn the fundamentals interactively: GROUP BY, joins and ORDER BY.

FAQ

What are the most common SQL Server interview questions?

Clustered vs nonclustered indexes, DELETE vs TRUNCATE, joins, the second highest salary, CTEs, window functions, stored procedures vs functions, isolation levels and how to troubleshoot a slow query.

Is T-SQL the same as SQL?

No. T-SQL is Microsoft's extension of SQL. Core query syntax is shared, but SQL Server adds TOP, TRY...CATCH, variables and its own functions.

How should I prepare for a SQL Server interview as a beginner?

Get comfortable with SELECT, joins, GROUP BY and subqueries first, then learn indexes, transactions and stored procedures. Our free interactive lessons cover the fundamentals in your browser.

Do I need to know SSMS?

Usually yes for DBA and developer roles. You should be able to open a query window, read an execution plan and browse objects in SQL Server Management Studio. For analyst roles any SQL client is fine.

How long does it take to prepare?

With basic SQL knowledge, one to two weeks of daily practice covers this list. Starting from zero, plan for a month. See is SQL easy to learn?

SQL Server Interview Questions and Answers