| 1 | What are the four standard transaction isolation levels in SQL Server, and what does isolation level actually control? SQL Server › Isolation & Locking | SQL Server | Isolation & Locking | Beginner |
| 2 | What is the difference between an optimistic and a pessimistic approach to handling concurrency, and where does SQL Server's snapshot isolation fit into this? SQL Server › Isolation & Locking | SQL Server | Isolation & Locking | Intermediate |
| 3 | How would you diagnose and resolve a blocking problem where many queries are waiting on a single long running transaction? SQL Server › Isolation & Locking | SQL Server | Isolation & Locking | Advanced |
| 4 | How does Read Committed Snapshot Isolation differ from regular Read Committed isolation, and what tradeoff does enabling it introduce? SQL Server › Isolation & Locking | SQL Server | Isolation & Locking | Advanced |
| 5 | What is a lock in SQL Server, and why does the database engine use locks? SQL Server › Isolation & Locking | SQL Server | Isolation & Locking | Beginner |
| 6 | What is a deadlock, and how does SQL Server automatically handle one when it occurs? SQL Server › Isolation & Locking | SQL Server | Isolation & Locking | Intermediate |
| 7 | What are lock escalation and its potential impact on a busy system? SQL Server › Isolation & Locking | SQL Server | Isolation & Locking | Intermediate |
| 8 | What is the difference between a deadlock and normal blocking, and how do you tell them apart when troubleshooting a slow application? SQL Server › Deadlocks | SQL Server | Deadlocks | Intermediate |
| 9 | What is In-Memory OLTP in SQL Server, and what performance benefit does it provide? SQL Server › In-Memory OLTP (Memory-Optimized Tables) | SQL Server | In-Memory OLTP (Memory-Optimized Tables) | Beginner |
| 10 | What is a transaction in SQL Server, and why is grouping multiple statements into one important? SQL Server › Transactions & ACID | SQL Server | Transactions & ACID | Beginner |
| 11 | What does ACID stand for, and what does each letter represent in the context of database transactions? SQL Server › Transactions & ACID | SQL Server | Transactions & ACID | Beginner |
| 12 | What is a deadlock in SQL Server, and why does it happen? SQL Server › Deadlocks | SQL Server | Deadlocks | Beginner |
| 13 | How does SQL Server decide which transaction becomes the deadlock victim? SQL Server › Deadlocks | SQL Server | Deadlocks | Beginner |
| 14 | How would you perform a point in time restore to recover a database to the exact moment just before an accidental data deletion? SQL Server › Backup & Recovery | SQL Server | Backup & Recovery | Intermediate |
| 15 | What are the different cursor types available in SQL Server, such as static, dynamic, and forward only, and how do they differ? SQL Server › Cursors | SQL Server | Cursors | Intermediate |
| 16 | How do you combine a TRY CATCH block with a transaction to ensure the database is properly rolled back if an error occurs partway through? SQL Server › Error Handling with TRY CATCH | SQL Server | Error Handling with TRY CATCH | Intermediate |
| 17 | What types of errors cannot be caught by a TRY CATCH block in SQL Server? SQL Server › Error Handling with TRY CATCH | SQL Server | Error Handling with TRY CATCH | Intermediate |
| 18 | How does the concurrency model of In-Memory OLTP differ from traditional disk based tables, and why does this improve performance? SQL Server › In-Memory OLTP (Memory-Optimized Tables) | SQL Server | In-Memory OLTP (Memory-Optimized Tables) | Intermediate |
| 19 | What are some known issues or caveats with the MERGE statement that developers should be aware of before relying on it heavily? SQL Server › Merge Statement (Upsert) | SQL Server | Merge Statement (Upsert) | Intermediate |
| 20 | How do you create an Extended Events session to capture specific long running queries on a production server? SQL Server › SQL Server Profiler & Extended Events | SQL Server | SQL Server Profiler & Extended Events | Intermediate |
| 21 | What is the difference between a local temporary table and a global temporary table? SQL Server › Temporary Tables & Table Variables | SQL Server | Temporary Tables & Table Variables | Intermediate |
| 22 | How do you use a SAVE TRANSACTION savepoint within a larger transaction to allow partial rollback of just a portion of the work? SQL Server › Transactions & ACID | SQL Server | Transactions & ACID | Intermediate |
| 23 | What is the difference between an implicit and an explicit transaction in SQL Server? SQL Server › Transactions & ACID | SQL Server | Transactions & ACID | Intermediate |
| 24 | How do you check whether a transaction is currently active and view its properties within a session? SQL Server › Transactions & ACID | SQL Server | Transactions & ACID | Intermediate |
| 25 | How would you decide whether a specific table in your application is a good candidate for conversion to a memory optimized table? SQL Server › In-Memory OLTP (Memory-Optimized Tables) | SQL Server | In-Memory OLTP (Memory-Optimized Tables) | Advanced |
| 26 | How would you design an Extended Events session to monitor for a very specific, hard to reproduce issue, such as intermittent deadlocks, in a production environment? SQL Server › SQL Server Profiler & Extended Events | SQL Server | SQL Server Profiler & Extended Events | Advanced |
| 27 | How would you design a distributed transaction that needs to span operations across two different database servers, and what additional complexity does this introduce? SQL Server › Transactions & ACID | SQL Server | Transactions & ACID | Advanced |
| 28 | How do long running transactions negatively impact overall system performance, and what strategies help avoid this problem? SQL Server › Transactions & ACID | SQL Server | Transactions & ACID | Advanced |
| 29 | What design changes can you make to application code and database access patterns to reduce the frequency of deadlocks? SQL Server › Deadlocks | SQL Server | Deadlocks | Advanced |