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.

posted on Tuesday, November 30, 2004 11:24 PM by scott

Comments

Wednesday, December 01, 2004 9:55 AM by Adam Machanic

# re: ISNULL versus COALESCE

Good catch! Yet another reason to use COALESCE, even if it's ever-so-slightly less performant.
Friday, January 21, 2005 10:56 AM by

# RE: Searchable Nullable Optional Fields in SQL

Wednesday, March 16, 2005 4:52 PM by Bas

# re: ISNULL versus COALESCE

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.

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.
Tuesday, May 23, 2006 4:05 PM by Jerry Nixon

# re: ISNULL versus COALESCE

I found IsNull() to be fastest.

http://jerrytech.blogspot.com/2006/05/sql-2k-performance-isnull-vs-coalesce.html

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.