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.
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:
<< Home
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.
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.
# posted by 
: 9:59 AM
: 9:59 AM
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!
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!
# posted by : 7:19 AM
: 7:19 AM
Both sides also collectively released a 3 festivities to commemorate the tenth anniversary of cooperation
Nike Air Max Shoes
Nike Air Max 2011
Nike Air Max
Nike Air Max 2009
Nike Air Max 95
Nike Air Max 91
Nike Air Max 87
Nike Air Max 180
Nike Air Max Griffey Max 1
Nike Air Max 90
Nike Air Max 1
Nike Air Max Wildwood Supreme
Nike Air Max Turbulence
Nike Air Max Skyline
Nike Air Max Goadome
Nike Air Max Fitsole
Nike Air Max Zenyth
Nike Air Max Boots
Nike Air Max Zoom Kobe
Nike Air Max Tn
Nike Air Max LTD
Nike Air Max Presto
Nike Air Max BW
Nike Air Max 24 7
Nike Air Max 2010
Nike Air Max 2011
Abercrombie and Fitch London
Abercrombie & Fitch Clothes
Vibram Shoes
Cheap Air Max
Nike Air Max Shoes
I saw peach falling from the window in your house. Walking really happy, to ensure that all of the uncomfortable into simple
silence. I'm the only person who heard one's heart beat.
Post a Comment
Nike Air Max Shoes
Nike Air Max 2011
Nike Air Max
Nike Air Max 2009
Nike Air Max 95
Nike Air Max 91
Nike Air Max 87
Nike Air Max 180
Nike Air Max Griffey Max 1
Nike Air Max 90
Nike Air Max 1
Nike Air Max Wildwood Supreme
Nike Air Max Turbulence
Nike Air Max Skyline
Nike Air Max Goadome
Nike Air Max Fitsole
Nike Air Max Zenyth
Nike Air Max Boots
Nike Air Max Zoom Kobe
Nike Air Max Tn
Nike Air Max LTD
Nike Air Max Presto
Nike Air Max BW
Nike Air Max 24 7
Nike Air Max 2010
Nike Air Max 2011
Abercrombie and Fitch London
Abercrombie & Fitch Clothes
Vibram Shoes
Cheap Air Max
Nike Air Max Shoes
I saw peach falling from the window in your house. Walking really happy, to ensure that all of the uncomfortable into simple
silence. I'm the only person who heard one's heart beat.
# posted by : 6:41 AM
: 6:41 AM << Home
