MCTS Database Management & SQL Server 2

Question 1

Which SQL Server isolation level prevents dirty reads but allows non-repeatable reads?

Accepted Answer

READ COMMITTED

Answer

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?

Accepted Answer

Allows reading uncommitted data without acquiring shared locks

Answer

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?

Accepted Answer

Read-only filegroup

Answer

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?

Accepted Answer

Aggregate performance statistics for cached query plans

Answer

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?

Accepted Answer

SQL Server inserts it in the correct logical position, potentially causing a page split

Answer

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.

(MCTS) Microsoft Certified Technology Specialist Practice Test

(MCTS) Microsoft Certified Technology Specialist Practice Test

MCTS Database Management & SQL Server 2