| 1 | 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 |
| 2 | 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 |
| 3 | 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 |
| 4 | How do you use output parameters in a stored procedure to return a value back to the calling code, in addition to or instead of a normal result set? SQL Server › Stored Procedures & Functions | SQL Server | Stored Procedures & Functions | Intermediate |
| 5 | What is the difference between using EXEC and calling a stored procedure through parameterized application code, and why does this distinction matter for security? SQL Server › Stored Procedures & Functions | SQL Server | Stored Procedures & Functions | Intermediate |
| 6 | How would you design a stored procedure to handle a complex business process involving multiple steps, proper error handling, and transaction management together? SQL Server › Stored Procedures & Functions | SQL Server | Stored Procedures & Functions | Advanced |
| 7 | 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 |
| 8 | What restrictions exist on what a user defined function is allowed to do compared to a stored procedure? SQL Server › User Defined Functions | SQL Server | User Defined Functions | Intermediate |
| 9 | 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 |
| 10 | What is a user defined function in SQL Server, and why would you create one instead of repeating the same logic in multiple queries? SQL Server › User Defined Functions | SQL Server | User Defined Functions | Beginner |
| 11 | 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 |
| 12 | 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 |
| 13 | What error information functions are available inside a CATCH block to help you understand what went wrong? SQL Server › Error Handling with TRY CATCH | SQL Server | Error Handling with TRY CATCH | Beginner |
| 14 | 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 |
| 15 | How do nested TRY CATCH blocks work, and when would you use them in a complex stored procedure? SQL Server › Error Handling with TRY CATCH | SQL Server | Error Handling with TRY CATCH | Advanced |
| 16 | What is a cursor in SQL Server, and why do most developers try to avoid using them? SQL Server › Cursors | SQL Server | Cursors | Beginner |
| 17 | What is dynamic SQL, and why would you need to build and execute a SQL statement as a string rather than writing it directly? SQL Server › Dynamic SQL | SQL Server | Dynamic SQL | Beginner |
| 18 | What is SQL injection, and how does it relate to writing dynamic SQL unsafely? SQL Server › Dynamic SQL | SQL Server | Dynamic SQL | Beginner |
| 19 | How does a TRY CATCH block work in SQL Server, and why is it useful? SQL Server › Error Handling with TRY CATCH | SQL Server | Error Handling with TRY CATCH | Beginner |
| 20 | What is SQL Server Agent, and what kinds of tasks is it typically used to automate? SQL Server › SQL Server Agent & Job Scheduling | SQL Server | SQL Server Agent & Job Scheduling | Beginner |
| 21 | 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 |
| 22 | What is a local temporary table in SQL Server, and how is it different from a regular permanent table? SQL Server › Temporary Tables & Table Variables | SQL Server | Temporary Tables & Table Variables | Beginner |
| 23 | What is a table variable in SQL Server, and how is it declared and used? SQL Server › Temporary Tables & Table Variables | SQL Server | Temporary Tables & Table Variables | Beginner |
| 24 | What is T-SQL, and how does it relate to standard SQL? SQL Server › T-SQL Fundamentals & Syntax | SQL Server | T-SQL Fundamentals & Syntax | Beginner |
| 25 | How do you declare and use a variable in T-SQL? SQL Server › T-SQL Fundamentals & Syntax | SQL Server | T-SQL Fundamentals & Syntax | Beginner |
| 26 | What are the basic steps required to declare, open, use, and properly clean up a cursor? SQL Server › Cursors | SQL Server | Cursors | Intermediate |
| 27 | What does the FAST_FORWARD cursor option do, and when should you use it? SQL Server › Cursors | SQL Server | Cursors | Intermediate |
| 28 | How would you build a dynamic SQL query where the table or column names themselves need to change based on input, since parameters cannot be used for object names? SQL Server › Dynamic SQL | SQL Server | Dynamic SQL | Intermediate |
| 29 | How do you debug a dynamic SQL statement when it fails or does not produce the expected results? SQL Server › Dynamic SQL | SQL Server | Dynamic SQL | Intermediate |
| 30 | What is the difference between using THROW and RAISERROR to raise a custom error in SQL Server? SQL Server › Error Handling with TRY CATCH | SQL Server | Error Handling with TRY CATCH | Intermediate |
| 31 | 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 |
| 32 | How do you generate JSON formatted output directly from a SQL query's result set? SQL Server › JSON Support in SQL Server | SQL Server | JSON Support in SQL Server | Intermediate |
| 33 | 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 |
| 34 | 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 |
| 35 | How do you grant and revoke specific permissions, such as SELECT or EXECUTE, on individual database objects rather than through broader roles? SQL Server › SQL Server Security & Permissions | SQL Server | SQL Server Security & Permissions | Intermediate |
| 36 | What is the difference between a batch and a transaction in T-SQL, and how does the GO keyword relate to batches? SQL Server › T-SQL Fundamentals & Syntax | SQL Server | T-SQL Fundamentals & Syntax | Intermediate |
| 37 | 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 |
| 38 | In what rare situations might using a cursor still be a reasonable choice despite the general recommendation to avoid them? SQL Server › Cursors | SQL Server | Cursors | Advanced |
| 39 | How would you build a centralized, reusable error logging procedure that captures detailed error information whenever it is called from within a CATCH block? SQL Server › Error Handling with TRY CATCH | SQL Server | Error Handling with TRY CATCH | Advanced |
| 40 | 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 |
| 41 | How would you design a complex SQL Server Agent job with multiple steps that need to run conditionally based on the success or failure of previous steps? SQL Server › SQL Server Agent & Job Scheduling | SQL Server | SQL Server Agent & Job Scheduling | Advanced |