Prepping for these interviews is mostly about understanding how the engine actually works under pressure, not memorizing T-SQL syntax.
I've been on both sides of the table for over a decade now. The questions that actually separate candidates from the rest have nothing to do with basic CRUD operations. They revolve around execution plans, locking behavior, and the things that go wrong at 3 AM when a query suddenly starts timing out. Here is what I look for when someone sits across from me. Index design comes up early, but I don't care if they can define a clustered versus non-clustered index. I ask them to walk me through a situation where an index made performance worse. Most people have no real answer for that. I had a production database once where a highly selective index on a frequently updated column caused massive page splits across a 400GB table during off-peak maintenance windows. The fragmentation climbed to 89 percent within forty-eight hours and every read query on that table slowed by roughly six hundred milliseconds. The fix was switching to a fill factor of seventy and scheduling a weekly rebuild during the lowest traffic window, which dropped average query time back down to under two hundred milliseconds. Execution plans are where candidates either fold or shine. You need to understand what a key lookup means and why it destroys performance on large result sets. I ask people to explain how to identify a scan versus a seek on an actual plan XML. A table scan on a fifty-million-row fact table will absolutely wreck your response times. Bookmark lookups happen when your non-clustered index doesn't cover all the columns in your select statement, forcing SQL Server to hop back to the clustered index for each row. I tell people to stop guessing and just run SET STATISTICS IO ON and SET STATISTICS TIME ON before every serious query. The logical reads number tells you everything you need to know about whether your index strategy is working or if you are wasting cycles.
Transaction isolation levels come up constantly and people consistently get them wrong. Read Committed Snapshot Isolation is the default in most modern deployments and it eliminates most blocking problems by using row versioning instead of shared locks. But it is not a free lunch. Your tempdb grows because every modified row version gets stored there until the reader finishes. I've seen tempdb balloons to over two hundred gigabytes on databases with heavy write workloads under RCSI. Read Uncommitted lets you avoid blocking entirely but you are reading dirty data and might see phantom rows or miss rows that were rolled back mid-query. Serializable is the safest isolation level but it locks ranges of data and will grind a concurrent OLTP system to a halt within minutes under moderate load. Deadlocks are another area where experience shows. A deadlock graph in SQL Server gives you the exact victim and resource information you need, but only if you have extended events or trace flags set up before it happens. I once spent three days tracking down a deadlock that only occurred under specific timing conditions during a batch import process. The issue was two stored procedures accessing the same order header and order detail tables in opposite lock orders. One procedure locked headers first then details. The other locked details first then headers. When they ran concurrently, they deadlocked every single time. The solution was straightforward: reorder the access pattern so both procedures locked tables in the same sequence. Once we aligned the lock ordering, the deadlocks stopped completely. Query hints are a trap most developers walk right into. People love to slap WITH(NOLOCK) everywhere because it sounds like a quick performance fix. It is not. NOLOCK allows dirty reads, missing reads, and duplicate reads. You will get inconsistent results and sometimes corrupted output, especially under high concurrency. I had a reporting query using NOLOCK on a transaction table that returned two hundred thousand rows one run and one hundred eighty-three thousand on the next, even though no data changed between executions. The difference was purely due to page splits happening during the read. Use snapshot isolation instead if you want consistent non-blocking reads without the risks.
Statistics and query optimization go hand in hand. SQL Server relies on statistics to build execution plans, and stale statistics are the number one cause of sudden query performance regression. Auto-update statistics works most of the time, but it updates at a 20 percent threshold, which is too late for many workloads. I manually update statistics with UPDATE STATISTICS table_name WITH FULLSCAN after any major data load operation. Fullscan gives you the most accurate density and histogram data. It takes longer but it usually pays for itself within the first few query executions by producing better plans. Temporary tables versus table variables is a question that comes up repeatedly. The common advice you will hear everywhere is that table variables stay in memory and temp tables go to disk. That is an oversimplification that misses the point. Table variables have a fixed row count estimate of one, which means the optimizer treats them as tiny regardless of actual content. This causes terrible plan choices for anything over a few thousand rows. Temp tables give the optimizer real cardinality estimates and generate proper execution plans. I switched an entire ETL pipeline from table variables to temp tables and saw query times drop from an average of fourteen seconds to under three seconds because the optimizer was finally building plans based on actual data distribution instead of a hardcoded guess of one row. Parameter sniffing is another advanced topic that separates people who understand the engine from those who just write queries. SQL Server caches execution plans based on the parameters used in the first execution. If your first run uses parameters that produce a small result set, the cached plan will use an index seek. Subsequent calls with parameters that should return millions of rows will reuse that same seek plan and perform terribly. I solved this in a stored procedure that filtered orders by date range by adding OPTION(RECOMPILE) at the end. The procedure recompiles every time but uses the current parameter values to build an optimal plan. The tradeoff is compilation overhead on each execution, which adds roughly five to eight milliseconds per call, but the query itself runs three to five times faster. For a stored procedure that runs thousands of times per hour, the net gain is significant.
Get the Full Details
Window functions like RANK(), DENSE_RANK(), and ROW_NUMBER() are fair game and most junior developers struggle with the differences. RANK leaves gaps in numbering when ties exist. DENSE_RANK does not. ROW_NUMBER assigns a unique sequential number regardless of ties. I asked a candidate once to write a query that returned the top three salaries per department using window functions. They wrote a correlated subquery that ran in twelve seconds on a ten-million-row employee table. I showed them the window function version which ran in under half a second because it avoided the repeated scan. Query plan caching and compilation costs matter more than people realize. Every time a query compiles, SQL Server does CPU work to determine the best execution strategy. Ad-hoc queries with different parameter values fragment the plan cache and waste memory. Using parameterized queries or stored procedures keeps plan cache usage efficient. I ran a query that compared ad-hoc dynamic SQL against parameterized stored procedures on the same workload. The stored procedure version used roughly forty percent less CPU and had a plan cache hit rate above ninety-five percent compared to thirty-two percent for the dynamic approach. Missing index requests from the execution plan DMVs are useful but you should not implement every single one. SQL Server suggests missing indexes aggressively and many of them provide minimal benefit while adding write overhead. I once implemented twelve missing index suggestions on a busy OLTP table and saw insert performance degrade by over forty percent because each new non-clustered index had to be maintained on every write. I evaluated each suggestion against the actual query workload and only implemented four, which gave me the performance improvement I needed without the write penalty.
The interview will likely include a live coding segment where you write a query on the spot. Practice writing queries without IntelliSense. I remember being asked to write a recursive CTE that generated a company org chart on a whiteboard during an interview. The recursion limit tripped me up because I forgot to add OPTION(MAXRECURSION 0) and my CTE hit the default thirty-two level limit. A real org chart could easily exceed that depending on hierarchy depth. Performance tuning questions often include a scenario where a previously fast query suddenly becomes slow. The answer is almost always statistics, parameter sniffing, or a plan regression. Check sys.dm_exec_query_stats for recent plan changes. Look at sys.dm_exec_cached_plans to see if the plan was flushed from cache. Review sys.dm_db_stats_properties for staleness. These three DMVs cover the vast majority of sudden performance issues and knowing them by heart matters more than any textbook definition. There is no shortcut to understanding how SQL Server behaves under real load. Reading about query optimization gets you far enough to answer interview questions correctly. But actually debugging a production deadlock at midnight or watching a query plan degrade after a statistics update is what makes the knowledge stick. The candidates who do well are the ones who have been burned by these problems before and can describe exactly what happened and how they fixed it.
