SQLServer CASE expressions - short circuit evaluation?

Question

I am trying to optimize a query that does something like this:

SELECT
   ...
   CASE WHEN (condition) THEN (expensive function call #1)
   ELSE (expensive function call #2) 
   END
...

The query plan shows that for even in cases where 100% of rows satisfy the WHEN clause, a substantial portion of the time is spent in calls to the result in the ELSE branch.

The only way I can make sense of this is to presume that SQLServer is evaluating both results, then only selecting one based on evaluation of the WHEN condition, but I can't find any definitive reference as to whether or not the results of a CASE statement are evaluated before the conditionals. Can anyone please clarify or point me to a reference?

Answer 1

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.

Answer 2

SQL is a declarative language. You express in a query the desired result and the server is free to choose whatever means to deliver those results. As such the order of evaluation of SQL epxressions is not determined and OR and AND evaluation short circuit does not occur.

However for CASE the documentation actually states that the order of evaluation occurs in the order of declaration and the evaluation stops after the first condition is met:

• Evaluates input_expression, and then in the order specified, evaluates input_expression = when_expression for each WHEN clause.

• Returns the result_expression of the first input_expression = when_expression that evaluates to TRUE.

That means that if you see the expression in the FALSE branch evaluated, your CASE condition is incorrect and sometimes evaluates to FALSE or UNKNOWN. Make sure that tri-values logic of SQL is taken into account (ie. you account for NULLs). Also make sure the data in the tables is the one you expect (ie. the condition really evaluates to FALSE 100% of the cases).