SQL Server Database Development Flashcards
7 cards from real MCTS practice questions. Tap to flip, then mark Knew It or Still Learning — missed cards come back until you master them.
Read the first 7 SQL Server Database Development flashcards as text
What is the purpose of the MERGE statement in SQL Server?
Answer: It performs INSERT, UPDATE, and DELETE operations in a single statement based on a join condition
MERGE allows you to synchronize a target table with a source by specifying actions for matched, not matched by target, and not matched by source conditions.
Which SQL Server feature enables you to define a reusable parameterized query fragment that is inlined at compile time?
Answer: Inline Table-Valued Function
Inline Table-Valued Functions (iTVFs) are expanded into the calling query's execution plan like a macro, allowing the optimizer to push predicates into the function.
What does the APPLY operator do differently from a standard JOIN?
Answer: APPLY invokes a table-valued function or subquery for each row of the outer table expression
CROSS APPLY and OUTER APPLY invoke a right table expression once per row of the left expression, enabling row-by-row correlated table operations.
In SQL Server, what is the purpose of filegroups?
Answer: To organize database files for storage management, backup, and performance optimization
Filegroups allow DBAs to control where database objects are physically stored, enabling placement of tables and indexes on specific disks for performance and manageability.
Which SQL Server data type should be used to store a globally unique identifier (GUID)?
Answer: UNIQUEIDENTIFIER
The UNIQUEIDENTIFIER data type stores a 16-byte GUID value and works with the NEWID() and NEWSEQUENTIALID() functions.
What happens when you insert a row into a table with an IDENTITY column and then execute ROLLBACK?
Answer: The IDENTITY value is permanently consumed and not reused even after rollback
IDENTITY values are allocated outside the transaction scope, so rolling back a transaction does not reclaim the consumed identity value, creating gaps.
Which execution plan operator indicates that SQL Server is performing a row-by-row lookup from a non-clustered index back to the clustered index?
Answer: Key Lookup
A Key Lookup (formerly Bookmark Lookup) occurs when a non-clustered index doesn't cover all needed columns, requiring SQL Server to fetch remaining columns from the clustered index.