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. (Ignore the complications introduced by my use of system tables for the sake of an easy example). Three different scans could easily, in general, produce three different results. The fact that it is a single statement doesn't change that.
And in situations such as the scenario that brought us here, where the actual subquery occurs only once in the submitted query and it's SQL Server itself that expands it, I'd consider it even more buggy!
That I can see, but if we accept that the 'compressed' CASE statement is purely a syntactical convenience, and that it is merely shorthand for the searched expression...then clearly the subqueries must be evaluated x number of times, due to the fact that data access makes the expression non-deterministic.
I agree that this whole area is a very odd duck though - much like the multiply-referenced CTE issue.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi