How are COALESCE and ISNULL different?

6

August 7, 2013 by Kenneth Fisher

At first glance these two functions seem very similar, and superficially they are. For example COALESCE(fieldname1, fieldname2) and ISNULL(fieldname1, fieldname2) return seemingly identical results. The big obvious difference is that ISNULL can only have two parameters while COALESCE can have n parameters.

So what are some of the more subtle but still significant differences?

First let’s create a work table and add some data.

CREATE TABLE ISNULL_vs_COALESCE
(
	VARCHAR30 VARCHAR(30) NULL,
	VARCHAR10 VARCHAR(10) NOT NULL,
	[DATETIME] DATETIME,
	[INTEGER] INT
)
GO

INSERT INTO ISNULL_vs_COALESCE VALUES (
	'This is the varchar(30) field',
	'varchar10',
	'1/1/2001',
	1234)
GO

Next, as an easy way to show several differences, I’m going to create a view that uses ISNULL and COALESCE in various ways and then run sp_help on the view.

CREATE VIEW vw_ISNULL_vs_COALESCE AS
	SELECT
		ISNULL(VARCHAR30,VARCHAR10) AS ISNULL_Test_30_10,
		COALESCE(VARCHAR30,VARCHAR10) AS COALESCE_Test_30_10,

		ISNULL(VARCHAR10,VARCHAR30) AS ISNULL_Test_10_30,
		COALESCE(VARCHAR10,VARCHAR30) AS COALESCE_Test_10_30
	FROM ISNULL_vs_COALESCE
GO

EXEC sp_help vw_ISNULL_vs_COALESCE
GO

The results of the sp_help look like this:

ISNULLvsCOALESCE1

There a few things of note. First take a look at the datatype. All of the columns are varchar to simplify things but the lengths are different. You will note that COALESCE has a length of 30 regardless. Specifically COALESCE returns a value with a datatype of the highest prescedence (in this case the longest varchar) from the list of parameters. ISNULL on the other hand returns a value with the datatype of the first parameter. You might think “No big deal, I’ve never run into a problem before.” However if you think about it this can have some fairly significant ramifications if for example your parameters are varchar and int.

To demonstrate that let’s try out the other data types in my test table. Specifically the ones with data types of DATETIME and INTEGER.

SELECT ISNULL([INTEGER], [DATETIME]) AS ISNULL_Test_INT_DT
FROM ISNULL_vs_COALESCE
GO

CREATE VIEW vw_ISNULL_COLLATE_DT_and_INT AS
SELECT ISNULL([DATETIME],[INTEGER]) AS ISNULL_Test_DT_INT,
	COALESCE([DATETIME],[INTEGER]) AS COALESCE_Test_DT_INT,
	COALESCE([INTEGER], [DATETIME]) AS COALESCE_Test_INT_DT
FROM ISNULL_vs_COALESCE
GO

EXEC sp_help vw_ISNULL_COLLATE_DT_and_INT
GO

The first select is not part of the view because it returns an error. In this case ISNULL wants to return a data type of INT. But since it can’t implicitly convert the DATETIME value to INT it returns an error in case the first parameter ends up being NULL.

Msg 257, Level 16, State 3, Line 1
Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.

The sp_help returns the following:

ISNULLvsCOALESCE3

Remember that COALESCE returns a value with the highest precedence data type of the parameter list while ISNULL returns the data type of the first parameter. This means that COALESCE is going to return a value with the data type of datetime regardless of the order of the parameters. And since the ISNULL test where INTEGER was passed in first failed, and we had to remove it from the view, only the test where DATETIME is first is left. This means that it also has a data types of datetime. This also gives us a rather interesting result if we query the view but let’s consider the result and the why of the result homework.

Next note the Nullable column back on the first sp_help result. Now it get’s a little weird here and I ran a few extra tests on my own, switching the NULL/NOT NULL values on the table back and forth. COALESCE was always NULLABLE unless both parameters were NOT NULL. ISNULL on the other hand always returned NOT NULL unless the lower prescendence data type (varchar(10)) was NULLABLE and the first in the list of parameters or both parameters were NULLABLE. I’m not really sure why it worked out this way and BOL says this:

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.

Which to be very honest I don’t quite follow either. Particularly when I try to match it up with my test results. So in the end I’m going to say if the NULLABILITY of your result matters you will just have to pay attention to your specific case.

Moving on. Since data type was different let’s see what happens with COLLATION.

SELECT
 COALESCE(VARCHAR30 COLLATE SQL_Latin1_General_CP1_CI_AS,
	VARCHAR30 COLLATE Latin1_General_CI_AS)
FROM ISNULL_vs_COALESCE
GO

SELECT
 COALESCE(VARCHAR30 COLLATE Latin1_General_CI_AS,
	VARCHAR30 COLLATE SQL_Latin1_General_CP1_CI_AS)
FROM ISNULL_vs_COALESCE
GO

CREATE VIEW vw_ISNULL_COLLATE_TEST AS
	SELECT
	 ISNULL(VARCHAR30 COLLATE SQL_Latin1_General_CP1_CI_AS,
		VARCHAR30 COLLATE Latin1_General_CI_AS) AS ISNULL_COLLATE_TEST1,
	 ISNULL(VARCHAR30 COLLATE Latin1_General_CI_AS,
		VARCHAR30 COLLATE SQL_Latin1_General_CP1_CI_AS) AS ISNULL_COLLATE_TEST2
	FROM ISNULL_vs_COALESCE
GO

EXEC sp_help vw_ISNULL_COLLATE_TEST
GO

The reason the COLLATE tests are just selects and not views is because they return errors even when trying to create a view. Here are the errors:

Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the CASE operation.
Msg 468, Level 16, State 9, Line 2
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the CASE operation.

The ISNULL query however will work and I was able to create view without the COALESCE tests. In this case sp_help returns this:

ISNULLvsCOALESCE2

So it appears that COALESCE has a problem with implicitly converting the COLLATION of the output while ISNULL returns the COLLATION of the first parameter and appears to handle the implicit conversion without a problem. Again some interesting ramifications there.

There are a few other differences that are based on how ISNULL and COALESCE are implemented but that goes beyond what I am going to do here. Maybe as a part 2 later.

The thing to take away here is that while the two functions seem the same, they really aren’t and once you look past the superficial both produce different outputs. Probably not such a big deal in an ad-hoc query but more significant in a view or a calculated column.

6 thoughts on “How are COALESCE and ISNULL different?

  1. […] How are COALESCE and ISNULL different? – Taking a closer look at these two functions with Kenneth Fisher (Blog). […]

  2. Lynne says:

    Thank you. Helps me understand the difference!!

  3. Ron Hosenfeld says:

    Thanks for taking the time to generate this and explain the down-stream importance and impact that I’m not sure everyone would consider the first pass through.

  4. Nick Holt says:

    I love articles like this. ISNULL and COALESCE are some of the building blocks of database development and its great to be able to look more deeply into them and maybe learn something new! Thanks for taking the time to write this.

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

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,671 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: