Coalesce vs. ISNull SQL, Coalesce SQL

As with many technology questions involving roughly equivalent choices, it depends. There are a variety of 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

If you are using COALESCE() and or ISNULL() as a method of allowing optional parameters into your WHERE clause, please see Article #2348 for some useful information (the most common techniques will use a scan, but the article shows methods that will force a more efficient seek).

Finally, COALESCE() can generate a less efficient plan in some cases, for example when it is used against a subquery. Take the following example in Pubs and compare the execution plans:

USE PUBS
GO

SET SHOWPLAN_TEXT ON
GO

SELECT COALESCE
(
(SELECT a2.au_id
FROM pubs..authors a2
WHERE a2.au_id = a1.au_id),
''
)
FROM authors a1

SELECT ISNULL
(
(SELECT a2.au_id
FROM pubs..authors a2
WHERE a2.au_id = a1.au_id),
''
)
FROM authors a1
GO

SET SHOWPLAN_TEXT OFF
GO

Notice the extra work that COALESCE() has to do? This may not be a big deal against this tiny table in Pubs, but in a bigger environment this can bring servers to their knees. And no, this hasn't been made any more efficient in SQL Server 2005, you can reproduce the same kind of plan difference in AdventureWorks:

USE AdventureWorks
GO

SET SHOWPLAN_TEXT ON
GO

SELECT COALESCE
(
(SELECT MAX(Name)
FROM Sales.Store s2
WHERE s2.name = s1.name),
''
)
FROM Sales.Store s1

SELECT ISNULL
(
(SELECT MAX(Name)
FROM Sales.Store s2
WHERE s2.name = s1.name),
''
)
FROM Sales.Store s1

GO
SET SHOWPLAN_TEXT OFF
GO

Should I use COALESCE() or ISNULL()?

Related Articles



// home // advanced search // news // categories // sql build chart // downloads // statistics
ASP FAQ