Hey, no worries...I do not see this as arguing - it is a fascinating debate! It has also been a good 'refresher' for me, since I haven't looked at this issue in depth for over a year now. Late reply due to time zone issues.

I agree. I was mis-remembering the cause here. Page splitting is one mechanism that can cause problems at READ UNCOMMITTED, as you rightly point out. Please forgive my memory lapse there - page splits were not relevant to my point. It turns out that the whole issue is partly described in Books Online: Concurrency Effects. The relevant extracts are:

Page Splits

"When you are using READ UNCOMMITTED, if your query reads rows using an allocation order scan (using IAM pages), you might miss rows if another transaction is causing a page split. This cannot occur when you are using read committed because a table lock is held during a page split and does not happen if the table does not have a clustered index, because updates do not cause page splits."

Missed/duplicate reads at higher isolation levels

"Transactions that are running at the READ COMMITTED level do issue shared locks, but the row or page locks are released after the row is read. In either case, when you are scanning an index, if another user changes the index key column of the row during your read, the row might appear again if the key change moved the row to a position ahead of your scan. Similarly, the row might not appear if the key change moved the row to a position in the index that you had already read. To avoid this, use the SERIALIZABLE or HOLDLOCK hint, or row versioning."

Craig Freedman has an excellent explanation of the latter effect here: http://blogs.msdn.com/craigfr/archive/2007/04/25/read-committed-isolation-level.aspx for anyone reading along that is interested.

What neither of those state explicitly, is that the same effect occurs at REPEATABLE READ - it is easy to verify that though, using a test based on Craig's reproduction script.

On the subject of IAM-driven scans. You are, of course, absolutely right about the conditions for an IAM scan on a table with a clustered index (ordered:false, plus NOLOCK or TABLOCK), but it occurs to me that heaps introduce an extra possibility: A row that has been read by an IAM scan can be updated by a concurrent process, such that the row moves (leaving behind a forwarding pointer) to a later page that the original scan has not reached yet. I imagine it is possible to miss a row via the same mechanism, in reverse, but I haven't tested either of these. I just mention it as a point of interest, really.

I knew I had seen the point proven by Alex at some stage - it is a pity I originally referenced the wrong article, and mis-remembered the cause (page splits). I have also just realised that this covers my points above, but I am leaving them in, because it seems a shame to delete all that nice formatting and references 😉

Anyhow...my flawed powers of recall aside, can we now agree that rows can be read twice or not at all under READ COMMITTED and REPEATABLE READ - regardless of heaps/ordered scans? I do apologise that it took so many posts to get the causes nailed down, but the discussion seems to have been of interest to some, so it's not all bad news 🙂

One final thing. We started off talking about common sub-expressions being evaluated more than once by CASE. That man Craig Freedman has another entry that describes the issue, and presents a work-around: http://blogs.msdn.com/craigfr/archive/2008/06/27/subqueries-in-between-and-case-statements.aspx. I don't think it helps with COALESCE directly, but it is very interesting nonetheless.

Thanks for a most enjoyable discussion!

Paul