Dirty Records and Table Hints: Wait, Read Dirty, Skip or Fail

Dirty records and table hints work together: a hint decides whether your SELECT sees a change that isn’t committed yet. That change is a dirty record. By default, the SELECT waits. Four other choices let it read the change, skip it or give up.

The Sample Table

Four pots with a price each. Run this in any test database. Every result in this post comes from SQL Server 2025, with two query windows open against the same database.

CREATE TABLE Pots (PotID INT PRIMARY KEY, PotName VARCHAR(20) NOT NULL, Price DECIMAL(6,2) NOT NULL);
INSERT INTO Pots (PotID, PotName, Price) VALUES (1, 'Clay Pot', 24.00), (2, 'Glazed Pot', 31.00), (3, 'Hanging Pot', 18.00), (4, 'Window Box', 45.00);

One Session Changes a Price

Pretend 240.00 is a typing mistake. This session changes the price of the Clay Pot, waits 12 seconds and rolls back. For those 12 seconds the new price is a dirty record. It lives in the database, but it isn’t official, and it never will be.

BEGIN TRAN;
UPDATE Pots SET Price = 240.00 WHERE PotID = 1;
WAITFOR DELAY '00:00:12';
ROLLBACK TRAN;

Start it in the first window. Three seconds later, start each reader below in a second window.

The Default: Wait

A plain SELECT asks for the row. SQL Server makes it wait until the change is committed or rolled back. This is a safety feature. Only committed data comes back.

SELECT PotID, PotName, Price FROM Pots ORDER BY PotID;

The reader waited about 9 seconds, which is the time left on the writer’s clock. It returned 24.00. It never saw 240.00. The cost is speed: a query that could run in a millisecond waited nine seconds.

READUNCOMMITTED and NOLOCK: Read Dirty

A table hint changes that. READUNCOMMITTED tells the query to read without waiting.

SELECT PotID, PotName, Price FROM Pots WITH (READUNCOMMITTED) ORDER BY PotID;

It returned at once, with 240.00 for the Clay Pot. That price was rolled back a few seconds later, so any report built on it would be wrong. NOLOCK is the same hint under another name, and it returned the same rows.

SELECT PotID, PotName, Price FROM Pots WITH (NOLOCK) ORDER BY PotID;

The old shorthand without WITH, as in FROM Pots (NOLOCK), still ran in my test. Write WITH anyway.

A count is the classic use, when an approximate number is fine. This query returned 4 at once.

SELECT COUNT(*) AS PotRows FROM Pots WITH (NOLOCK);

Keep the count rough. A NOLOCK query can count rows twice or miss them while data moves. NOLOCK: Why It Counts Some Rows Twice and Misses Others shows how.

READPAST: Skip the Locked Row

READPAST leaves out any row that another session has locked.

SELECT PotID, PotName, Price FROM Pots WITH (READPAST) ORDER BY PotID;

The query came back at once with three pots. The Clay Pot is missing, not wrong. This suits a worklist where each worker takes whatever is free. It doesn’t suit a report that has to show every row.

NOWAIT: Fail

NOWAIT gives up instead of waiting.

SELECT PotID, PotName, Price FROM Pots WITH (NOWAIT) ORDER BY PotID;

The answer came at once: Msg 1222, lock request time out period exceeded. Your code can catch it and try again later.

A Database Option Changes the Default

A reader asked why a plain SELECT didn’t wait. A database option called READ_COMMITTED_SNAPSHOT is the usual reason. When it’s on, a plain SELECT reads the last committed version of the row instead of waiting. Check it with this query.

SELECT is_read_committed_snapshot_on FROM sys.databases WHERE name = DB_NAME();

The query returns 0 for a database with the option off and 1 for a database with it on. In this test I turned it on in my scratch database and ran the default reader again. It returned 24.00 at once, the last committed price. No dirty read, and no waiting. Switch it on only after you understand the extra work in tempdb.

Clean Up

Remove the sample table when you finish.

DROP TABLE Pots;

What to Remember

The default read waits for committed data. READUNCOMMITTED and NOLOCK read dirty data without waiting. READPAST skips locked rows, and NOWAIT fails with an error. Pick the one that matches the question: exact numbers need the default, and rough trends can use NOLOCK.

To go further, read NOLOCK: Why It Counts Some Rows Twice and Misses Others, Difference Between NOLOCK and NOWAIT Hints and The NOLOCK Question: Notes from the Field #117.

A table hint is not a speed trick, it is a decision about the version of the truth you accept.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.