There are hundreds of blogs that compare the T-SQL ISNULL function with the ANSI standard COALESCE function. There are also plenty of arguments of why you should use one over the other.
I’m in the ISNULL camp. I once argued about this with a Microsoft MVP. He was adamant that you should use COALESCE because ISNULL is not an ANSI standard function. My argument was simple. COALESCE is slow. He even mentioned something silly about ISNULL potentially getting deprecated down the road and I responded that it’s still not deprecated in SQL 2016 so it will still be there in SQL 2018 so why not write faster code for what you have today. Right?
He didn’t agree. I left it with the idea that you could write every application to be completely platform agnostic; this way you can just keep moving from platform to platform when you realize the performance is so poor from not taking advantages of platform specific code. Hopefully the next platform will be much faster. Right? hehe.
Let’s discuss why ISNULL is often faster
Without running this or looking below, examine the following statement and tell me what the result for each column should be:
DECLARE @string char(1) = null
SELECT ISNULL(@string, ‘This is NULL’) as A,
COALESCE(@string, ‘This is NULL’) as B
Take some time.
Think about it.
Do they return the same result?
Why would I use this example if I’m so concerned about demonstrating speed?
Have you had enough time to think?
Did you come up with an answer?
Was your answer “This is NULL” for both columns?
Was your answer different for each column?
Have I spaced out the answer far enough down the post to where you haven’t instantly read it yet?
The answer is:
Column A = “T”
and Column B = “This is NULL”
How is this possible? Well, I can say that we’ve just demonstrated one extreme difference between the two functions that you may have never noticed before.
ISNULL respects the datatype of the input. COALESCE does not.
What happens if you replace “This is NULL” with an int value such as 1234? Column A would return “*” and B would return 1234.
Understanding this is huge step closer to understanding why ISNULL can often be faster than its ANSI standard cousin COALSECE.
If you enjoyed this post, be sure to check out my other posts on SQL Server Central and SQL Tech Blog.
Also be sure to follow me on Twitter and LinkedIn. I am always happy to get new endorsements and chat with my followers.
