A row can vanish from your report while it is sitting safely in the table the entire time. Nobody deleted it. Nothing rolled back. The read simply walked past it.
This week’s episode of SQL in Sixty Seconds is about that moment, and about the ten minutes of panic that usually follow it. Dex runs the nightly count. The number is short. Somebody says the word deleted, and the evening is gone.
Watch It First. It Really Is Sixty Seconds.
Watch it before you read the rest, because the whole point lands better in sixty seconds than in sixteen hundred words. I will wait here.
Reading this in email or a feed reader where the player did not load? Here is the direct link: NOLOCK Can Miss Rows Without Deleting Them, SQL in Sixty Seconds 212. Send it to whoever put NOLOCK on the reconciliation report. Gently. They meant well.
What Actually Happened
The nightly reconciliation has run without complaint for two years. It counts orders. It has NOLOCK on it, because somebody added that years ago when the report was blocking people, and it worked, and nobody touched it again.
SELECT COUNT_BIG(*) AS OrderCount
FROM dbo.Orders WITH (NOLOCK);Tonight it comes back one short. One order, gone. Owen has already found which customer it belongs to, because Owen is thorough in the least helpful way available.
Here is the part worth slowing down for. SQL Server did nothing wrong, and neither did anybody on the team. No DELETE ran. No transaction rolled back. The order was in the table before the count, during the count, and after it.
The count still came back short, and it will not appear in any error log, because as far as SQL Server is concerned nothing went wrong.
The Row Was Never Gone
Mia asks the only question that matters, and it takes four seconds to answer. Run the same count without the hint.
SELECT COUNT_BIG(*) AS OrderCount
FROM dbo.Orders;The order is there. It was always there. What Dex had was not a missing row, it was a wrong number, and those two things feel identical at eleven at night while somebody is asking you about a customer by name.
This is why I keep saying NOLOCK problems are expensive even when no data is lost. You spend the evening proving a deletion that never happened.
Why A Scan Can Walk Straight Past A Row
I wrote the long version of this a few days ago, with animations, in NOLOCK: Why It Counts Some Rows Twice and Misses Others. Here is the short version.
For some scans, SQL Server reads data pages in allocation order, roughly the order the pages sit in the file, rather than following the rows by key. That can be faster when the query does not need ordered output.
While that scan is walking forward, other people are still writing. Somebody updates a row and it gets longer. If its page has no room for the bigger version, the page splits and rows move elsewhere in the file. There is a second way a row moves that is just as ordinary: update the column the index is sorted by, and the row cannot stay where it is.
So a row can move backward, into a page the scan has already read and will never revisit. That row is committed, valid, and present, and your count never sees it. Move the other way instead, into a page ahead of the scan, and the same row gets counted twice.
Notice what is missing from that description. Nobody inserted. Nobody deleted. Nothing rolled back. Every transaction involved committed successfully.
NOLOCK is a shorthand for the READ UNCOMMITTED isolation level, and this is the part of that bargain nobody reads. Dirty data is only half of what you agreed to. The other half is that the scan gives up the guarantee that it will see each row exactly once.
What I Measured, And What I Did Not
I want to be precise here, because this is the kind of claim that gets repeated for twenty years without anybody running it.
On SQL Server 2025 CU7, I built a table of exactly 100,000 rows, then ran a NOLOCK count in a loop while two other sessions churned the table with updates that force pages to split. Out of 380 counts, 372 came back correct. Eight came back wrong, ranging from 100,007 to 100,101.
Every wrong answer in that particular run was too high, not too low. I did not capture a short count in that pass. The mechanism is the same one running in the other direction, and Microsoft documents both, but I am not going to tell you I measured something I did not measure.
The other number from that run is the one I think about more. The index started 97 percent full across 9,092 pages. After the churn pass it was 11 percent full across 81,418 pages. The same 100,000 rows, spread over nine times as many pages. That is what heavy page splitting does to a table while you are reading it.
Two things follow. Wrong answers are rare, which is exactly why nobody catches them. And they need real write activity to appear, which is why the report that has worked for two years starts lying in the month you get busy.
How To Get A Count You Can Reconcile
If a number has to reconcile, do not read it with NOLOCK. That is the whole recommendation. What you use instead depends on why NOLOCK got added in the first place.
If you only need an approximate count, do not scan the table at all. The engine already tracks this.
SELECT SUM(ps.row_count) AS ApproximateRows
FROM sys.dm_db_partition_stats AS ps
WHERE ps.object_id = OBJECT_ID('dbo.Orders')
AND ps.index_id IN (0, 1);This reads metadata rather than data, so it does not block and does not scan. Microsoft describes these row counts as approximate, so use it for a dashboard tile or a capacity check, not for a figure somebody signs.
If you need a number that was actually true at one moment, and you added NOLOCK to stop blocking readers, snapshot isolation is the tool that was built for this. Readers get a consistent point-in-time view without taking shared locks, so they neither block writers nor get blocked by them.
-- One time, and test it first. Row versions are kept in tempdb,
-- or in the persistent version store when ADR is enabled.
ALTER DATABASE Sales
SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;Be honest about that one. WITH ROLLBACK IMMEDIATE terminates other sessions in the database to get the exclusive access it needs, so it is a scheduled change, not a Tuesday afternoon change. It also changes the behavior of every read committed query in the database, and it needs version store space. Test it somewhere that is not production.
If you would rather not change database-wide behavior, ask for the consistent view one transaction at a time, after enabling it on the database.
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
SELECT COUNT_BIG(*) AS OrderCount
FROM dbo.Orders;
COMMIT TRANSACTION;Let The Script Show You The Drift
Arguing about this in the abstract goes nowhere. Measuring it on your own busiest table ends the argument in about a minute. Run this while the system is genuinely busy, because on a quiet table it will show you nothing and prove nothing.
DECLARE @WithHint bigint,
@WithoutHint bigint;
SELECT @WithHint = COUNT_BIG(*)
FROM dbo.Orders WITH (NOLOCK);
SELECT @WithoutHint = COUNT_BIG(*)
FROM dbo.Orders;
SELECT @WithHint AS CountWithNolock,
@WithoutHint AS CountWithoutNolock,
@WithHint - @WithoutHint AS Drift;One caveat so nobody misreads their own result. These two counts run at different moments, so on a table taking inserts and deletes a small difference is simply the clock, not the hint. Run it on a table whose row count is stable while updates are heavy, or repeat it enough times that the pattern separates from the noise. A drift that lands on both sides of zero is the signature you are looking for.
If the drift is always zero on your workload, that is a real answer too. It means your tables are not splitting pages under your scans today. It does not mean the hint is safe forever, only that you are not currently paying for it.
Four Habits That Keep Your Numbers Honest
Decide whether the number has to reconcile. A row count on a monitoring screen and a row count in a financial close are not the same request. NOLOCK is a defensible choice for the first one. It has no business anywhere near the second.
Stop adding it by reflex. Most NOLOCK in production was added during one bad afternoon years ago and never reviewed. Search your codebase for it, and for SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED, which does the same thing to every table in the batch and is easier to miss in a review.
Fix the blocking instead. NOLOCK is usually treating a symptom. A missing index, a scan that should be a seek, or a transaction held open across a user prompt will keep causing blocking no matter which hint you paste on the reader.
When a number looks wrong, check how it was read before you check whether data was lost. That is the habit this episode is really about. Dex spent his evening looking for a deletion. The answer was in the query, not in the table.
What Should Dex Break Next?
Dex and Mia are nowhere near out of material. DELETE with no WHERE. A truncate on the wrong table. An index rebuild that starts at the worst possible hour. Or the classic that gets everybody at least once, a NULL comparison that quietly returns nothing at all.
Drop your vote in the comments on the video. The whole SQL in Sixty Seconds playlist is on the channel.
For a closer look at how rows move ahead of or behind a scan, see my earlier article with animations: NOLOCK: Why It Counts Some Rows Twice and Misses Others.
Owen has stopped looking for the missing order. He is now telling everyone he found it, which is not strictly what happened, but I am letting him have this one.
Before you go looking for deleted data, check how the number was read.
NOLOCK did not lose your row, it just agreed not to look for it carefully.
Reference: Pinal Dave (https://blog.sqlauthority.com/), NOLOCK and Read Uncommitted, X
