all_columns ) AS y GROUP BY x - Query B: additional ELSE clause: SELECT x, COUNT ( * ) FROM ( SELECT x = CASE ABS ( CHECKSUM ( NEWID ( ) ) %3 ) WHEN 0 THEN '0' WHEN 1 THEN '1' WHEN 2 THEN '2' ELSE '2' END FROM sys. Query A: expression referenced directly in CASE no ELSE: SELECT x, COUNT ( * ) FROM ( SELECT x = CASE ABS ( CHECKSUM ( NEWID ( ) ) %3 ) WHEN 0 THEN '0' WHEN 1 THEN '1' WHEN 2 THEN '2' END FROM sys. I often see people writing a simple CASE expression, like this: So, when aggregates or non-native services like Full-Text Search are involved, please do not make any assumptions about short circuiting in a CASE expression. I don't have a repro handy, but I do believe him, and I don't think we've unearthed all of the edge cases where this may occur. On that item, Paul White commented that he also observed something similar using the new LAG() function introduced in SQL Server 2012. For example, Connect #780132 : FREETEXT() does not honor order of evaluation in CASE statements (no aggregates involved) shows that, well, CASE evaluation order is not guaranteed to be left-to-right when using certain full-text functions either. This behavior can yield itself in some other, less obvious scenarios, too. I reported the bug in Connect #690017 : CASE / COALESCE won't always evaluate in textual order it was swiftly closed as "By Design." Paul White ( blog | subsequently filed Connect #691535 : Aggregates Don't Follow the Semantics Of CASE, and it was closed as "Fixed." The fix, in this case, was clarification in the Books Online article namely, the snippet I copied above. I was first made aware of this specific scenario in a conversation on a private e-mail distribution list by Itzik Ben-Gan ( who in turn was initially notified by Jaime Lafargue. There are trivial workarounds (such as ELSE (SELECT MIN(1/0)) END), but this comes as a real surprise to many who haven't memorized the above sentences from Books Online.
When people think of CASE as a statement, it leads to experiments in code shortening like this: I find this mildly annoying (like row/record and column/field) and, while it's mostly semantics, but there is an important distinction between an expression and a statement: an expression returns a result. Likely not important to most people, and perhaps this is just my pedantic side, but a lot of people call it a CASE statement – including Microsoft, whose documentation uses statement and expression interchangeably at times. The return expression is always a single value, and the output data type is determined by data type precedence.Īs I said, the CASE expression is often misunderstood here are some examples: CASE is an expression, not a statement