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
<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>
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.