Paul White NZ (3/31/2010)
Hugo Kornelis (3/31/2010)
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)
Actually, I would expect exactly that to happen. This is a single query, supposed to return results as if it were executed at a single moment in time. Transaction isolation levels govern what happens if multiple statements are executed in succession. So if I change your code to
SELECT @result1 = (SELECT COUNT(*) FROM sys.objects);
SELECT @result2 = (SELECT COUNT(*) FROM sys.objects);
SELECT @result3 = (SELECT COUNT(*) FROM sys.objects);
then 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.
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!