MS SQL Server Interview Questions

MS SQL Server Interview Questions

SQL Fundamentals

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;

---

Indexes

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

---

Transactions & Concurrency

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;

---

Stored Procedures & Functions

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);

---

Performance

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);