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 not" databases.aspfaq.com
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:
Very clear :)
Thanks for the specific differences.
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)
Post a Comment