ISNULL versus COALESCE

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.

Print | posted @ Wednesday, December 01, 2004 7:24 AM

Comments on this entry:

# re: ISNULL versus COALESCE

by Adam Machanic at 12/1/2004 5:55 PM

Good catch! Yet another reason to use COALESCE, even if it's ever-so-slightly less performant.

# re: ISNULL versus COALESCE

by Bas at 3/16/2005 11:52 PM

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>

# re: ISNULL versus COALESCE

by Jerry Nixon at 5/23/2006 11:05 PM

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.