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.