Paul White NZ (4/1/2010)
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.
No, that won't happen. This is because of how a heap scan treats forwarding pointers. The rules are simple:
1. when a forwarding pointer is encountered, the pointer is followed IMMEDIATELY and the forwarded row is then read
2. when a forwarded row is encountered, it is skipped (becuase it either has already been read from the forwarding pointer, or will be read if the page holding the forwarding pointer still has to be scanned).
So if a row that has already been read is forwarded to a page that has not yet been read, it won't be read again - it's marked as a forwarded row and will be skipped. And in the reverse case, if a row that has not yet been read is forwarded to a page that has already been processed, it will be read once the forwarding pointer is encountered.
And so, I finally get a question to an answer that I have asked myself since 2006. I finally know why Microsoft chose to implement "the table scan from hell".
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?
Yes, we can. Didn't I already say so in one of my previous posts?
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 the link - anything Craig Freedman writes is always worth reading!