ISNULL() vs. COALESCE()

There are a some minor differences between COALESCE() and ISNULL():

* COALESCE() is ANSI standard, so that is an advantage for the purists out there.

* Many consider ISNULL()'s readability and common sense naming to be an advantage. While I will agree that it easier to spell and pronounce, I disagree that its naming is intuitive. In other languages such as VB/VBA/VBScript, ISNULL() accepts a single input and returns a single boolean output.

* ISNULL() accepts exactly two parameters. If you want to take the first non-NULL among more than two values, you will need to nest your ISNULL() statements. COALESCE(), on the other hand, can take multiple inputs:

SELECT ISNULL(NULL, NULL, 'foo')

-- yields:
Server: Msg 174, Level 15, State 1, Line 1
The isnull function requires 2 arguments.

SELECT COALESCE(NULL, NULL, 'foo')

-- yields:
----
foo

In order to make this work with ISNULL(), you would have to say:

SELECT ISNULL(NULL, ISNULL(NULL, 'foo'))

* The result of ISNULL() always takes on the datatype of the first parameter (regardless of whether it is NULL or NOT NULL). COALESCE works more like a CASE expression, which returns a single datatype depending on precendence and accommodating all possible outcomes. For example:

DECLARE @foo VARCHAR(5)
SET @foo = NULL
SELECT ISNULL(@foo, '123456789')

-- yields:
-----
12345

SELECT COALESCE(@foo, '123456789')

-- yields:
---------
123456789

This gets more complicated if you start mixing incompatible datatypes, e.g.:

DECLARE @foo VARCHAR(5), @bar INT
SET @foo = 'foo'
SET @bar = NULL

SELECT ISNULL(@foo, @bar)
SELECT COALESCE(@foo, @bar)

-- yields:

-----
foo

Server: Msg 245, Level 16, State 1, Line 6
Syntax error converting the varchar value 'foo' to a column of data type int.

* A relatively scarce difference is the ability to apply constraints to computed columns that use COALESCE() or ISNULL(). SQL Server views a column created by COALESCE() as nullable, whereas one using ISNULL() is not. So:

CREATE TABLE dbo.Try
(
col1 INT,
col2 AS COALESCE(col1, 0)
PRIMARY KEY
)
GO

-- yields:
Server: Msg 8111, Level 16, State 2, Line 1
Cannot define PRIMARY KEY constraint on nullable column in table 'Try'.
Server: Msg 1750, Level 16, State 1, Line 1
Could not create constraint. See previous errors.

Whereas the following works successfully:

CREATE TABLE dbo.Try
(
col1 INT,
col2 AS ISNULL(col1, 0)
PRIMARY KEY
)
GO


* Finally, COALESCE() can generate a less efficient plan in some situations.