Hugo Kornelis (3/31/2010)
No. That would be possible if you use NOLOCK or READ_UNCOMMITTED, but not at the default level (READ_COMMITTED).
I know you know this! It has been blogged many times, but here's the entry from your 'blog-colleague' Alex K:
Double/missed reads are definitely possible and even quite likely even at REPEATABLE READ.
Except that I believe that when it is a single statement, there should not be three different scans. Especially BECAUSE that could return inconsistent results. But I think we'll just have to agree to disagree on this one! 😉
Ah right, I see your point. You think the three identical sub-queries should be 'collapsed' into one execution by SQL Server. Sounds distinctly dodgy to me 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi