Hugo Kornelis (3/31/2010)
That is caused by a related and similar reason. This "simple" CASE is also defined in ANSI in terms of the "searched" CASE (and apparently implemented in this way):
Yep, you can see that by examining the query plan produced - the CASE is always expanded to the searched form.
I disagree. If the query includes some non-deterministic code (such as NEWID()), there might be room for argument. But if the query in itself is deterministic, than the results should be deterministic.
I do see your point, but I'm not sure I agree. Once the decision has been taken to expand the CASE, the sub-queries must be re-evaluated. The fact that data access is involved makes the result non-deterministic - the result depends on the state of the database.
We would not expect the following code to be 'collapsed' into a single sub-query execution:
SELECT result1 = (SELECT COUNT(*) FROM sys.objects),
result2 = (SELECT COUNT(*) FROM sys.objects),
result3 = (SELECT COUNT(*) FROM sys.objects)
A query is supposed to behave as if it is evaluated at a single moment in time; implementation choices should never influence the results.
I think this is the crux of the matter. You are only guaranteed this sort of behaviour when running at SERIALIZABLE or one of the row-versioning isolation levels, as I mentioned before. (Also see the example above)
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi