Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm using a T-SQL COALESCE function where the first argument will not be null on about 95% of the times it is ran. If the first argument is NULL, the second argument is quite a lengthy process:

SELECT COALESCE(c.FirstName
                ,(SELECT TOP 1 b.FirstName
                  FROM TableA a 
                  JOIN TableB b ON .....)
                )

If, for example, c.FirstName = 'John', would SQL Server still run the sub-query?

I know with the VB.NET IIF() function, if the second argument is True, the code still reads the third argument (even though it won't be used).

share|improve this question
    
"Does SQL Server read all of a COALESCE function" -- it will certainly parse all of it. p.s. in Access Database Engine SQL, the IIF() function can short circuit. –  onedaywhen Feb 14 '12 at 8:39
add comment

migrated from stackoverflow.com Feb 13 '12 at 22:07

This question came from our site for professional and enthusiast programmers.

8 Answers

up vote 50 down vote accepted

Nope. Here's a simple test:

SELECT COALESCE(1, (SELECT 1/0)) -- runs fine
SELECT COALESCE(NULL, (SELECT 1/0)) -- throws error

If the second condition is evaluated, an exception is thrown for divide-by-zero.

Per the MSDN Documentation this is related to how COALESCE is viewed by the interpreter - it's just an easy way to write a CASE statement.

CASE is well known to be one of the only functions in SQL Server that (mostly) reliably short circuits.

There are some exceptions when comparing to scalar variables and aggregations as shown by Aaron Bertrand in another answer here (and this would apply both to CASE and COALESCE):

DECLARE @i INT = 1;
SELECT CASE WHEN @i = 1 THEN 1 ELSE MIN(1/0) END;

will generate a division by zero error.

This should be considered a bug, and as a rule COALESCE will parse from left to right.

share|improve this answer
4  
@JNK please see my answer to see a very simple case where this does not hold true (my concern is that there are even more, yet-undiscovered scenarios - making it hard to agree that CASE always evaluates left-to-right and always short circuits). –  Aaron Bertrand Sep 19 '11 at 18:02
2  
Other interesting behavior @SQLKiwi pointed me to: SELECT COALESCE((SELECT CASE WHEN RAND() <= 0.5 THEN 1 END), 1); - repeat multiple times. You will get NULL sometimes. Try again with ISNULL - you'll never get NULL... –  Aaron Bertrand Sep 19 '11 at 18:33
3  
    
@Martin yes I believe so. But not behavior that most users would find intuitive unless they had heard of (or been bitten by) that issue. –  Aaron Bertrand Sep 19 '11 at 18:43
add comment

No, it would not. It would only run when c.FirstName is NULL.

However, you should try it yourself. Experiment. You said your subquery is lengthy. Benchmark. Draw your own conclusions on this.

@Aaron answer on the sub-query being run is more complete.

However, I still think you should rework your query and use LEFT JOIN. Most of the time, sub queries can be removed by reworking your query to use LEFT JOINs.

The problem with using sub queries is that your overall statement will run slower because the sub query is ran for each row in main query's result set.

share|improve this answer
1  
@Downvoter, would you care to comment? I'd be glad to discuss –  Adrian Sep 19 '11 at 18:26
    
I think folks are DVing because of the points that Aaron brought up. I just got -2 myself. –  JNK Sep 19 '11 at 18:29
    
I considered downvoting for your sloppy explanation, "sub queries... are slower because you execute them for each returned row in main query". –  onedaywhen Sep 20 '11 at 14:09
    
@onedaywhen Yes, you are right, it was bad writing indeed. How about now? –  Adrian Sep 20 '11 at 14:16
    
@Adrian it is still not right. Look at the execution plan and you will see that subqueries are often converted quite smartly to JOINs. It is a mere thought-experiment error to assume that the entire subquery has to be run over and over for each row, though this can effectively happen if a nested loop join with a scan is chosen. –  ErikE Aug 3 '12 at 21:40
add comment

Neither COALESCE nor CASE are guaranteed to short-circuit in SQl Server. In fact, it can be demonstrated that they do not.

My example is pulled from here. Turn on Include Actual Execution Plan, and run this query:

DECLARE @test INT
SET @test = 1
SELECT COALESCE(@test, (SELECT COUNT(*) FROM sysobjects))

Then look at the execution plan.

Update: My example is flawed, see @Aaron's answer for a working example.

share|improve this answer
    
This answer is incorrect. Look at SET STATISTICS IO ON; you will verify that despite appearing in the plan it is never accessed. Also see "number of executions" for operators on that side of the plan is zero. –  Martin Smith Sep 19 '11 at 15:23
    
@Martin but to be fair and to play devil's advocate a bit, how confident are you that short-circuiting will always work, in all cases, in all future versions/service packs/trace flags, and with all possible queries and plan shapes even in current versions? I've been very careful to not relay on short-circuiting behavior because if it's not in a written guarantee I treat it just like relying on order without ORDER BY. –  Aaron Bertrand Sep 19 '11 at 15:25
    
@Aaron - See BOL quote in this connect item (although admittedly the quote is a lot less explicit than the OP there seems to suggest) –  Martin Smith Sep 19 '11 at 15:31
    
@Martin so it seems like you trust Books Online even though the Connect item doesn't indicate what builds of SQL Server this issue has been fixed, and whether those fixes are publicly available yet? Still seems like a lot of faith to me... –  Aaron Bertrand Sep 19 '11 at 15:36
    
@Aaron- The issue was a compile time one where it blew up trying to fold LOG(0) into a constant. Nothing to do with CASE at all really. –  Martin Smith Sep 19 '11 at 15:37
show 8 more comments

How about this one?

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.

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:

http://connect.microsoft.com/SQLServer/feedback/details/780132/

share|improve this answer
1  
I have to +1 this! –  Martin Smith Sep 19 '11 at 17:50
1  
Thanks @Martin. I knew I could get you to come around. :-) –  Aaron Bertrand Sep 19 '11 at 17:51
5  
SELECT CASE V.v WHEN 1 THEN NULL ELSE MIN(V.v/0) END FROM (VALUES (1),(1),(1)) AS V(v) GROUP BY V.v –  Paul White Sep 19 '11 at 18:25
3  
Is this not a datatype precedence issue? That is, MIN ust be evaluated to determine the datatype of the resulting expression? What about a simple 1/0 ? –  gbn Sep 19 '11 at 18:32
2  
JNK: CREATE TABLE #V (v INT NULL); INSERT #v (v) VALUES (1); SELECT CASE V.v WHEN 1 THEN NULL ELSE MIN(V.v/0) END FROM #V AS V GROUP BY V.v; –  Paul White Sep 19 '11 at 18:45
show 10 more comments

My view on this is that the documentation makes it reasonably clear that the intention is that CASE should short-circuit. As Aaron mentions, there have been several cases (ha!) where this has been shown to not always be true.

So far, all these have been acknowledged as bugs and fixed - though not necessarily in a version of SQL Server you can buy and patch up today (the constant-folding bug has not yet made it to a Cumulative Update AFAIK). The newest potential bug - originally reported by Itzik Ben-Gan - has yet to be investigated (either Aaron or I will add it to Connect shortly).

Related to the original question, there are other issues with CASE (and therefore COALESCE) where side-effecting functions or sub-queries are used. Consider:

SELECT COALESCE((SELECT CASE WHEN RAND() <= 0.5 THEN 999 END), 999);
SELECT ISNULL((SELECT CASE WHEN RAND() <= 0.5 THEN 999 END), 999);

The COALESCE form often returns NULL, more details at https://connect.microsoft.com/SQLServer/feedback/details/546437/coalesce-subquery-1-may-return-null

The demonstrated issues with optimizer transforms and common-expression-tracking mean that it is impossible to guarantee that CASE will short-circuit in all circumstances. I can conceive of cases where it might not even be possible to predict the behaviour by inspecting the public show plan output, though I don't have a repro for that today.

In summary, I think you can be reasonably confident that CASE will short-circuit in general (particularly if a reasonably-skilled person inspects the execution plan, and that execution plan is 'enforced' with a plan guide or hints) but if you need an absolute guarantee, you have to write SQL that does not include the expression at all.

Not a hugely satisfactory state of affairs, I guess.

share|improve this answer
3  
+1 - Great background info, and thanks for linking to the related connect item –  JNK Sep 19 '11 at 18:47
    
    
I had one with LAG a while ago too. Was too depressed by it to save a repro unfortunately. I worked around it by moving the CASE statement :( –  Paul White Feb 28 '13 at 0:27
add comment

I just wanted to mention a strategy you may not have considered. It may not be a match here, but it does come in handy sometimes. See if this modification gives you any better performance:

SELECT COALESCE(c.FirstName
            ,(SELECT TOP 1 b.FirstName
              FROM TableA a 
              JOIN TableB b ON .....
              WHERE C.FirstName IS NULL) -- this is the changed part
            )

Another way to do it could be this (basically equivalent, but allows you to access more columns from the other query if necessary):

SELECT COALESCE(c.FirstName, x.FirstName)
FROM
   TableC c
   OUTER APPLY (
      SELECT TOP 1 b.FirstName
      FROM
         TableA a 
         JOIN TableB b ON ...
      WHERE
         c.FirstName IS NULL -- the important part
   ) x

Basically this is a technique of "hard" joining tables but including the condition on when any rows at all should be JOINed. In my experience this has really helped execution plans at times.

share|improve this answer
add comment

I've come across another case where CASE / COALESCE do not short circuit. The following TVF will raise a PK violation if passed 1 as a parameter.

CREATE FUNCTION F (@P INT)
RETURNS @T TABLE (
  C INT PRIMARY KEY)
AS
  BEGIN
      INSERT INTO @T
      VALUES      (1),
                  (@P)

      RETURN
  END

If called as follows

DECLARE @Number INT = 1

SELECT COALESCE(@Number, (SELECT number
                          FROM   master..spt_values
                          WHERE  type = 'P'
                                 AND number = @Number), 
                         (SELECT TOP (1)  C
                          FROM   F(@Number))) 

Or as

DECLARE @Number INT = 1

SELECT CASE
         WHEN @Number = 1 THEN @Number
         ELSE (SELECT TOP (1) C
               FROM   F(@Number))
       END 

Both give the result

Violation of PRIMARY KEY constraint 'PK_F_3BD019A800551192'. Cannot insert duplicate key in object 'dbo.@T'. The duplicate key value is (1).

showing that the SELECT (or at least the table variable population) is still carried out and raises an error even though that branch of the statement should never be reached. The plan for the COALESCE version is below.

Plan

This rewrite of the query appears to avoid the issue

SELECT COALESCE(Number, (SELECT number
                          FROM   master..spt_values
                          WHERE  type = 'P'
                                 AND number = Number), 
                         (SELECT TOP (1)  C
                          FROM   F(Number))) 
FROM (VALUES(1)) V(Number)   

Which gives plan

Plan2

share|improve this answer
add comment

Another example

CREATE TABLE T1 (C INT PRIMARY KEY)

CREATE TABLE T2 (C INT PRIMARY KEY)

INSERT INTO T1 
OUTPUT inserted.* INTO T2
VALUES (1),(2),(3);

The query

SET STATISTICS IO ON;

SELECT T1.C,
       COALESCE(T1.C , CASE WHEN EXISTS (SELECT * FROM T2 WHERE T2.C = T1.C)  THEN -1 END)
FROM T1
OPTION (LOOP JOIN)

Shows no reads against T2 at all.

The seek of T2 is under a pass through predicate and the operator is never executed. But

SELECT T1.C,
       COALESCE(T1.C , CASE WHEN EXISTS (SELECT * FROM T2 WHERE T2.C = T1.C)  THEN -1 END)
FROM T1
OPTION (MERGE JOIN)

Does show that T2 is read. Even though no value from T2 is ever actually needed.

Of course this is not really surprising but I thought worth adding to the counter example repository if only because it raises the issue of what short circuiting even means in a set based declarative language.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.