Does SQL Server read all of a COALESCE function even if the first argument is not NULL?

Nope. 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.

1 Duhh why didn't I think of this! I was trying to think of a performance test which would be able to help me find out, I didn't think of just causing an exception with the second argument! Cheers @JNK – Curt Sep 19 at 15:16 2 @Curt - Happy to help.

SELECT 1/0 is my goto when checking if something gets evaluated or not. – JNK Sep 19 at 15:18 I think it'll be mine too from now on! – Curt Sep 19 at 15:33 1 @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 at 18:02 Other interesting behavior @SQLKiwi pointed me to: SELECT COALESCE((SELECT CASE WHEN RAND().

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.

I'd be glad to discuss – Adrian Sep 19 at 18:26 I think folks are DVing because of the points that Aaron brought up. I just got -2 myself. – JNK Sep 19 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 at 14:09 @onedaywhen Yes, you are right, it was bad writing indeed. How about now? – Adrian Sep 20 at 14:16.

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.

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 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 at 15:25 @Aaron - This is the only way of safely dealing safely with EAV type columns of mixed datatypes.

I'm sure it's guaranteed somewhere. Will try and find it (I know that SQLKiwi has an apparent counter example with LOG(0) but that is a constant folding issue rather than a CASE issue) – Martin Smith Sep 19 at 15:27 @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 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 at 15:36.

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.

1 Thanks @Martin. I knew I could get you to come around. :-) – Aaron Bertrand Sep 19 at 17:51 1 This is always from parameters though, correct?

– JNK Sep 19 at 18:04 3 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 – SQLkiwi Sep 19 at 18:25 2 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 at 18:32 1 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; – SQLkiwi Sep 19 at 18:45.

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() 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.

1 +1 - Great background info, and thanks for linking to the related connect item – JNK Sep 19 at 18:47.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.