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:

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:

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:

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.

Filed under: Microsoft SQL Server, SQLServerPedia Syndication, T-SQL Tagged: COALESCE, code language, data types, isnull, language sql, microsoft sql server, T-SQL