Preparing for a SQL Server Interview
SQL Server interviews at most companies probe three layers at once: whether you can write correct T-SQL under a slightly ambiguous requirement, whether you understand why a query performs the way it does (indexing, execution plans, isolation levels), and whether you've operated a database in production long enough to have opinions about transactions, error handling, and schema design trade-offs. Strong candidates don't just recite definitions -- they explain the trade-off behind a choice, like why a clustered index on an ever-incrementing IDENTITY column avoids page splits that a clustered index on a GUID would cause.
Cricket analogy: A strong SQL Server interview answer is like a batter explaining not just that they left a wide delivery outside off stump, but why -- reading the field placement and the bowler's plan, the same depth expected when explaining an indexing choice.
Core Concept Questions
Clustered vs Non-Clustered Indexes
A near-universal question is the difference between a clustered and a non-clustered index. A clustered index determines the physical order in which table rows are stored on disk -- a table can have at most one, and if none is explicitly defined, SQL Server stores the table as an unordered heap. A non-clustered index is a separate structure containing a copy of the indexed columns plus a pointer back to the full row (the clustering key if a clustered index exists, or a row locator if the table is a heap); a table can have many non-clustered indexes. The strongest follow-up answer explains why choosing a narrow, ever-increasing clustering key (like an IDENTITY column) avoids the page splits and fragmentation that a wide or random clustering key (like a GUID generated by NEWID()) would cause, since new rows would need to be inserted into the middle of existing pages rather than appended at the end.
Cricket analogy: A clustered index is like a scorecard physically sorted by batting order -- there's only one true batting order per innings -- while a non-clustered index is like a separate quick-reference sheet sorted by runs scored that still points back to each player's row in the batting order.
-- IDENTITY clustering key: sequential inserts append at the end, minimal fragmentation
CREATE TABLE dbo.Orders (
OrderId INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
CustomerId INT NOT NULL,
OrderDate DATE NOT NULL
);
-- Non-clustered index supporting a common lookup pattern
CREATE NONCLUSTERED INDEX IX_Orders_CustomerId_OrderDate
ON dbo.Orders (CustomerId, OrderDate)
INCLUDE (OrderId);
-- GUID clustering key: random inserts cause page splits and fragmentation
-- CREATE TABLE dbo.OrdersGuid (
-- OrderId UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY CLUSTERED, -- avoid this pattern
-- ...
-- );Isolation Levels, Deadlocks, and Query Debugging
Interviewers commonly ask candidates to explain isolation levels beyond a textbook definition: READ UNCOMMITTED allows dirty reads by ignoring locks entirely, READ COMMITTED (the default) prevents dirty reads but allows non-repeatable reads and phantom reads, REPEATABLE READ prevents non-repeatable reads by holding shared locks until the transaction ends, and SERIALIZABLE prevents phantom reads too but at the cost of the most blocking. A related favorite is 'how would you debug a deadlock?' -- a strong answer mentions enabling trace flag 1222 or using Extended Events to capture the deadlock graph, identifying which two sessions held incompatible locks in reverse order of each other, and resolving it by ensuring all transactions access objects in a consistent order or by reducing transaction scope. Candidates should also be ready to explain execution plans: recognizing that a Key Lookup operator next to an Index Seek often signals a missing covering index, and that a large gap between estimated and actual row counts usually points to stale statistics.
Cricket analogy: READ UNCOMMITTED is like reporting a batter's score from a shaky, unconfirmed radio commentary before the official scorer updates the board -- fast, but occasionally wrong, unlike SERIALIZABLE's fully confirmed and locked-in final scorecard.
A common trick question: 'What's the default isolation level in SQL Server?' The answer is READ COMMITTED, not READ UNCOMMITTED -- SQL Server never allows dirty reads by default, unlike the common misconception. RCSI can be enabled to make READ COMMITTED use row versioning instead of locking, without changing the isolation level name itself.
When asked to write a query live, narrate your assumptions out loud (grain, NULL handling, whether duplicates matter) before typing. Interviewers weigh how you clarify ambiguity as heavily as whether the final query is syntactically perfect.
- A table can have at most one clustered index, which defines physical row order, and many non-clustered indexes.
- An ever-increasing clustering key (IDENTITY) minimizes page splits; a random key (GUID via NEWID()) maximizes them.
- READ COMMITTED is SQL Server's default isolation level, not READ UNCOMMITTED.
- Isolation levels trade off consistency guarantees against blocking: READ UNCOMMITTED allows dirty reads, SERIALIZABLE blocks the most.
- Deadlock debugging relies on the deadlock graph (trace flag 1222 or Extended Events) to see the lock cycle.
- A Key Lookup next to an Index Seek in an execution plan usually signals a missing covering index.
- Narrate assumptions about grain and NULL handling out loud during a live coding interview.
Practice what you learned
1. How many clustered indexes can a single table have?
2. Why is an ever-increasing IDENTITY column generally a better clustering key than a random GUID?
3. What is SQL Server's default transaction isolation level?
4. What does a Key Lookup operator appearing next to an Index Seek in an execution plan usually indicate?
5. What tool or mechanism is commonly used to capture a deadlock graph for debugging?
Was this page helpful?
You May Also Like
T-SQL Best Practices
Practical, production-tested guidelines for writing T-SQL that is correct, performant, and maintainable in SQL Server.
SQL Server vs Other Databases
How SQL Server compares to PostgreSQL, MySQL, and Oracle across architecture, tooling, licensing, and everyday development experience.
T-SQL Quick Reference
A fast-lookup reference covering core T-SQL syntax: data types, joins, window functions, string/date functions, and common statements.
Related Reading
Related Study Notes in Programming
Browse all study notesApache Spark Study Notes
Programming · 30 topics
ProgrammingApache Flink Study Notes
Programming · 30 topics
ProgrammingHadoop Study Notes
Programming · 30 topics
ProgrammingSnowflake Study Notes
Programming · 30 topics
ProgrammingApache Airflow Study Notes
Programming · 30 topics
Programmingdbt (Data Build Tool) Study Notes
Programming · 30 topics