Sunday, August 01, 2004

 

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


Feel Free to postback your comments.


Comments:
Wow..its really a great explanation
 
Um, so, which is faster? Generally.
 
Good Work !!
thanks
 
great article, good work, thanks!
 
Have you heared about a game which you need use kal geons to play, and you can also borrow kal gold from other players? But you can buy kal online geons, or you will lose the choice if you do not have kal online gold. If you get kalonline Geons, you can continue this game.
Have you heared about a game which you need use Entropiauniverse ped to play, and you can also borrow Entropia Universe Gold from other players? But you can Buy Entropia Universe Gold, or you will lose the choice if you do not have Entropia Universe Money. If you get cheap Entropiauniverse ped, you can continue this game.
 
Your blog is wonderful, I like it very much, thank you!
By the way, do you like polo shirts, which are very chic, especially the polo t shirts, I love them very much. I also like playing tennis rackets, it can keep healthy, what do you like to do?
We are the outlet of polo t shirts women, polo t shirts on sale, polo t shirts for women, polo shirts on sale, these products are best-seller in our store online.besides we also sell polo shirts men, men's polo shirt, men polo shirt, mens polo shirts, mens polo shirt and cheap polo shirts, discount polo shirts, men's polo shirts, women's polo shirts We are also the outlet of cheap tennis racket, discount tennis racket, and the main product is prince tennis racquet, head tennis rackets, wilson tennis racket, babolat tennis racquet.You are warmly welcomed to my store online!
 
Free WordPress Themes from templatedesk.com- Site Templates and CMS Themes.
 
Post a Comment

<< Home

This page is powered by Blogger. Isn't yours?