MCTS SQL Server Database Development 2 — Questions and Answers
Question 1: Which isolation level in SQL Server prevents dirty reads and non-repeatable reads but still allows phantom reads?
- READ UNCOMMITTED
- READ COMMITTED
- REPEATABLE READ (Correct answer)
- SERIALIZABLE
Correct answer: REPEATABLE READ
REPEATABLE READ prevents dirty reads and non-repeatable reads by holding shared locks until the end of the transaction, but it does not prevent phantom reads.
Question 2: What does the NOLOCK query hint do in SQL Server?
- Forces an exclusive lock on the table
- Allows reading uncommitted data (Correct answer)
- Prevents any locks from being taken on the table
- Upgrades shared locks to update locks
Correct answer: Allows reading uncommitted data
NOLOCK (equivalent to READ UNCOMMITTED) allows a query to read dirty, uncommitted data, which can improve concurrency but risks reading inconsistent data.
Question 3: Which SQL Server feature allows you to store XML data in a typed format with schema validation?
- VARCHAR(MAX)
- XML data type with XML Schema Collection (Correct answer)
- NTEXT column
- VARBINARY(MAX)
Correct answer: XML data type with XML Schema Collection
The XML data type combined with an XML Schema Collection enforces structure and data types on stored XML documents.
Question 4: In a SQL Server indexed view, which SET options MUST be ON when creating or querying the view?
- ANSI_NULLS and QUOTED_IDENTIFIER only
- ARITHABORT and NUMERIC_ROUNDABORT only
- ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER (Correct answer)
- NOCOUNT and XACT_ABORT only
Correct answer: ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER
SQL Server requires several SET options to be ON (and NUMERIC_ROUNDABORT OFF) to create or use indexed views correctly.
Question 5: What is the purpose of the OUTPUT clause in a DML statement?
- It redirects query results to a file
- It returns rows affected by INSERT, UPDATE, DELETE, or MERGE from inserted/deleted tables (Correct answer)
- It sends email notifications after DML completes
- It logs DML changes to the SQL Server error log
Correct answer: It returns rows affected by INSERT, UPDATE, DELETE, or MERGE from inserted/deleted tables
The OUTPUT clause accesses the virtual inserted and deleted tables to return data about rows affected by a DML statement.
Question 6: Which function returns the rank of each row within a partition without gaps in the ranking values?
- RANK()
- DENSE_RANK() (Correct answer)
- ROW_NUMBER()
- NTILE()
Correct answer: DENSE_RANK()
DENSE_RANK() assigns consecutive rank values without gaps, unlike RANK() which skips values after ties.
Question 7: What does a CLUSTERED index physically determine in SQL Server?
- The order of rows in a separate index structure
- The physical storage order of data rows in the table (Correct answer)
- Which columns are included in covering indexes
- The order non-clustered indexes reference data
Correct answer: The physical storage order of data rows in the table
A clustered index defines the physical order of data rows stored on disk, meaning a table can only have one clustered index.
MCTS SQL Server Database Development 2
Which isolation level in SQL Server prevents dirty reads and non-repeatable reads but still allows phantom reads?