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.