Tuesday 16 April 2013

ISNULL vs. COALESCE

A friend of mine just asked me when COALESCE should be used. As I'm a DBA and don't do too much SQL development work, I never had a need to use COALESCE (or I never thought I had a need). Therefore, I was inspired to do a little digging. As there has been much written about the similarities and differences I will use this post as a jumping off point to a few good resources that shed light on the topic. Though, I'll highlight some of the points here as well.



The main points:
  • ISNULL is a SQL Server proprietary function
  • COALESCE is ANSI SQL compliant
  • COALESCE works like a case statement where the first argument to be evaluated as NOT NULL will be displayed
  • COALESCE is generally less efficient than ISNULL, especially when subqueries are used within them
  • And finally, a slightly more complex and subtle difference: 
    • "An expression involving ISNULL with non-null parameters is considered to be NOT NULL, while expressions involving COALESCE with non-null parameters is considered to be NULL. In SQL Server, to index expressions involving COALESCE with non-null parameters, the computed column can be persisted using the PERSISTED column attribute." Taken from the above COALESCE MSDN article
      Or, said a little more simply: "SQL Server views a column created by COALESCE() as nullable, whereas one using ISNULL() is notdatabases.aspfaq.com
As an intro here's Microsoft's documentation on the two:
MSDN COALESCE documentation
MSDN ISNULL documentation

In addition to my points above, here is a summary of the differences:
http://blogs.msdn.com/b/sqltips/archive/2008/06/26/differences-between-isnull-and-coalesce.aspx

And here is a very well written explanation of how to use COALESCE:
http://itdeveloperzone.blogspot.in/2013/04/coalesce-in-sql-server.html

Here's an example of how data types are handled differently between them:
http://haacked.com/archive/2005/01/21/difference-between-isnull-and-coalesce.aspx

I hope this helps.

2 comments:

Tron said...

Very clear :)
Thanks for the specific differences.

Kristen said...

Key difference for me is that ISNULL result is the datatype of the first argument, COALESCE returns the data type of the expression with the highest precedence for that reason I always use COALESCE (plus the slight benefit that it will take more than 2 parameters, but the occasions when I make use of that are pretty rare)

SELECT ISNULL (CONVERT(int, NULL), CONVERT(float, '1.234')) -- returns INT 1
SELECT COALESCE (CONVERT(int, NULL), CONVERT(float, '1.234')) -- returns FLOAT 1.234

SELECT ISNULL (CONVERT(varchar(3), NULL), 'abcdefghij') -- returns "abc"
SELECT COALESCE (CONVERT(varchar(3), NULL), 'abcdefghij') -- returns "abcdefghij"

SELECT ISNULL (CONVERT(bit, NULL), -1) -- returns 1 (BIT, "True")
SELECT COALESCE (CONVERT(bit, NULL), -1) -- returns -1 (INT)