Crikey. That title sounds like an essay I had to write when I was at school and that was a long time ago...
Jamie Thomson raised an important point in a comment on a blog I posted yesterday entitled "Getting rid of getdate()". In it I stated that ISNULL() and COALESCE() are in the same boat as getdate() and CURRENT_TIMESTAMP. Both COALESCE() and CURRENT_TIMESTAMP are ANSI standard functions whereas their corollary T-SQL specific functions, ISNULL() and GETDATE() are not. However, Jamie pointed out that ISNULL() does have a behaviour difference to COALESCE(). That set me thinking as to what if any other differences there were between these two functions. Guess what I found one!
In Books On-line it states a key difference between the two functions. When you COALESCE() an expression the value coalesced is Nullable. Whereas if you used ISNULL then the value is NOT NULL. This shows up when using these functions as computed columns. I have to say this isn't something I do a lot but ignorance is no excuse in the eyes of the law and so I am not going to start making excuses now (although...I think I just have)...
So to take the Books On-line example:
CREATE TABLE #CheckSumTest
(
ID int identity ,
Num int DEFAULT ( RAND() * 100 ) ,
RowCheckSum AS COALESCE( CHECKSUM( id , num ) , 0 ) PRIMARY KEY
)
Produces an error:
Msg 1711, Level 16, State 1, Line 1
Cannot define PRIMARY KEY constraint on column 'RowCheckSum' in table '#CheckSumTest'. The computed column has to be persisted and not nullable.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
Whereas this (note now using ISNULL()) does not:
CREATE TABLE #CheckSumTest
(
ID int identity ,
Num int DEFAULT ( RAND() * 100 ) ,
RowCheckSum AS ISNULL( CHECKSUM( id , num ) , 0 ) PRIMARY KEY
)
So there we have it a difference. This is not the end of the story though. It is still possible to use COALESCE() in this scenario. You need to use the PERSISTED key word. Like this:
CREATE TABLE #CheckSumTest
(
ID int identity ,
Num int DEFAULT ( RAND() * 100 ) ,
RowCheckSum AS COALESCE( CHECKSUM( id , num ) , 0 ) PERSISTED PRIMARY KEY
)
So functionally it can be the same. Note the clue in the error message about persistence. Functionally the same result can be achieved with either function and we can still safely dispose of ISNULL(). Hoorah.
This however brings me nicely onto Jamie's comment. He pointed me at an article that shows an example of ISNULL() and COALESCE() producing different results.
The example was:
DECLARE @value VARCHAR(2)
DECLARE @result1 VARCHAR(4)
DECLARE @result2 VARCHAR(4)
SELECT @result1 = ISNULL(@value,'test')
SELECT @result2 = COALESCE(@value,'test')
SELECT @result1 AS Result
UNION ALL
SELECT @result2 AS Result
With the result being:
Yuck. That to me has all the hallmarks of being a bug! Cards on the table time. I can't see how the definition of one variable should influence the output of the ISNULL()/COALESCE(). To me ISNULL is broken - each value separated by the comma is isolated and the test being performed is whether the value contained within either is or isn't null. The data type shouldn't have anything to do with it and certainly shouldn't have a bearing on the overall outcome of the function. This gives me greater reason to exclude the usage of ISNULL from a standards perspective. If I saw a developer actually try and 'leverage' this feature I'd ask for the code to be re-written as I expect there'd have been a better way to write the same thing. In my own mind therefore I will continue to exclude the usage of ISNULL().
However, many thanks to JT for pointing this out - it's certainly a gotcha that I hadn't previously appreciated.
I am off to connect now to file some "feedback" on this feature... I'll update you in due course depending on what the powers say.
Cheers, James