| 1 | What are the most commonly used data types in SQL Server, and when would you use each one? SQL Server › Data Types & Schema Design | SQL Server | Data Types & Schema Design | Beginner |
| 2 | How do you design a schema to properly handle a many to many relationship between two entities, such as students and courses? SQL Server › Data Types & Schema Design | SQL Server | Data Types & Schema Design | Advanced |
| 3 | How do you choose an appropriately sized data type for a column to balance storage efficiency with future flexibility? SQL Server › Data Types & Schema Design | SQL Server | Data Types & Schema Design | Intermediate |
| 4 | 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 |
| 5 | 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 |
| 6 | 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 |
| 7 | How do computed columns work in SQL Server, and what are some practical use cases for them? SQL Server › Data Types & Schema Design | SQL Server | Data Types & Schema Design | Intermediate |
| 8 | What is database normalization, and why is it an important concept in schema design? SQL Server › Normalization | SQL Server | Normalization | Beginner |
| 9 | What built-in masking functions are available in SQL Server for dynamic data masking? SQL Server › Dynamic Data Masking | SQL Server | Dynamic Data Masking | Beginner |
| 10 | What does it mean to pivot data in SQL Server, and what kind of reporting need does it typically address? SQL Server › Pivoting & Unpivoting Data | SQL Server | Pivoting & Unpivoting Data | Beginner |
| 11 | What does it mean to unpivot data, and how is it essentially the opposite operation of pivoting? SQL Server › Pivoting & Unpivoting Data | SQL Server | Pivoting & Unpivoting Data | Beginner |
| 12 | What is the XML data type in SQL Server, and why would you use it instead of storing XML as plain text? SQL Server › XML Data Type & Querying | SQL Server | XML Data Type & Querying | Beginner |
| 13 | How do you enable Change Data Capture on a specific table, and what does that process actually create? SQL Server › Change Data Capture (CDC) | SQL Server | Change Data Capture (CDC) | Intermediate |
| 14 | 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 |
| 15 | How do you grant a specific user or role permission to see the real, unmasked data despite dynamic data masking being applied? SQL Server › Dynamic Data Masking | SQL Server | Dynamic Data Masking | Intermediate |
| 16 | How do you remove dynamic data masking from a column once it is no longer needed? SQL Server › Dynamic Data Masking | SQL Server | Dynamic Data Masking | Intermediate |
| 17 | How would you decide between using the XML data type and the JSON functions available in SQL Server for a new project that needs to store semi structured data? SQL Server › XML Data Type & Querying | SQL Server | XML Data Type & Querying | Advanced |
| 18 | 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 |
| 19 | How would you approach normalizing an existing, poorly designed legacy database without breaking the applications that currently depend on it? SQL Server › Normalization | SQL Server | Normalization | Advanced |
| 20 | How would you design a set of reusable functions for a data warehouse to standardize common calculations like currency conversion or fiscal period determination? SQL Server › User Defined Functions | SQL Server | User Defined Functions | Advanced |
| 21 | 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 |
| 22 | What are some practical, real world use cases where PIVOT and UNPIVOT are commonly applied in business reporting? SQL Server › Pivoting & Unpivoting Data | SQL Server | Pivoting & Unpivoting Data | Intermediate |
| 23 | How do you use the query and nodes methods to work with repeating elements within an XML document, such as a list of items in an order? SQL Server › XML Data Type & Querying | SQL Server | XML Data Type & Querying | Intermediate |
| 24 | How do you modify a specific part of an XML document stored in a column without replacing the entire document? SQL Server › XML Data Type & Querying | SQL Server | XML Data Type & Querying | Intermediate |
| 25 | 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 |
| 26 | How would you build a cross tabulation report using PIVOT that shows multiple aggregate calculations, such as both total and average, for each category? SQL Server › Pivoting & Unpivoting Data | SQL Server | Pivoting & Unpivoting Data | Advanced |
| 27 | What is a primary key constraint, and why is it important for a database table? SQL Server › Constraints (Primary Key, Foreign Key, Check & Unique) | SQL Server | Constraints (Primary Key, Foreign Key, Check & Unique) | Beginner |
| 28 | What is an INNER JOIN, and how does it combine data from two tables? SQL Server › Joins | SQL Server | Joins | Beginner |
| 29 | Can you insert, update, or delete data through a view, and what conditions must be met for this to work? SQL Server › Views | SQL Server | Views | Beginner |
| 30 | How do you prevent a recursive common table expression from running forever if the underlying data accidentally contains a circular reference? SQL Server › Common Table Expressions (CTEs) | SQL Server | Common Table Expressions (CTEs) | Intermediate |
| 31 | What is a check constraint, and how do you use one to enforce a specific business rule at the database level? SQL Server › Constraints (Primary Key, Foreign Key, Check & Unique) | SQL Server | Constraints (Primary Key, Foreign Key, Check & Unique) | Intermediate |
| 32 | 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 |
| 33 | 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 |
| 34 | What practical problems, such as update anomalies, can occur in a poorly normalized database, and how does normalization address them? SQL Server › Normalization | SQL Server | Normalization | Intermediate |
| 35 | 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 |
| 36 | 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 |
| 37 | What is the difference between using EXISTS and IN when checking for the presence of related data with a subquery? SQL Server › Subqueries | SQL Server | Subqueries | Intermediate |
| 38 | 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 |
| 39 | Why should triggers generally be designed to handle multiple affected rows at once rather than assuming only a single row was changed? SQL Server › Triggers | SQL Server | Triggers | Intermediate |
| 40 | How would you add a new constraint to an existing table that already contains data, especially if some existing rows might violate that constraint? SQL Server › Constraints (Primary Key, Foreign Key, Check & Unique) | SQL Server | Constraints (Primary Key, Foreign Key, Check & Unique) | Advanced |
| 41 | What are the performance considerations and limitations of querying JSON data stored as text compared to using normalized relational columns? SQL Server › JSON Support in SQL Server | SQL Server | JSON Support in SQL Server | Advanced |
| 42 | How would you use MERGE to implement a slowly changing dimension pattern in a data warehouse, tracking historical changes to a record over time? SQL Server › Merge Statement (Upsert) | SQL Server | Merge Statement (Upsert) | Advanced |
| 43 | What is full text search in SQL Server, and how is it different from a regular LIKE search? SQL Server › Full Text Search | SQL Server | Full Text Search | Beginner |
| 44 | How do you create a full text index on a table so you can use full text search functions? SQL Server › Full Text Search | SQL Server | Full Text Search | Beginner |
| 45 | What is an index in SQL Server, and how does it help speed up queries? SQL Server › Indexes | SQL Server | Indexes | Beginner |
| 46 | How do you store and query JSON formatted text in SQL Server? SQL Server › JSON Support in SQL Server | SQL Server | JSON Support in SQL Server | Beginner |
| 47 | What is the difference between the JSON_VALUE and JSON_QUERY functions? SQL Server › JSON Support in SQL Server | SQL Server | JSON Support in SQL Server | Beginner |
| 48 | What does the MERGE statement do in SQL Server, and why is it called an upsert operation? SQL Server › Merge Statement (Upsert) | SQL Server | Merge Statement (Upsert) | Beginner |
| 49 | What are the three main clauses of a MERGE statement, and what does each one control? SQL Server › Merge Statement (Upsert) | SQL Server | Merge Statement (Upsert) | Beginner |
| 50 | What is the first normal form, and what specific rule does it require a table to follow? SQL Server › Normalization | SQL Server | Normalization | Beginner |
| 51 | 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 |
| 52 | What is the difference between Windows Authentication and SQL Server Authentication? SQL Server › SQL Server Security & Permissions | SQL Server | SQL Server Security & Permissions | Beginner |
| 53 | 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 |
| 54 | What is the difference between an AFTER trigger and an INSTEAD OF trigger? SQL Server › Triggers | SQL Server | Triggers | Beginner |
| 55 | 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 |
| 56 | What is a view in SQL Server, and what benefits does it provide? SQL Server › Views | SQL Server | Views | Beginner |
| 57 | What is the difference between COUNT(*), COUNT(1), and COUNT(column_name) in SQL Server? SQL Server › Aggregate Functions & GROUP BY | SQL Server | Aggregate Functions & GROUP BY | Intermediate |
| 58 | What are the three recovery models available in SQL Server, and how do they affect your backup strategy? SQL Server › Backup & Recovery | SQL Server | Backup & Recovery | Intermediate |
| 59 | How does columnstore compression work, and why does it typically achieve much higher compression ratios than regular row based storage? SQL Server › Columnstore Indexes | SQL Server | Columnstore Indexes | Intermediate |
| 60 | What is a recursive common table expression, and what kind of problems is it commonly used to solve? SQL Server › Common Table Expressions (CTEs) | SQL Server | Common Table Expressions (CTEs) | Intermediate |
| 61 | How is a unique constraint different from a primary key constraint, and when would you use one instead of the other? SQL Server › Constraints (Primary Key, Foreign Key, Check & Unique) | SQL Server | Constraints (Primary Key, Foreign Key, Check & Unique) | Intermediate |
| 62 | 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 |
| 63 | 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 |
| 64 | 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 |
| 65 | What common mistakes do developers make when writing multi table joins that can lead to unexpectedly large or incorrect result sets? SQL Server › Joins | SQL Server | Joins | Intermediate |
| 66 | 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 |
| 67 | How do you update a specific value within a JSON document stored in a column without replacing the entire JSON text? SQL Server › JSON Support in SQL Server | SQL Server | JSON Support in SQL Server | Intermediate |
| 68 | What are the security considerations when configuring authentication for a linked server connection? SQL Server › Linked Servers | SQL Server | Linked Servers | Intermediate |
| 69 | How do you use the OUTPUT clause together with a MERGE statement to capture which rows were inserted, updated, or deleted? SQL Server › Merge Statement (Upsert) | SQL Server | Merge Statement (Upsert) | Intermediate |
| 70 | What is the second normal form, and how does it build on the requirements of the first normal form? SQL Server › Normalization | SQL Server | Normalization | Intermediate |
| 71 | What is the third normal form, and what kind of dependency does it specifically eliminate? SQL Server › Normalization | SQL Server | Normalization | Intermediate |
| 72 | 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 |
| 73 | 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 |
| 74 | 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 |
| 75 | 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 |
| 76 | How do you access the old and new values of a row that was just updated from inside a trigger? SQL Server › Triggers | SQL Server | Triggers | Intermediate |
| 77 | 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 |
| 78 | 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 |
| 79 | What is the difference between RANK, DENSE_RANK, and ROW_NUMBER when there are tied values in the ordering column? SQL Server › Window Functions | SQL Server | Window Functions | Intermediate |
| 80 | How do LAG and LEAD window functions let you access values from a previous or following row without needing a self join? SQL Server › Window Functions | SQL Server | Window Functions | Intermediate |
| 81 | How does the PARTITION BY clause within a window function differ from GROUP BY, and why is this distinction useful? SQL Server › Window Functions | SQL Server | Window Functions | Intermediate |
| 82 | How would you use a CTE combined with a window function to identify and remove duplicate rows from a table? SQL Server › Common Table Expressions (CTEs) | SQL Server | Common Table Expressions (CTEs) | Advanced |
| 83 | How would you configure full text search to handle different languages correctly, including proper word breaking and stemming? SQL Server › Full Text Search | SQL Server | Full Text Search | Advanced |
| 84 | What migration steps and limitations should you consider when converting an existing disk based table to a memory optimized table? SQL Server › In-Memory OLTP (Memory-Optimized Tables) | SQL Server | In-Memory OLTP (Memory-Optimized Tables) | Advanced |
| 85 | 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 |
| 86 | How would you decide on an appropriate partitioning strategy, including the partition key and boundary values, for a very large table in a new system? SQL Server › Partitioning | SQL Server | Partitioning | Advanced |
| 87 | How do CASE expressions work in T-SQL, and how can they be used to implement conditional logic directly within a SELECT statement? SQL Server › T-SQL Fundamentals & Syntax | SQL Server | T-SQL Fundamentals & Syntax | Advanced |
| 88 | 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 |
| 89 | How would you troubleshoot a query that is unexpectedly using a slow hash join when a much faster index seek and nested loop join should be possible? SQL Server › Hash Indexes & Hash Join Operations | SQL Server | Hash Indexes & Hash Join Operations | Advanced |