Differences between COALESCE and ISNULL in TSQL

Differences between COALESCE and ISNULL

I am trying to document the difference
between some of the Keywords/functions, that are used for the same purpose and
has some differences.

Let me
start with COALESCE and ISNULL today. What comes first to my mind is COALESCE is
hard to spell ;)

Lets see what are the relevant differences.


1. COALESCE is ANSI standard and
ISNULL is T-SQL proprietary.

2. You can work with only one value at a time with
ISNULL, but COALESCE can deal with multiple values. as in

SELECT ISNULL(NULL,
'SomeValue')
GO
SELECT COALESCE(NULL, NULL,
NULL, 'SomeValue')
GO

3. With ISNULL, the Alternate value you specify is
limited to the length of the first parameter. In case of COALESCE, you dont have
such restrictions.

See
the example below.

DECLARE @somestring
CHAR(4)
SET
@somestring = NULL

SELECT ISNULL(@somestring,
'Roji Thomas')
--Returns 'Roji'


SELECT COALESCE(@somestring,
'Roji Thomas')
--Returns 'Roji Thomas'


4. When using COALESCE All
expressions must be of the same type or must be implicitly convertible to the
same type. If you are not careful, this can give you incorrect results. See the
examples below.

SELECT COALESCE(NULL,
GetDate())
--Returns : 2004-07-26
13:47:15.937
--Correct

SELECT COALESCE(1,
GetDate())
--Returns 1900-01-02
00:00:00.000
--Incorrect Value


SELECT
COALESCE(1,'abcd')
--Returns 1
SELECT
COALESCE('abcd',1)
--Returns Error

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

SELECT
COALESCE(NULL,435,'abcd', CURRENT_TIMESTAMP,'xyzzz',435)
--Returns 1901-03-12
00:00:00.000
--Incorrect


SELECT
COALESCE(NULL,GetDate(),9999999999)
--Returns : 2004-07-26
13:47:15.937
--Correct

SELECT
COALESCE(NULL,9999999999, GetDate())
--Returs Error
-- Server: Msg 8115, Level 16,
State 2, Line 1
-- Arithmetic overflow error
converting expression to data type datetime.

5. Eventhough COALESCE is generally the preferred
way, there are some performance consideration especially when you have a select
statement as an arguument to COALESCE. So always analyse the execution plan. You
can read further abt it here.


Here is two interesting
Performance comparison between COALESCE and ISNULL.

http://blogs.x2line.com/al/archive/2004/03/01/189.aspx

http://weblogs.sqlteam.com/mladenp/articles/2937.aspx