"Blocking, RCSI, and Why NOLOCK Is the Wrong Fix"

1. What does 'WITH (NOLOCK)' achieve in SQL queries?

It locks rows to prevent blocking It allows reading uncommitted data It commits transactions immediately It ensures no data is missed when reading

2. What is the primary disadvantage of using 'WITH (NOLOCK)' as mentioned in the lesson?

It improves performance but increases blocking It allows dirty reads, potentially leading to incorrect report data It reduces data retrieval speed significantly It guarantees that all data returned is committed

3. What is the key benefit of enabling READ COMMITTED SNAPSHOT (RCSI)?

Readers see only uncommitted changes Writers and readers do not block each other All transactions are automatically committed It requires application code changes for compatibility

4. What is one of the costs associated with enabling READ COMMITTED SNAPSHOT (RCSI)?

Increased load on primary database Temporary loss of existing data Row versions being stored in tempdb Faster query execution without resource concerns

Continue: "Heavier Options When the Query Is Genuinely Expensive" → Review this lesson Retake quiz