ISNULL versus COALESCE

Wednesday, December 1, 2004
SQLJunkie Adam Machanic has a good analysis of the SQL functions ISNULL and COALESCE, both of which we can use to replace NULL values in T-SQL.

I wasn't aware of the perf difference, but I quit using ISNULL when it bit me in some code I was using to extract records from a client’s database. They had a field allowing NULL values where we did not. I was using something like the following batch, which has a bug.

CREATE TABLE #Foos
(
  FooID int PRIMARY KEY IDENTITY,
  FooDescription varchar(5) NULL
)
 
CREATE TABLE #Bars
(
  BarID int PRIMARY KEY IDENTITY,
  BarDescription varchar(15) NOT NULL
)
 
INSERT INTO #Foos (FooDescription) VALUES(NULL)
INSERT INTO #Foos (FooDescription) VALUES(NULL)
 
INSERT INTO #Bars (BarDescription)
  SELECT ISNULL(FooDescription, 'No Description')
  FROM #Foos
 
INSERT INTO #Bars (BarDescription)
  SELECT COALESCE(FooDescription, 'No Description')
  FROM #Foos
            
 
SELECT BarID, BarDescription FROM #Bars
 
DROP TABLE #Bars
DROP TABLE #Foos

The resultset from the previous batch will make my bug stand out:

1          No De
2          No De
3          No Description
4          No Description

Oops. ISNULL apparently truncates the replacement value (2nd parameter) to the size of the check expression (1st parameter), while COALESCE does not. Long live COALESCE.


Comments
Adam Machanic Wednesday, December 1, 2004
Good catch! Yet another reason to use COALESCE, even if it's ever-so-slightly less performant.
Bas Wednesday, March 16, 2005
Interresting comparison, but to say that you would ALWAYS want to use COALESCE I find not true, as you may want to have it limit the returned value from your NULL check. Maybe we need to use it in a straight comparison, then the field size could be important.
<br>
<br>In the little example you give there, we are moving data between tables, but this is only one situation where NULLs may be wanted to be filtered out.
<br>
Jerry Nixon Tuesday, May 23, 2006
I found IsNull() to be fastest.

jerrytech.blogspot.com/...

By the way, IsNull() will not truncate anything if the leading datatype is correctly cast prior to the isNull() operation.

Declare @x varchar(1)
Set @x = IsNull(@x, 'Jerry Nixon')

Results: 'J'

Declare @x varchar(11)
Set @x = IsNull(@x, 'Jerry Nixon')

Results: 'Jerry Nixon'

This behavior is documented and years old. Using correct data types and lengths isn't the best reason to opt for a poorer performing function. However, I would conceed Coalesce() is easier to read - from a maintenance point of view.
Comments are now closed.
by K. Scott Allen K.Scott Allen
My Pluralsight Courses
The Podcast!