How about this one - as reported to me by Itzik Ben-Gan, who was told about it by Jaime Lafargue?
DECLARE @i INT = 1;
SELECT CASE WHEN @i = 1 THEN 1 ELSE MIN(1/0) END;
Result:
Msg 8134, Level 16, State 1, Line 2
Divide by zero error encountered.
There are trivial workarounds of course, but the point is still that CASE
does not always guarantee left-to-right evaluation / short-circuiting. I reported the bug here and it was closed as "by design." Paul White subsequently filed this Connect item, and it was closed as Fixed. Not because it was fixed per se, but because they updated Books Online with a more accurate description of the scenario where aggregates can change the evaluation order of a CASE
expression. I recently blogged more about this here.
EDIT just an addendum, while I agree that these are edge cases, that most of the time you can rely on left-to-right evaluation and short-circuiting, and that these are bugs that contradict the documentation and will probably eventually be fixed (this isn't definite - see the follow-up conversation on Bart Duncan's blog post to see why), I have to disagree when folks say that something is always true even if there is a single edge case that disproves it. If Itzik and others can find solitary bugs like this, it makes it at least in the realm of possibility that there are other bugs as well. And since we don't know the rest of the OP's query, we can't say for certain that he will rely on this short-circuiting but end up being bitten by it. So to me, the safer answer is:
While you can usually rely on CASE
to evaluate left-to-right and short-circuit, as described in the documentation, it is not accurate to say that you can always do so. There are two demonstrated cases on this page where it is not true, and neither bug has been fixed in any publicly available version of SQL Server.
EDIT here is another case (I need to stop doing that) where a CASE
expression does not evaluate in the order you would expect, even though no aggregates are involved.