COALESCE - guaranteed to short-circuit?

I just had a look at the linked article and can confirm short circuiting can fail for both COALESCE and ISNULL It seems to fail if you have any sub-query involved, but works fine for scalar functions and hard coded values Eg DECLARE @test INT SET @test = 1 PRINT 'test2' SET @test = COALESCE(@test, (SELECT COUNT(*) FROM sysobjects)) SELECT 'test2', @test -- OUCH a scan through sysobjects COALESCE is implemented according to the ANSI standard it is simply shorthand for a CASE statement. ISNULL is not part of the ANSI standard. Section 6.9 does not seem to require short circuiting explicitly but it does imply that the first true clause in the when statement should be returned Here is some proof that is works for scalar based functions (ran on sql 2005): CREATE FUNCTION dbo.

Evil ( ) RETURNS int AS BEGIN -- create an huge delay declare @c int select @c = count(*) from sysobjects a join sysobjects be on 1=1 join sysobjects c on 1=1 join sysobjects d on 1=1 join sysobjects e on 1=1 join sysobjects f on 1=1 return @c / 0 END go select dbo.evil() -- takes forever select ISNULL(1, dbo.evil()) -- very fast select COALESCE(1, dbo.evil()) -- very fast Here is some proof that the underlying implementation with CASE will execute sub queries DECLARE @test INT SET @test = 1 select case when @test is not null then @test when @test = 2 then (SELECT COUNT(*) FROM sysobjects) when 1=0 then (SELECT COUNT(*) FROM sysobjects) else (SELECT COUNT(*) FROM sysobjects) end -- OUCH 2 table scans. If 1=0 does not result in a table scan.

I just had a look at the linked article and can confirm short circuiting can fail for both COALESCE and ISNULL. It seems to fail if you have any sub-query involved, but works fine for scalar functions and hard coded values. Eg.

DECLARE @test INT SET @test = 1 PRINT 'test2' SET @test = COALESCE(@test, (SELECT COUNT(*) FROM sysobjects)) SELECT 'test2', @test -- OUCH a scan through sysobjects COALESCE is implemented according to the ANSI standard it is simply shorthand for a CASE statement. ISNULL is not part of the ANSI standard. Section 6.9 does not seem to require short circuiting explicitly but it does imply that the first true clause in the when statement should be returned.

Here is some proof that is works for scalar based functions (ran on sql 2005): CREATE FUNCTION dbo. Evil ( ) RETURNS int AS BEGIN -- create an huge delay declare @c int select @c = count(*) from sysobjects a join sysobjects be on 1=1 join sysobjects c on 1=1 join sysobjects d on 1=1 join sysobjects e on 1=1 join sysobjects f on 1=1 return @c / 0 END go select dbo.evil() -- takes forever select ISNULL(1, dbo.evil()) -- very fast select COALESCE(1, dbo.evil()) -- very fast Here is some proof that the underlying implementation with CASE will execute sub queries. DECLARE @test INT SET @test = 1 select case when @test is not null then @test when @test = 2 then (SELECT COUNT(*) FROM sysobjects) when 1=0 then (SELECT COUNT(*) FROM sysobjects) else (SELECT COUNT(*) FROM sysobjects) end -- OUCH 2 table scans.

If 1=0 does not result in a table scan.

Yes, it looks like COALESCE is totally equivalent to CASE, and short-circuits the same way, however, as you show, the behavior of CASE does not always short circuit, which IS really quite nasty. – Cade Roux Feb 3 '09 at 8:38 COALESCE does short-circuit correctly (even with subqueries) in 11g – Jeffrey Kemp Mar 7 '10 at 8:21 It does not do 2 table scans even though the plan shows 2 scans. This is easy to verify with SET STATISTICS IO ON or simply look at the "number of executions" in the execution plan properties.

There is an issue with COALESCE that does not occur with ISNULL though. – Martin Smith Sep 19 at 16:15.

The efficient way to guarantee short circuit in MS SQL Server is to use CASE. For the success WHEN clause, no others are evaluated. COALESCE can have issues In this instance, why have so many branches in the COALESCE/CASE constructs?

SELECT Numerator ,Denominator ,CASE WHEN Denominator = 0 THEN 0 END, ELSE Numerator / Denominator END AS TestCalc FROM Fractions.

Feb 3 '09 at 5:16 Yes, CASE can do subqueries but I0m not sure of the relevance to the OP's question. I've seen it used as a short circuit but I don't like it personally because of the table scans or increase in IO (as you demonstrated) – gbn Feb 3 '09 at 7:11.

I was also surprised to see that answer works! I'm not sure this behaviour is guaranteed. (but I have not been able to find an example that does not work!) 5 years of sql and I'm still surprised.

I also went ahead and did one more change : INSERT INTO #Fractions VALUES (0, 0) SELECT Numerator ,Denominator ,coalesce ( CASE WHEN Denominator = 0 THEN 0 ELSE NULL END, CASE WHEN Numerator 0 THEN Numerator / Denominator ELSE NULL END) AS TestCalc FROM #Fractions the result I got was : Numerator Denominator TestCalc 1 1 1 1 2 0.5 1 3 0.3333333333333335 1 0 0 2 0 0 3 0 0 0 0 0 Now I'm even more confused! For the case when num=0 and den=0 , how did I get testcalc as 0 (especially since I removed the 0 after the last case!).

That should fall into the first case. Over a decade of SQL Server, and I never considered COALESCE would short-ciruict, because it LOOKS like a function call. Obviously CASE does, and it seams like COALESCE is defined to function identically to CASE.

– Cade Roux Feb 3 '09 at 4:24 my bad ... of course it falls into the first case. It's now my life's mission to find a case where this does not work :) – Learning Feb 3 '09 at 4:29 @Learning, make sure you have a look at my expanded answer, it corrects some stuff. – Sam Saffron?

Feb 4 '09 at 0:37.

I just had a look at the linked article and can confirm short circuiting can fail for both COALESCE and ISNULL.

The efficient way to guarantee short circuit in MS SQL Server is to use CASE. For the success WHEN clause, no others are evaluated.

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.