Many interviewers focus on “gotcha” SQL questions, but the real goal should be assessing how candidates approach problems. For instance, asking “How would you alter a table to make an existing column an identity column in SQL Server?” can have multiple valid answers:
- You could recreate the table with an identity column and reinsert records using
IDENTITY_INSERT.
- You could add a new identity column, drop the old one, and rename it.
- Advanced users might use
ALTER TABLE ... SWITCH for a faster schema update.
Such questions don’t just check syntax knowledge, they reveal your depth of understanding and ability to think through SQL limitations.
This classic tests understanding of ranking and subqueries.
Candidates often write something like:
SELECT DISTINCT salary
FROM employees e1
WHERE (SELECT COUNT(DISTINCT salary)
FROM employees e2
WHERE e2.salary > e1.salary) = N - 1;
What it tests: deep knowledge of correlated subqueries, performance reasoning, and ranking logic.
Bonus points if the candidate mentions that ROW_NUMBER() or DENSE_RANK() is cleaner in modern SQL.
Tests both understanding of window functions and transaction safety.
A strong candidate might say:
DELETE FROM employees
WHERE id NOT IN (
SELECT MIN(id)
FROM employees
GROUP BY name, department
);
Or, in databases that support CTEs:
WITH cte AS (
SELECT id, ROW_NUMBER() OVER (PARTITION BY name, department ORDER BY id) AS rnum
FROM employees
)
DELETE FROM cte WHERE rnum > 1;
What it tests: ability to reason about deduplication, row identity, and side effects of DML operations.
This is less about syntax and more about query optimization and indexing strategy.
A well-prepared answer might include:
- Checking execution plans (EXPLAIN / SHOW PLAN)
- Adding composite indexes or covering indexes
- Reducing subqueries via joins
- Using partitioning for very large datasets
- Avoiding
SELECT * and ensuring predicates are sargable
What it tests: practical experience with performance tuning and understanding of SQL internals.