MCTS Database Management & SQL Server 2 — Questions and Answers
Question 1: Which SQL Server isolation level prevents dirty reads but allows non-repeatable reads?
- READ UNCOMMITTED
- READ COMMITTED (Correct answer)
- REPEATABLE READ
- SERIALIZABLE
Correct answer: READ COMMITTED
READ COMMITTED prevents dirty reads by only reading committed data, but another transaction can modify the row before the current transaction re-reads it.
Question 2: What is the purpose of the NOLOCK query hint in SQL Server?
- Forces exclusive locks on all rows
- Allows reading uncommitted data without acquiring shared locks (Correct answer)
- Prevents any locks from being released
- Enables row-level versioning automatically
Correct answer: Allows reading uncommitted data without acquiring shared locks
NOLOCK (equivalent to READ UNCOMMITTED) allows a query to read data without acquiring shared locks, potentially returning dirty or phantom reads.
Question 3: Which filegroup type in SQL Server is used to store read-only data for improved performance?
- PRIMARY filegroup
- ROWS filegroup
- Read-only filegroup (Correct answer)
- FILESTREAM filegroup
Correct answer: Read-only filegroup
A read-only filegroup can be placed on slower storage since it never requires write I/O, and SQL Server skips logging for it.
Question 4: What does the SQL Server DMV sys.dm_exec_query_stats expose?
- Current active sessions and their blocking chains
- Aggregate performance statistics for cached query plans (Correct answer)
- Index fragmentation levels for all user tables
- Memory allocation by database component
Correct answer: Aggregate performance statistics for cached query plans
sys.dm_exec_query_stats returns aggregate performance statistics (CPU, I/O, elapsed time, executions) for all cached query plans.
Question 5: A table has a clustered index on CustomerID. What happens when you INSERT a row with a CustomerID value that falls between existing rows?
- SQL Server appends it to the end and rebuilds the index nightly
- SQL Server inserts it in the correct logical position, potentially causing a page split (Correct answer)
- SQL Server rejects the insert until the table is defragmented
- SQL Server places it in a separate overflow page
Correct answer: SQL Server inserts it in the correct logical position, potentially causing a page split
Because a clustered index defines the physical order of data, inserting out-of-sequence values can cause page splits when a data page is full.
Question 6: Which SQL Server feature allows you to query historical data as it existed at a past point in time?
- Change Data Capture
- Temporal Tables (Correct answer)
- Database Snapshots
- Log Shipping
Correct answer: Temporal Tables
Temporal tables (system-versioned) automatically track row history in a history table, and FOR SYSTEM_TIME AS OF lets you query past states.
Question 7: What is the maximum number of columns allowed in a SQL Server composite index key?
- 8
- 16 (Correct answer)
- 32
- 64
Correct answer: 16
SQL Server allows a maximum of 16 key columns per index, with a combined key size limit of 900 bytes for non-clustered indexes (1700 for newer versions).
MCTS Database Management & SQL Server 2
Which SQL Server isolation level prevents dirty reads but allows non-repeatable reads?