Some of the differences between ISNULL and COALESCE are quite obvious. For example, a column that’s been created by ISNULL is not regarded as NULLABLE. On the other hand, a column created by COALESCE is regarded as nullable. Similarly, ISNULL only takes two arguments, whereas COALESCE can accept more than two arguments. It is possible to nest ISNULL statements, however, so this can be used to meet a requirement to use ISNULL rather than COALESCE.
There is one difference between ISNULL and COALESCE, however, that may not be so obvious. There is a restriction with ISNULL, where the length of the first parameter dictates the data type of the resulting column. Thus, if the second argument is longer than the datatype of the first argument, it will be truncated.
Here is an example using the AddressLine2 column of the Person.Address table in the AdventureWorks2008 database, which has a datatype of nvarchar(60):
SELECT
ISNULL(AddressLine2,
‘1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890’
) as ISNULL_Result
FROM [AdventureWorks2008].[Person].[Address]
SELECT
COALESCE(AddressLine2,
‘1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890’
) As COALESCE_Result
FROM [AdventureWorks2008].[Person].[Address]
Using ISNULL, the resulting column is reduced to having a length of 60. Using COALESCE, however, no such restriction is in place. Thus, using the above query, the length of the second argument is retained, resulting in a length of 70. This can be seen in the image below:
It is worth noting that, if COALESCE isn’t used properly, it can provide strange results. For example, if the datatypes are far apart, such as an integer and a datetime, COALESCE will return a datetime. So, the following SQL command:
SELECT COALESCE(100, GETDATE()) as Coalese_Result
This results in the following:
‘1900-04-11 00:00:00.000’
Since datetime has been picked as the higher priority datatype for the resulting, the integer value of 100 has been converted to a datetime. COALESCE is behaving as it was designed to do, which is to pick the higher-priority data type. Just be careful of unexpected results if the datatypes are mixed: test, test and test again!
To summarise, COALESCE can be a useful SQL tool to have in your back pocket, if it is used and tested properly.
Good overview of the differences…
So many reasons to use COALESCE() over ISNULL()…
Another is that COALESCE() is an ANSI standard function; whereas, ISNULL() is not.
Also, perhaps COALESCE() works so well (and as expected) is that the optimizer translates it to a CASE expression behind the scenes…
In other words COALESCE(Column,0) is translated to CASE WHEN Column IS NULL THEN 0 ELSE Column END.
–Brad