Differences between COALESCE and ISNULL


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

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.

Advertisements

3 thoughts on “Differences between COALESCE and ISNULL

  1. Howdy just wanted to give you a quick heads up and let you know a few of the images aren’t loading properly.
    I’m not sure why but I think its a linking issue. I’ve tried it in two different
    internet browsers and both show the same results.

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