Skip to content

MS SQL Server Interview Questions

Q: What is the difference between WHERE and HAVING?

  • WHERE filters rows before grouping
  • HAVING filters groups after GROUP BY
-- WHERE filters individual rows
SELECT department, COUNT(*) AS emp_count
FROM employees
WHERE salary > 50000
GROUP BY department
HAVING COUNT(*) > 5;
-- HAVING filters the grouped results

Q: What is the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN?

-- INNER JOIN โ€” only matching rows from both tables
SELECT e.name, d.name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;
-- LEFT JOIN โ€” all rows from left, matching from right (NULL if no match)
SELECT e.name, d.name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;
-- FULL OUTER JOIN โ€” all rows from both, NULL where no match
SELECT e.name, d.name
FROM employees e
FULL OUTER JOIN departments d ON e.dept_id = d.id;

Q: What is the difference between DELETE, TRUNCATE, and DROP?

DELETE TRUNCATE DROP
Removes Rows (with filter) All rows Entire table
WHERE clause Yes No No
Rollback Yes (logged) Yes (minimal log) No
Triggers Fires Does not fire N/A
Resets identity No Yes N/A

Q: What are window functions?

-- ROW_NUMBER โ€” unique sequential number per partition
SELECT name, salary, department,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;
-- RANK โ€” same rank for ties, gaps after ties
-- DENSE_RANK โ€” same rank for ties, no gaps
-- Running total
SELECT name, salary,
SUM(salary) OVER (ORDER BY hire_date ROWS UNBOUNDED PRECEDING) AS running_total
FROM employees;
-- LAG/LEAD โ€” access previous/next row
SELECT name, salary,
LAG(salary, 1) OVER (ORDER BY hire_date) AS prev_salary
FROM employees;

Q: What is a CTE (Common Table Expression)?

-- Basic CTE
WITH HighEarners AS (
SELECT name, salary, department
FROM employees
WHERE salary > 80000
)
SELECT department, COUNT(*) AS count
FROM HighEarners
GROUP BY department;
-- Recursive CTE โ€” org hierarchy
WITH OrgChart AS (
SELECT id, name, manager_id, 0 AS level
FROM employees
WHERE manager_id IS NULL -- root
UNION ALL
SELECT e.id, e.name, e.manager_id, oc.level + 1
FROM employees e
INNER JOIN OrgChart oc ON e.manager_id = oc.id
)
SELECT * FROM OrgChart;

Q: What is the difference between clustered and non-clustered indexes?

Clustered Non-Clustered
Physical order Sorts table data Separate structure
Per table 1 only Up to 999
Leaf nodes Actual data rows Row pointers
Speed Fast for range scans Fast for lookups
-- Clustered index (usually on primary key)
CREATE CLUSTERED INDEX IX_Employees_Id ON employees(id);
-- Non-clustered index
CREATE NONCLUSTERED INDEX IX_Employees_Name ON employees(last_name, first_name);
-- Covering index โ€” includes extra columns to avoid key lookup
CREATE NONCLUSTERED INDEX IX_Orders_Customer
ON orders(customer_id)
INCLUDE (order_date, total_amount);

Q: What is index fragmentation and how do you fix it?

-- Check fragmentation
SELECT index_id, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('orders'), NULL, NULL, 'SAMPLED');
-- < 10% โ€” no action needed
-- 10-30% โ€” REORGANIZE (online, less resource)
ALTER INDEX IX_Orders_Customer ON orders REORGANIZE;
-- > 30% โ€” REBUILD (more thorough, can be offline)
ALTER INDEX IX_Orders_Customer ON orders REBUILD;
ALTER INDEX ALL ON orders REBUILD; -- rebuild all indexes on table

Q: What are transaction isolation levels?

Level Dirty Read Non-Repeatable Read Phantom Read
READ UNCOMMITTED Yes Yes Yes
READ COMMITTED (default) No Yes Yes
REPEATABLE READ No No Yes
SERIALIZABLE No No No
SNAPSHOT No No No
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- or ROLLBACK;

Q: What is a deadlock and how do you prevent it?

A deadlock occurs when two transactions each hold a lock the other needs.

Prevention strategies:

  • Access tables in the same order across transactions
  • Keep transactions short
  • Use READ COMMITTED SNAPSHOT isolation
  • Add appropriate indexes to reduce lock duration
-- Enable snapshot isolation to reduce blocking
ALTER DATABASE MyDB SET READ_COMMITTED_SNAPSHOT ON;

Q: What is the difference between a stored procedure and a function?

Stored Procedure Function
Return value Optional (output params) Must return a value
Use in SELECT No Yes (scalar/table-valued)
DML statements Yes Only in table-valued
Transaction control Yes No
Error handling TRY/CATCH Limited
-- Stored procedure
CREATE PROCEDURE GetUserById @UserId INT
AS
BEGIN
SELECT * FROM users WHERE id = @UserId;
END;
-- Scalar function
CREATE FUNCTION dbo.GetFullName(@First NVARCHAR(50), @Last NVARCHAR(50))
RETURNS NVARCHAR(100)
AS
BEGIN
RETURN @First + ' ' + @Last;
END;
-- Table-valued function
CREATE FUNCTION dbo.GetUsersByDept(@DeptId INT)
RETURNS TABLE
AS
RETURN (SELECT * FROM users WHERE dept_id = @DeptId);

Q: What is the difference between EXISTS and IN?

-- IN โ€” evaluates subquery fully, then checks membership
SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE country = 'US');
-- EXISTS โ€” stops at first match, more efficient for large subqueries
SELECT * FROM orders o WHERE EXISTS (
SELECT 1 FROM customers c WHERE c.id = o.customer_id AND c.country = 'US'
);

EXISTS is generally faster when the subquery returns many rows.


Q: What is query execution plan and how do you read it?

-- View estimated plan
SET SHOWPLAN_XML ON;
SELECT * FROM orders WHERE customer_id = 1;
-- View actual plan
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT * FROM orders WHERE customer_id = 1;

Key operators to watch:

  • Table Scan โ€” no index, reads entire table (bad for large tables)
  • Index Seek โ€” efficient, uses index to find rows
  • Index Scan โ€” reads entire index
  • Key Lookup โ€” extra lookup for non-covered columns (consider covering index)
  • Hash Match / Nested Loops / Merge Join โ€” join algorithms

Q: What are computed columns and when are they useful?

CREATE TABLE orders (
id INT PRIMARY KEY,
quantity INT,
unit_price DECIMAL(10,2),
total AS (quantity * unit_price) PERSISTED -- stored on disk
);
-- Can be indexed
CREATE INDEX IX_Orders_Total ON orders(total);