SQL Server Database Development Flashcards

Which isolation level in SQL Server prevents dirty reads and non-repeatable reads but still allows phantom reads?

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.

What does the NOLOCK query hint do in SQL Server?

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.

Which SQL Server feature allows you to store XML data in a typed format with schema validation?

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.

In a SQL Server indexed view, which SET options MUST be ON when creating or querying the view?

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.

What is the purpose of the OUTPUT clause in a DML statement?

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.

Which function returns the rank of each row within a partition without gaps in the ranking values?

Answer: DENSE_RANK()

DENSE_RANK() assigns consecutive rank values without gaps, unlike RANK() which skips values after ties.

What does a CLUSTERED index physically determine in SQL Server?

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.