MCTS SQL Server Database Development 2

Question 1

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

Accepted Answer

REPEATABLE READ

Answer

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?

Accepted Answer

Allows reading uncommitted data

Answer

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?

Accepted Answer

XML data type with XML Schema Collection

Answer

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?

Accepted Answer

ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER

Answer

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?

Accepted Answer

It returns rows affected by INSERT, UPDATE, DELETE, or MERGE from inserted/deleted tables

Answer

The OUTPUT clause accesses the virtual inserted and deleted tables to return data about rows affected by a DML statement.

(MCTS) Microsoft Certified Technology Specialist Practice Test

(MCTS) Microsoft Certified Technology Specialist Practice Test

MCTS SQL Server Database Development 2