Quiz on NOLOCK hints in SQL Server

Grab a pen and notepad, and jot down your answers as you go, then check your answers at the key at the bottom of the page.

Questions

Q1. Which table hint is equivalent to NOLOCK?

1. NOHOLDLOCK
2. LESSLOCK
3. READCOMMITTED
4. READUNCOMMITTED
5. YOLO

Q2. Fill in the blank.

This statement applies NOLOCK to every table referenced in your session (unless otherwise hinted in the queries).

SET  _________ ISOLATION LEVEL READ UNCOMMITTED

Q3. Fill in the blank with a term that describes a phenomenon that only occurs under the read uncommitted isolation level.

Reading uncommitted data is called a  _______ read.

Q4. Other “bad data” phenomena that can occur under READ UNCOMMITTED are….

Choose all that apply….

1. Un-split reads
2. Non-repeatable reads
3. Phantom reads
4. Backward reads
5. Hopeful reads

Q5. Fill in the blank

Creating an _______  can often reduce or eliminate blocking.

Q6. Which lock type does a query using NOLOCK still require against a table?

1.  LCK_M_RIn_X
2.  LCK_M_U
3.  LCK_M_SCH_S
4.  LCK_M_SCH_M

Q7. Which type of pages are used by an allocation order scan?

1.  XTP Pages
2.  PFE Pages
3.  B+ Tree Pages
4.  IAM Pages

Q8. If you want to get an allocation order scan, you have to be willing to read uncommitted data.

1.  True
2.  False

Q9 The NOLOCK hint in queries is ignored if you enable Read Committed Snapshot Isolation (RCSI) for a database.

1.  True
2.  False

Scroll down for the answer key :point_down:

Answer Key

• A1. 3. READUNCOMMITTED
• A2. TRANSACTION
• A3. dirty
• A4. 2. non-repeatable reads, 3. phantom reads
• A5. index
• A6. 3. LCK_M_SCH_S
• A7. 4. IAM
• A8. 2. False
• A9. 2. False