StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I just had a look at the linked article and can confirm short circuiting can fail for both COALESCE and ISNULL.</p> <p>It seems to fail if you have any sub-query involved, but it works fine for scalar functions and hard coded values.</p> <p>For example,</p> <pre><code>DECLARE @test INT SET @test = 1 PRINT 'test2' SET @test = COALESCE(@test, (SELECT COUNT(*) FROM sysobjects)) SELECT 'test2', @test -- OUCH, a scan through sysobjects </code></pre> <p>COALESCE is implemented according to the <a href="http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt" rel="nofollow noreferrer">ANSI standard</a>. It is simply a 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 <code>when</code> statement should be returned.</p> <p>Here is some proof that is works for scalar based functions (I ran it on <a href="http://en.wikipedia.org/wiki/Microsoft_SQL_Server#SQL_Server_2005" rel="nofollow noreferrer">SQL Server 2005</a>):</p> <pre><code>CREATE FUNCTION dbo.evil ( ) RETURNS int AS BEGIN -- Create an huge delay declare @c int select @c = count(*) from sysobjects a join sysobjects b 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 </code></pre> <p>Here is some proof that the underlying implementation with CASE will execute sub queries.</p> <pre><code>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, two table scans. If 1=0, it does not result in a table scan. </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload