| 1 | What is an execution plan in SQL Server, and how does it help you understand query performance? SQL Server › Query Optimization & Plans | SQL Server | Query Optimization & Plans | Beginner |
| 2 | How do outdated or missing statistics affect the query optimizer's ability to choose an efficient execution plan? SQL Server › Query Optimization & Plans | SQL Server | Query Optimization & Plans | Intermediate |
| 3 | What is parameter sniffing, and how can it sometimes lead to unexpectedly poor query performance? SQL Server › Query Optimization & Plans | SQL Server | Query Optimization & Plans | Intermediate |
| 4 | How do you use the SQL Server Query Store feature to identify and address query performance regressions over time? SQL Server › Query Optimization & Plans | SQL Server | Query Optimization & Plans | Advanced |
| 5 | What are the most common causes of poor query performance that you should check first when troubleshooting a slow query? SQL Server › Query Optimization & Plans | SQL Server | Query Optimization & Plans | Intermediate |
| 6 | How would you use query hints to influence the optimizer's behavior when you have determined the default execution plan is genuinely suboptimal? SQL Server › Query Optimization & Plans | SQL Server | Query Optimization & Plans | Advanced |
| 7 | What is the difference between a table scan and an index seek in an execution plan, and why does this distinction matter for performance? SQL Server › Query Optimization & Plans | SQL Server | Query Optimization & Plans | Beginner |
| 8 | What is Query Store in SQL Server, and what information does it automatically capture? SQL Server › Query Store | SQL Server | Query Store | Beginner |
| 9 | How do you view the top resource consuming queries captured by Query Store? SQL Server › Query Store | SQL Server | Query Store | Beginner |
| 10 | How do you use Query Store to identify a query whose performance has regressed after a recent code deployment? SQL Server › Query Store | SQL Server | Query Store | Intermediate |
| 11 | How do you force SQL Server to use a specific, previously known good execution plan for a query using Query Store? SQL Server › Query Store | SQL Server | Query Store | Intermediate |
| 12 | How do you convert the results of a SQL query directly into an XML formatted document using the FOR XML clause? SQL Server › XML Data Type & Querying | SQL Server | XML Data Type & Querying | Intermediate |
| 13 | How can Query Store data be queried directly using its underlying system views for custom analysis beyond the built-in reports? SQL Server › Query Store | SQL Server | Query Store | Advanced |
| 14 | How does an XML index improve query performance against large or frequently queried XML columns? SQL Server › XML Data Type & Querying | SQL Server | XML Data Type & Querying | Advanced |
| 15 | How do statistics and query optimization differ between temporary tables and table variables, and how does this affect performance for larger data sets? SQL Server › Temporary Tables & Table Variables | SQL Server | Temporary Tables & Table Variables | Intermediate |
| 16 | How do you extract a value from an XML column using the value method? SQL Server › XML Data Type & Querying | SQL Server | XML Data Type & Querying | Beginner |
| 17 | What is a common table expression, and why would you use one instead of a subquery? SQL Server › Common Table Expressions (CTEs) | SQL Server | Common Table Expressions (CTEs) | Beginner |
| 18 | How do you write a query that retrieves data from a table on a linked server? SQL Server › Linked Servers | SQL Server | Linked Servers | Beginner |
| 19 | What is the difference between a correlated and a non correlated subquery? SQL Server › Subqueries | SQL Server | Subqueries | Beginner |
| 20 | How do you filter the results of a grouped query based on an aggregated value, such as only showing customers who spent more than a certain amount? SQL Server › Aggregate Functions & GROUP BY | SQL Server | Aggregate Functions & GROUP BY | Intermediate |
| 21 | How do you correctly combine WHERE and HAVING in the same query to filter both individual rows and grouped results? SQL Server › Aggregate Functions & GROUP BY | SQL Server | Aggregate Functions & GROUP BY | Intermediate |
| 22 | How do you query the change data captured for a table to find out exactly what changed within a specific time range? SQL Server › Change Data Capture (CDC) | SQL Server | Change Data Capture (CDC) | Intermediate |
| 23 | What performance issues commonly arise when querying data through a linked server, and how can you address them? SQL Server › Linked Servers | SQL Server | Linked Servers | Intermediate |
| 24 | How does partition elimination improve query performance for a partitioned table? SQL Server › Partitioning | SQL Server | Partitioning | Intermediate |
| 25 | Why can calling a scalar user defined function within a query against a large table cause significant performance problems? SQL Server › User Defined Functions | SQL Server | User Defined Functions | Intermediate |
| 26 | In what situations does SQL Server's query optimizer typically prefer a hash join over a nested loop join or a merge join? SQL Server › Hash Indexes & Hash Join Operations | SQL Server | Hash Indexes & Hash Join Operations | Intermediate |
| 27 | How do the ROLLUP and CUBE extensions to GROUP BY help you generate subtotal and grand total rows in a single query? SQL Server › Aggregate Functions & GROUP BY | SQL Server | Aggregate Functions & GROUP BY | Advanced |
| 28 | How would you build a dynamic SQL statement inside a stored procedure that constructs a flexible search query based on several optional filter parameters? SQL Server › Dynamic SQL | SQL Server | Dynamic SQL | Advanced |
| 29 | How do you rank and sort full text search results by their relevance to the search query? SQL Server › Full Text Search | SQL Server | Full Text Search | Advanced |
| 30 | How does SQL Server's query optimizer decide which physical join algorithm, such as nested loops, merge join, or hash join, to actually use for a given query? SQL Server › Joins | SQL Server | Joins | Advanced |
| 31 | How would you troubleshoot a linked server query that is running much slower than expected? SQL Server › Linked Servers | SQL Server | Linked Servers | Advanced |
| 32 | How does SQL Server compile and cache execution plans for stored procedures, and how does this affect performance compared to ad hoc queries? SQL Server › Stored Procedures & Functions | SQL Server | Stored Procedures & Functions | Advanced |
| 33 | How would you rewrite a slow correlated subquery as an equivalent, potentially faster JOIN based query? SQL Server › Subqueries | SQL Server | Subqueries | Advanced |
| 34 | How does the SQL Server query optimizer typically handle and potentially transform a subquery internally when generating an execution plan? SQL Server › Subqueries | SQL Server | Subqueries | Advanced |
| 35 | How would you use window functions together to build a comprehensive sales analytics report showing running totals, rankings, and period over period comparisons all in a single query? SQL Server › Window Functions | SQL Server | Window Functions | Advanced |
| 36 | What are aggregate functions in SQL Server, and what are the most common ones? SQL Server › Aggregate Functions & GROUP BY | SQL Server | Aggregate Functions & GROUP BY | Beginner |
| 37 | What is the difference between the primary replica and secondary replicas in an Always On Availability Group? SQL Server › Always On Availability Groups | SQL Server | Always On Availability Groups | Beginner |
| 38 | What is Change Data Capture in SQL Server, and what problem does it solve? SQL Server › Change Data Capture (CDC) | SQL Server | Change Data Capture (CDC) | Beginner |
| 39 | What is a columnstore index in SQL Server, and how is it different from a regular index? SQL Server › Columnstore Indexes | SQL Server | Columnstore Indexes | Beginner |
| 40 | What is the difference between a clustered columnstore index and a nonclustered columnstore index? SQL Server › Columnstore Indexes | SQL Server | Columnstore Indexes | Beginner |
| 41 | What is a cursor in SQL Server, and why do most developers try to avoid using them? SQL Server › Cursors | SQL Server | Cursors | Beginner |
| 42 | What is the difference between VARCHAR and NVARCHAR, and when should you choose one over the other? SQL Server › Data Types & Schema Design | SQL Server | Data Types & Schema Design | Beginner |
| 43 | What is an index in SQL Server, and how does it help speed up queries? SQL Server › Indexes | SQL Server | Indexes | Beginner |
| 44 | What is the difference between a clustered index and a nonclustered index? SQL Server › Indexes | SQL Server | Indexes | Beginner |
| 45 | 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 |
| 46 | What is a linked server in SQL Server, and why would you set one up? SQL Server › Linked Servers | SQL Server | Linked Servers | Beginner |
| 47 | How do you create a basic SQL Server Agent job that runs a T-SQL script on a defined schedule? SQL Server › SQL Server Agent & Job Scheduling | SQL Server | SQL Server Agent & Job Scheduling | Beginner |
| 48 | What is SQL Server Profiler, and what is it traditionally used for? SQL Server › SQL Server Profiler & Extended Events | SQL Server | SQL Server Profiler & Extended Events | Beginner |
| 49 | What are Extended Events, and why are they generally recommended over SQL Server Profiler for modern monitoring needs? SQL Server › SQL Server Profiler & Extended Events | SQL Server | SQL Server Profiler & Extended Events | Beginner |
| 50 | What is a stored procedure in SQL Server, and what are the main benefits of using one? SQL Server › Stored Procedures & Functions | SQL Server | Stored Procedures & Functions | Beginner |
| 51 | What is the difference between a scalar function and a table valued function in SQL Server? SQL Server › Stored Procedures & Functions | SQL Server | Stored Procedures & Functions | Beginner |
| 52 | What is the difference between an inline table valued function and a multi statement table valued function? SQL Server › User Defined Functions | SQL Server | User Defined Functions | Beginner |
| 53 | What is a hash join, and how does SQL Server use it to combine two tables? SQL Server › Hash Indexes & Hash Join Operations | SQL Server | Hash Indexes & Hash Join Operations | Beginner |
| 54 | What is an availability group listener, and why is it important for applications connecting to an Always On Availability Group? SQL Server › Always On Availability Groups | SQL Server | Always On Availability Groups | Intermediate |
| 55 | What kinds of workloads benefit the most from columnstore indexes, and which workloads should avoid them? SQL Server › Columnstore Indexes | SQL Server | Columnstore Indexes | Intermediate |
| 56 | What tools or queries can you use to monitor the health and effectiveness of a columnstore index over time? SQL Server › Columnstore Indexes | SQL Server | Columnstore Indexes | Intermediate |
| 57 | What are the main limitations of common table expressions that developers should be aware of? SQL Server › Common Table Expressions (CTEs) | SQL Server | Common Table Expressions (CTEs) | Intermediate |
| 58 | How do you temporarily disable a constraint to perform a bulk data load, and how do you safely re-enable it afterward? SQL Server › Constraints (Primary Key, Foreign Key, Check & Unique) | SQL Server | Constraints (Primary Key, Foreign Key, Check & Unique) | Intermediate |
| 59 | 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 |
| 60 | What is the performance impact of using a cursor compared to a set based operation on a large table, and why does this difference occur? SQL Server › Cursors | SQL Server | Cursors | Intermediate |
| 61 | What does the FAST_FORWARD cursor option do, and when should you use it? SQL Server › Cursors | SQL Server | Cursors | Intermediate |
| 62 | What are the differences between DATETIME, DATETIME2, and DATE, and when would you choose each one? SQL Server › Data Types & Schema Design | SQL Server | Data Types & Schema Design | Intermediate |
| 63 | How do you safely use sp_executesql to execute dynamic SQL with parameters, avoiding the risks of simple string concatenation? SQL Server › Dynamic SQL | SQL Server | Dynamic SQL | Intermediate |
| 64 | What is the difference between the CONTAINS and FREETEXT predicates when performing a full text search? SQL Server › Full Text Search | SQL Server | Full Text Search | Intermediate |
| 65 | How do you perform a proximity search using full text search, finding rows where two words appear near each other? SQL Server › Full Text Search | SQL Server | Full Text Search | Intermediate |
| 66 | How often does a full text index need to be updated to reflect changes made to the underlying table's data? SQL Server › Full Text Search | SQL Server | Full Text Search | Intermediate |
| 67 | What is a covering index, and how does it help avoid an expensive lookup operation? SQL Server › Indexes | SQL Server | Indexes | Intermediate |
| 68 | How do you identify unused or duplicate indexes that might be safely removed to improve write performance? SQL Server › Indexes | SQL Server | Indexes | Intermediate |
| 69 | How do you decide which columns to choose, and in what order, when creating a composite index covering multiple columns? SQL Server › Indexes | SQL Server | Indexes | Intermediate |
| 70 | What are natively compiled stored procedures, and how do they work together with memory optimized tables? SQL Server › In-Memory OLTP (Memory-Optimized Tables) | SQL Server | In-Memory OLTP (Memory-Optimized Tables) | Intermediate |
| 71 | What are lock escalation and its potential impact on a busy system? SQL Server › Isolation & Locking | SQL Server | Isolation & Locking | Intermediate |
| 72 | What is a FULL OUTER JOIN, and when would you use one instead of a LEFT or RIGHT JOIN? SQL Server › Joins | SQL Server | Joins | Intermediate |
| 73 | How does a sliding window pattern work for automatically archiving old data using partitioning? SQL Server › Partitioning | SQL Server | Partitioning | Intermediate |
| 74 | How do you determine which partition a specific row of data belongs to, and how can this help with troubleshooting? SQL Server › Partitioning | SQL Server | Partitioning | Intermediate |
| 75 | How do you monitor the health and latency of a transactional replication setup to ensure subscribers are staying reasonably up to date? SQL Server › Replication | SQL Server | Replication | Intermediate |
| 76 | How does SQL Server's memory architecture work, particularly regarding the buffer pool and how it affects performance? SQL Server › SQL Server Architecture & Editions | SQL Server | SQL Server Architecture & Editions | Intermediate |
| 77 | What is tempdb, and why is its configuration particularly important for overall SQL Server performance? SQL Server › SQL Server Architecture & Editions | SQL Server | SQL Server Architecture & Editions | Intermediate |
| 78 | 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 |
| 79 | How do you analyze the data captured by an Extended Events session once it has been running for a period of time? SQL Server › SQL Server Profiler & Extended Events | SQL Server | SQL Server Profiler & Extended Events | Intermediate |
| 80 | How do you properly stop and clean up an Extended Events session once it is no longer needed? SQL Server › SQL Server Profiler & Extended Events | SQL Server | SQL Server Profiler & Extended Events | Intermediate |
| 81 | What are the main differences between a stored procedure and a function in terms of what each one can and cannot do? SQL Server › Stored Procedures & Functions | SQL Server | Stored Procedures & Functions | Intermediate |
| 82 | What are common mistakes developers make when writing subqueries that can lead to incorrect results or poor performance? SQL Server › Subqueries | SQL Server | Subqueries | Intermediate |
| 83 | Can you create indexes on a temporary table, and how does this compare to indexing options available for table variables? SQL Server › Temporary Tables & Table Variables | SQL Server | Temporary Tables & Table Variables | Intermediate |
| 84 | How do IF and WHILE control flow statements work in T-SQL, and what kinds of tasks are they typically used for? SQL Server › T-SQL Fundamentals & Syntax | SQL Server | T-SQL Fundamentals & Syntax | Intermediate |
| 85 | What are the differences between the various string functions available in T-SQL, such as SUBSTRING, CONCAT, and TRIM? SQL Server › T-SQL Fundamentals & Syntax | SQL Server | T-SQL Fundamentals & Syntax | Intermediate |
| 86 | What improvements did SQL Server 2019 introduce for scalar function performance through a feature called scalar UDF inlining? SQL Server › User Defined Functions | SQL Server | User Defined Functions | Intermediate |
| 87 | What is an indexed view, and how can it improve the performance of complex, frequently run queries? SQL Server › Views | SQL Server | Views | Intermediate |
| 88 | 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 |
| 89 | How does the BUCKET_COUNT setting affect the performance of a hash index on a memory optimized table? SQL Server › Hash Indexes & Hash Join Operations | SQL Server | Hash Indexes & Hash Join Operations | Intermediate |
| 90 | How would you calculate a running total or a percentage of total using aggregate functions combined with window functions? SQL Server › Aggregate Functions & GROUP BY | SQL Server | Aggregate Functions & GROUP BY | Advanced |
| 91 | What are the performance and storage considerations to keep in mind before enabling Change Data Capture on a very high volume table? SQL Server › Change Data Capture (CDC) | SQL Server | Change Data Capture (CDC) | Advanced |
| 92 | What are delta stores and rowgroups in the context of columnstore indexes, and how do they affect performance over time? SQL Server › Columnstore Indexes | SQL Server | Columnstore Indexes | Advanced |
| 93 | How would you rewrite a common cursor based row by row update into an equivalent, much faster set based UPDATE statement? SQL Server › Cursors | SQL Server | Cursors | Advanced |
| 94 | What factors should guide the decision to denormalize a schema for performance reasons, and what tradeoffs come with that decision? SQL Server › Data Types & Schema Design | SQL Server | Data Types & Schema Design | Advanced |
| 95 | What are the performance tradeoffs of using dynamic SQL compared to static, precompiled SQL statements? SQL Server › Dynamic SQL | SQL Server | Dynamic SQL | Advanced |
| 96 | How does index fragmentation happen over time, and how do you address it? SQL Server › Indexes | SQL Server | Indexes | Advanced |
| 97 | How do filtered indexes work, and what specific scenarios benefit most from using one? SQL Server › Indexes | SQL Server | Indexes | Advanced |
| 98 | 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 |
| 99 | 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 |
| 100 | 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 |
| 101 | How does a CROSS JOIN work, and what are some legitimate use cases for it despite the risk of accidentally creating a huge result set? SQL Server › Joins | SQL Server | Joins | Advanced |
| 102 | What are alternatives to linked servers for integrating data from multiple different database systems, and when might they be a better choice? SQL Server › Linked Servers | SQL Server | Linked Servers | Advanced |
| 103 | What performance considerations should you keep in mind when using MERGE against very large source and target tables? SQL Server › Merge Statement (Upsert) | SQL Server | Merge Statement (Upsert) | Advanced |
| 104 | When would it make sense to intentionally denormalize a database design, and what specific tradeoffs come with that decision? SQL Server › Normalization | SQL Server | Normalization | Advanced |
| 105 | What performance considerations should you keep in mind when using PIVOT or UNPIVOT on very large datasets? SQL Server › Pivoting & Unpivoting Data | SQL Server | Pivoting & Unpivoting Data | Advanced |
| 106 | How do you monitor and troubleshoot SQL Server Agent job performance and failures across an entire server with many scheduled jobs? SQL Server › SQL Server Agent & Job Scheduling | SQL Server | SQL Server Agent & Job Scheduling | Advanced |
| 107 | How would you plan a hardware and configuration strategy for a new SQL Server installation supporting a large, performance critical application? SQL Server › SQL Server Architecture & Editions | SQL Server | SQL Server Architecture & Editions | Advanced |
| 108 | What is the performance overhead comparison between SQL Server Profiler, server side traces, and Extended Events, and how should this inform monitoring decisions on a production system? SQL Server › SQL Server Profiler & Extended Events | SQL Server | SQL Server Profiler & Extended Events | Advanced |
| 109 | How do you implement row level security to restrict which specific rows a user can see within a shared table, beyond simply controlling access to entire tables or columns? SQL Server › SQL Server Security & Permissions | SQL Server | SQL Server Security & Permissions | Advanced |
| 110 | How would you decide between using a temporary table, a table variable, and a common table expression for a specific intermediate data processing need within a stored procedure? SQL Server › Temporary Tables & Table Variables | SQL Server | Temporary Tables & Table Variables | Advanced |
| 111 | How does tempdb contention affect the performance of applications that heavily rely on temporary tables, and what strategies help mitigate this? SQL Server › Temporary Tables & Table Variables | SQL Server | Temporary Tables & Table Variables | Advanced |
| 112 | What is the performance overhead of Transparent Data Encryption, and how does it compare across different SQL Server versions? SQL Server › Transparent Data Encryption | SQL Server | Transparent Data Encryption | Advanced |
| 113 | What are the performance implications of using triggers heavily on a busy, high transaction volume table? SQL Server › Triggers | SQL Server | Triggers | Advanced |
| 114 | How do nested and recursive triggers work in SQL Server, and what precautions should be taken to avoid unexpected behavior or infinite loops? SQL Server › Triggers | SQL Server | Triggers | Advanced |
| 115 | What are the tradeoffs between implementing complex reusable business logic as a user defined function versus building it directly into application code? SQL Server › User Defined Functions | SQL Server | User Defined Functions | Advanced |
| 116 | What happens to the performance and correctness of a view if the underlying tables change significantly after the view was originally created? SQL Server › Views | SQL Server | Views | Advanced |
| 117 | How does the ROWS versus RANGE frame specification within an OVER clause affect the result of a window function like a running total? SQL Server › Window Functions | SQL Server | Window Functions | Advanced |
| 118 | How would you read a deadlock graph to identify the exact queries and resources that caused a specific deadlock? SQL Server › Deadlocks | SQL Server | Deadlocks | Advanced |
| 119 | What happens when a hash join does not have enough memory available to build its hash table entirely in memory, and how does this affect performance? SQL Server › Hash Indexes & Hash Join Operations | SQL Server | Hash Indexes & Hash Join Operations | Advanced |