Sqlserver Case Expressions - Short Circuit Evaluation

SQLServer CASE expressions - short circuit evaluation?

Is that an actual or estimated plan? Sql Server builds plans based on what it expects to do based on collected statistics, and that doesn't always jibe with what specific conditions you send it for one instance of a query run.

CASE expression that does NOT have short-circuit evaluation?

If you are indeed just adding 1 to counting_column for each condition that is met, you can use a chain of n CASE statements and add the results together. Each individual CASE supplies a 0 or 1, all of which are added together and added to the current value of counting_column.

UPDATE [table] SET [counting_column] =
(
  [counting_column] + 
  (CASE when [column1] IS NULL then 1 ELSE 0 END) +
  (CASE when [column2] IS NULL then 1 ELSE 0 END) +
    ...
    ...
  (CASE when [column30] IS NULL then 1 ELSE 0 END) +
  (CASE when [column31] IS NULL then 1 ELSE 0 END)
)

Note that I have changed the = NULL to IS NULL. NULL is a special non-value that cannot be compared with an equality =.

I'll also point out that although [] enclosed identifiers are ok in SQLite, this is mostly for compatibility with T-SQL. The more standard approach is to use double-quotes as in UPDATE "table" SET "counting_column" = ...

Is the SQL WHERE clause short-circuit evaluated?

ANSI SQL Draft 2003 5WD-01-Framework-2003-09.pdf

6.3.3.3 Rule evaluation order

[...]

Where the precedence is not determined by the Formats or by
parentheses, effective evaluation of expressions is generally
performed from left to right. However, it is
implementation-dependent whether expressions are actually evaluated left to right, particularly when operands or operators might
cause conditions to be raised or if the results of the expressions
can be determined without completely evaluating all parts of the
expression.

Does SQL short-circuit the evaluation of an OR in the WHERE CLAUSE?

Whether or not SQL Server short circuits your particular expression isn't relevant here, it will generate the correct result either way. In this case, short circuiting is merely an optimization.

And as for optimizations, it's up to how your index is built. These are (ideally) index seeks.

Does SQL Server short-circuit IF statements?

Even if it appears to work, it should not be relied upon. The CASE statement is the only thing that the documentation states as being short-circuiting, but even that isn't (or at least wasn't) always the case (hee hee). Here is one bug that was fortunately fixed as of SQL Server 2012 (see the comments).

In addition to the rabbit hole (an interesting one, for sure) of links in comments from the comment posted by @Martin on the question, you should also check out this article:

Understanding T-SQL Expression Short-Circuiting

and the discussion forum related to that article.

Oracle CASE WHEN Short-Circuit Return Expressions

Oracle does process the CASE expressions in order. Unfortunately, because the aggregation, it also needs to evaluate all the aggregation expressions before it gets around to the CASE. And that is where the error is occurring. Note: I'm not sure where this is documented for Oracle, but I've seen the problem in other databases.

The solution is to use another CASE expression to avoid errors:

WITH tbl AS (
      SELECT 0 as val, 'avg' as calc_type FROM DUAL
      UNION ALL
      SELECT 1 as val, 'geom_mean' FROM DUAL
     )
SELECT CASE WHEN max(calc_type) = 'avg' THEN AVG(val)
       ELSE EXP(AVG(LN(CASE WHEN val > 0 THEN val END))) END as res
FROM tbl
GROUP BY calc_type

---

Related Topics