Paul White NZ (3/31/2010)
Hugo Kornelis (3/31/2010)
I would agree that, depending on transaction isolation level and @@TRANCOUNT, the results of the three statements might be different on a busy server. But for your code, with the single SELECT, I would consider anything but three equal values a bug.I don't see why. The COUNT done at the default isolation level could easily read the same values twice or skip values completely.
No. That would be possible if you use NOLOCK or DIRTY_READ, but not at the default level (READ_COMMITTED).
Three different scans could easily, in general, produce three different results. The fact that it is a single statement doesn't change that.
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! 😉