dave ballantyne's Blog

Getting Started with Adobe After Effects - Part 6: Motion Blur


	Blogs

Upload Image

Close it

Select File


	Like

Invite Friends

Browse by Tags · View All

BRH	8
TSQL	7
#TSQL	5
SQL Server	4
Performance	4
#DBA	3
DBA	2
Tips and Tricks	2
T-SQL	2
Syndicate	2

Archive · View All

July 2010	3
June 2010	3
May 2010	3
August 2010	2
September 2010	1

Home · Posts · Most Liked · Most Discussed · Ask a Question · Fans (54) · Preferences

IsNull or Coalesce. What’s the Difference?

May 19 2010 11:26AM by dave ballantyne

During his 24 Hours of Pass session, Andy Leonard was asked about the difference between a IsNull and a coalesce operation. Ordinarily, and let’s assume we are only talking about 2 parameters here, there isn’t much difference between them. Certainly you could probably run a million row query using the two and there would be a negligible impact.

However there is one major difference:

COALESCE(X,Y) , is interpreted as CASE WHEN X IS NOT NULL THEN X ELSE Y END. If X is a formula, then it will be evaluated twice. You can clearly see this in the query plan:

Execute

Create Table #a1(Col1 integer,Col2 integer)
go

select coalesce(col1,col2) 
from #a1

Then, in the query plan , look at the properties of the “Compute Scalar” operation and you will see this:

If this is a simple A+B formula, then again a negligible difference, but what if it is a subquery ?

Create 3 small temporary tables:

Create Table #a(Col integer)
go

Create Table #b(Col integer)
go

Create Table #c(Col integer)

Then execute this query and look at the query plan.

select 
	coalesce(
		(Select top(1) COL from #b),
		(Select top(1) col from #c))
from #a

Now we know what is happening with COALESCE under the covers, the double scan of #b will be of no surprise.

Ill leave it up to you to decide what horrors this code is capable of inflicting upon your server.

create function funca()
returns integer
as
begin
	return 1
end
go

create function funcb(
returns integer
as
begin 
	return 1
end
go

select 
	coalesce(dbo.funca(),dbo.funcb()) 
from #a1

Tags: Performance, Coalesce, isNULL, TSQL, BRH, Syndicate,

Previous:
Next: SSIS Package Reuse

dave ballantyne

111 · 1% · 462

Liked

Lifesaver

Refreshed

Learned

Incorrect


Like this
Post this to my facebook wall
Tweet this
This was a true life saver
Thanks, this helped me to refresh my memory
Very informative, I just learned something new
I disagree, this information is incorrect


	Submit

8 Comments

Here are my thoughts about the two http://beyondrelational.com/blogs/madhivanan/archive/2007/10/04/isnull-or-coalesce.aspx

commented on May 25 2010 2:41AM

Madhivanan

3 · 39% · 12430

So in short could we say ISNULL is better then coalesce???

commented on May 25 2010 7:06AM

uzairahm

514 · 0% · 71

"Better" and "Best" are not really terms that can be used in blanket statements. Im highlighting the difference and then its up to the reader to decide the best for their circumstances.

commented on May 25 2010 11:38AM

dave ballantyne

111 · 1% · 462

Here is a good example of the differences

declare @test varchar(2) Select IsNull(@test, 54545) select COALESCE(@test, 54545)

Results: * 54545

commented on May 25 2010 4:02PM

Brian Filppu

2638 · 0% · 4

Nicely done, Dave. Here's another (very) slight difference for those that have to operate on really big data.

--===== Conditionally drop the test table to make reruns easier IF OBJECT_ID('TempDB..#TestTable','U') IS NOT NULL DROP TABLE #TestTable;

--===== Build and populate the test tables on the fly SELECT TOP (1000000) IDENTITY(INT,1,1) AS SomeInt, CAST(NULL AS INT) AS SomeNull INTO #TestTable FROM Master.sys.AllColumns ac1 CROSS JOIN Master.sys.AllColumns ac2;

--===== Create a variable used to take the display time out of the picture DECLARE @BitBucket INT;

--===== Tests for performance PRINT '========== ISNULL on all INTs ====='; SET STATISTICS TIME ON; SELECT @BitBucket = ISNULL(SomeInt,1) FROM #TestTable; SET STATISTICS TIME OFF;

PRINT '========== ISNULL on all NULLs ====='; SET STATISTICS TIME ON; SELECT @BitBucket = ISNULL(SomeNull,1) FROM #TestTable; SET STATISTICS TIME OFF;

PRINT '========== COALESCE on all INTs ====='; SET STATISTICS TIME ON; SELECT @BitBucket = COALESCE(SomeInt,1) FROM #TestTable; SET STATISTICS TIME OFF;

PRINT '========== COALESCE on all NULLs ====='; SET STATISTICS TIME ON; SELECT @BitBucket = COALESCE(SomeNull,1) FROM #TestTable; SET STATISTICS TIME OFF;

On my box, I get the following results...

(1000000 row(s) affected) ========== ISNULL on all INTs =====

SQL Server Execution Times: CPU time = 594 ms, elapsed time = 603 ms. ========== ISNULL on all NULLs =====

SQL Server Execution Times: CPU time = 625 ms, elapsed time = 624 ms. ========== COALESCE on all INTs =====

SQL Server Execution Times: CPU time = 703 ms, elapsed time = 703 ms. ========== COALESCE on all NULLs =====

SQL Server Execution Times: CPU time = 766 ms, elapsed time = 775 ms.

commented on Jun 27 2010 1:20PM

Jeff Moden

166 · 1% · 291

As a sidebar, I REALLY hate blogs that have moderation (like this one) for multiple reasons.

commented on Jun 27 2010 1:23PM

Jeff Moden

166 · 1% · 291

Generally wouldn't this be bad programming practise, substituting an int for a varchar(2)?: declare @test varchar(2) Select IsNull(@test, 54545) select COALESCE(@test, 54545)

commented on Aug 14 2010 9:21AM

Jonathan Roberts

76 · 2% · 744

@Jonathan Roberts

Probably, but the ideia seems to be to show isnull returns the same type as the first argument, another example that could cause some trouble:

declare @test varchar(2)

Select IsNull(@test, 'testing') Select COALESCE(@test, 'testing')

Resulting: 'te' 'testing'

commented on Oct 4 2010 3:46PM

dave ballantyne's Blog

IsNull or Coalesce. What’s the Difference?

8 Comments

Your Comment