This isn’t about performance
If you’re interested in performance tests, you can get in the way back machine and read a couple posts by Adam Machanic here and here. I’m also not talking about the difference between them. There are a million articles about that. They’re obviously different, but how does SQL handle that internally?
We’re going to run the four queries below separately, and look at the XML of the execution plan for each.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
SELECT TOP 1000 COALESCE([u].[Id], [u].[Age]) FROM [dbo].[Users] AS [u]; GO SELECT TOP 1000 COALESCE([u].[Age], [u].[Id]) FROM [dbo].[Users] AS [u]; GO SELECT TOP 1000 ISNULL([u].[Id], [u].[Age]) FROM [dbo].[Users] AS [u]; GO SELECT TOP 1000 ISNULL([u].[Age], [u].[Id]) FROM [dbo].[Users] AS [u]; GO |
Even though COALESCE is the ANSI standard, I still see most people using ISNULL. Spelling? Laziness? Who knows? Under the covers, it’s just a CASE expression. You could write the same thing yourself.
The second query gives you almost the same thing, but the columns are reversed. What’s the point, then?
ISNULL ISDIFFERENT
SQL does something rather smart here. The Id column is NOT NULL, so when ISNULL is applied to it, it doesn’t bother evaluating anything.
Reversed, ISNULL, and whatever magical behind-the-scenes code Microsoft has thrown in there, runs against the Age column, which is NULLable.
Pros and Cons
ISNULL Pros
- Easy to spell
- Shortcuts if you do something dumb
- Not the name of a horrible band
ISNULL Cons
- Only two inputs
- Not ANSI standard
- Maybe doesn’t shower regularly
Coalesce Pros
- ANSI Standard
- Multiple inputs
- Drinks coffee black
Coalesce Cons
- Spelling
- Just a case expression
- Is the name of a horrible band
Thanks for reading!
14 Comments. Leave new
I always love a little 90’s hardcore/metalcore/whatevercore reference mixed in with SQL knowledge.
I try to include several cores and/or waves per post.
Much better post than Mladen and Stanislas Biron’s posts. COALESCE until Microsoft ends our support (or until we die)!
The data type of the output is also determined differently. ISNULL returns the data type of the first argument, whereas COALESCE returns the highest precedence data type of all of the arguments. Not an insignificant difference.
I agree. The article said “I’m also not talking about the difference between them. There are a million articles about that” but, personally, I think those differences need to be considered in the Pros and Cons list. I default to using COALESCE specifically because of the potential subtle bug caused by ISNULL silently adopting the data type of the first parameter.
The differences aren’t necessarily good or bad, just things to consider. I’m also not sure I’d call documented behavior a bug.
We ran across an issue yesterday where SQL server decided it would only use an index on a joined table if we used IsNull() instead of Coalesce():
Joining like this caused SQL Server to do a table scan on ThirdTable (query ran for hours before we killed it):
left join ThirdTable tbl3 on tbl3.KeyCol = coalesce(tbl1.KeyCol,tbl2.KeyCol)
Joining like this caused SQL Server to use the KeyCol-based index on ThirdTable (query finished in 19 seconds):
left join ThirdTable tbl3 on tbl3.KeyCol = isnull(tbl1.KeyCol,tbl2.KeyCol) — use tbl3.KeyCol-b
Data types on KeyCol are identical across all three tables.
Any ideas what’s going on here?
We always use coalesce() in our code since it’s more portable. We have other views with similar joins using coalesce() and it’s not a problem. This particular instance was working fine since we put the view in place 4 weeks ago, and only became problematic yesterday. ThirdTable has just under 1 million rows and increased by about 2200 rows yesterday. Updating statistics on all tables involved made no difference.
Bill – your best bet is probably to post the execution plans using SQL Sentry Plan Explorer. It’s got an Anonymizer feature that will anonymize your plans and post them to SQLPerformance.com where folks can review them in more detail.
Any chance the fields you’re joining on have variable precision? If so, you may want to try explicitly casting it to the exact column definition to see if it changes any behavior.
Perhaps it is worthwhile also surfacing the difference in behavior between ISNULL and COALESCE mentioned here:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/2b751e93-c7ae-48bc-9289-3015d3c37972/bug-in-isnull-function?forum=sqltools
For the main developer use case I think ISNULL is more readable and implies the intent better. i.e. Yes the field is from a LEFT OUTER JOIN’ed table but I don’t want a null in my returned results.
A little bit of history about coalesce (). The ANSI X3H2 committee was considering this, we look for a name for this function. Phil Shaw from IBM was one of the early committee members and one of the smartest guys we had. He was good on technical issues, ISO procedural issues, and the general functioning of the committee. He always carried a copy of Roberts rules of order a dictionary and thesaurus with him.
Phil pulled pulled out his thesaurus, and looked for a synonym that was the least likely to ever be used as a column name and a real table (honestly!). That candidate word was “coalesce”, and that is why we picked it.
I bet if he didn’t have that thesaurus with him, it would have been called MUSH() or something. Which, maybe…
In the case of such a pattern
Select
Coalesce ((Select Value From Test Where ID = @ID, -1)
it makes sense to use
Select
IsNull ((Select Value From Test Where ID = @ID, -1)
because of the number of seeks