Adventures in SQL: Coalesce versus ISNULL

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:

ISNULL versus COALESCE result

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.

One thought on “Adventures in SQL: Coalesce versus ISNULL

  1. 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s