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!
 
welcome to the wow gold, cheap WoW Power Leveling, service site,wotlk gold buy cheap wow gold,wow gold,world of warcraft power leveling buy wow gold
 
buy wow gold,cheap wow gold,power wow power levelingworld of warcrft gold.
 
Do you know eve isk? I like it.
My brother often go to the internet bar to buy eve online isk and play it.
After school, He likes playing games using these buy isk with his friend.
I do not like to play it. Because I think that it not only costs much money but also spend much time. One day, he give me many cheap eve isk and play the game with me.
I came to the bar following him and found buy eve online isk was so cheap. After that, I also go to play game with him.
Do you know fiesta Gold? I like it.
My brother often go to the internet bar to buy fiesta money and play it.
After school, He likes playing games using these fiesta online gold with his friend.
I do not like to play it. Because I think that it not only costs much money but also spend much time. One day, he give me many buy fiesta Gold and play the game with me.
I came to the bar following him and found fiesta online money was so cheap. After that, I also go to play game with him.
 
Do you know that the wow gold? The players often forget to eat meal when they play the online games. In the game many players need the World of Warcraft Gold to up their levels. so they often search where can warcraft gold, I think our website maybe is your best choice. Many friends told me that in here can get buy wow gold, and here you can also relax yourself. so i hope more and more players come here to buy the cheap wow gold.
Do you want to know the magic of online games, and here you can get more Sho Online Mun. Do you want to have a try? Come on and Sho Mun can make you happy. You can change a lot Sho Online gold for play games. Playing online games can make much Sho gold. And you can buy Sho Online gold do what you want to do in the online game.
 
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.
 
I think 4story Luna changes my life. Because of 4story Gold, I meet a lot of friends. Besides, my friends usually give me some 4story money. I usually buy 4story Luna through Internet and advice from my friends, so I gain a lot of cheap 4story Luna and harvest in life.
Flyff is a very good game. Through buying flyff penya, I find fun in it. I am so glad that I can earn a lot of flyff money. Flyff cater to the taste of young people. With cheap penya, you can get everything you want in this game. So I like to buy flyff penya. For me flyff gold is not just a simple thing.
 
At one time or another, I do not have the rf gold ; at the first time, I am not willing to buy rf online gold . But I do not wonder I buy the rf money , my friend thinks that I am ill. But when I have it, it carries the magic from such a group, such a link in the world, rf cp , it is the key bring you to this world. But buy cheap rf gold is the best desire that I make.

Without thinking I buy some runescape gold . I use some buy runescape and want to find a good group. We often buy rs gold together on Monday. As if it is a rule. I think it is the real friendship. runescape money is already become the essential thing in my life. So I will introduce my friends have the cheap rs gold , we can be consist of a group, it must be very interesting.
 
It is the LOTRO Gold which make me very happy these days, my brother says Lord Of The Rings Gold is his favorite games gold he likes, he usually buy some buy LOTRO Gold to start his game and most of the time he will win the cheap Lord Of The Rings Gold back.
I am so happy to get some mabinogi gold and the cheap mabinogi is given by my close friend who tells me that the mabinogi money is the basis to enter into the game. Therefore, I should buy mabinogi gold with the spare money and I gain some mabinogi online gold from other players.
 
You smart and buy Sword of the New World Vis, you play the game is right, Sword of the New World Gold. you have a wonderful time, buy vis, I have it cheap snw vis, I buy Sword of the New World money.
I want give they happy time, twelve sky Gold, I work here with 12sky gold. Though, I can not make much money, twelvesky Gold, But I want to Exercise myself, 12Sky Silver Coins, I get up early 12 sky goldthey are like me and tell me anything about them wakfu kamas, I know wakfu gold. one of my friend likes to go to play buy wakfu kamas, I can not stand praising the land and the nature wakfu money , It makes us to be wakfu kama.
I am so happy to get some aion kina from my friends. They know I need aion online kina, they give me. So I always can get some aion gold from my friends. I buy aion kina with my spare money. It makes me happy that I can still earn some cheap aion kina.
 
If you are cheap wow gold trying any unknown cheats to earn gold, you would cheapest wow gold face some problem and your account will be black out. So don't try to wow gold find cheats instead try to world of warcraft gold sale follow the guideline of world of warcraft. These tips could Cheap Wow Goldwork as gold cheat and can help a lot to earn gold and money. World of Warcraft is the most exiting online game and while Playing the game most of the players struggle wow power leveling to earn a lot of gold.

Most of the players World Of Warcraft Goldtry search cheat code for this game to earn gold. This wow gold cheaparticle will help Wow Goldyou a lot to earn more and more gold.The best way to earn gold in this online game is by going through the guidelines wow gold for saleand other information about itemchannel.comthe game that are available on the internet .
Wow Gold aoc gold warhammer goldbuy warhammer gold.
 
http://www.thehairstraighteners.com/ : cheap hair straighteners
http://www.polosales.com/ : cheap Lacoste polo shirts
http://www.superpolos.com/ : cheap Lacoste polo shirts
http://www.shoesbuying.com/ : 2009 nike shoes
http://www.shoesbuying.com/2009-nike-shoes-c-153.htm : new nike shoes
http://www.shoesbuying.com/air-max-classic-bw-c-185.htm : Women's max
http://www.shoesbuying.com/mens-max-93-c-188.htm : Men's max 93
http://www.shoesbuying.com/nike-shox-c-165.htm : nike shox
http://www.shoesbuying.com/nike-force-c-174.htm : Nike air force
http://www.shoesbuying.com/nike-2003-c-181.htm : Nike air max 2003
http://www.shoesbuying.com/nike-air-max-ltd-c-166.htm : nike air max ltd
http://www.shoesbuying.com/nike-air-max-tn-c-167.htm : nike air max tn
http://www.shoesbuying.com/nike-rift-shoes-c-162.htm : Nike air rift
http://www.shoesbuying.com/nike-yeezy-c-184.htm : Nike air Yeezy
http://www.shoesbuying.com/nike-airmax-c-154.htm : nike airmax
http://www.shoesbuying.com/nike-airmax-c-155.htm : Nike air max 90
http://www.shoesbuying.com/nike-airmax-c-157.htm : Nike air max 97
http://www.shoesbuying.com/nike-birds-nest-shoes-c-152.htm : nike birds nest shoes
http://www.shoesbuying.com/nike-dunk-high-c-177.htm : nike dunk
http://www.shoesbuying.com/nike-shoes-c-168.htm : nike RT1 shoes
http://www.shoesbuying.com/nike-sb-c-180.htm : nike SB
http://www.shoesbuying.com/nike-shox-shoes-c-156.htm : nike shox shoes
http://www.shoesbuying.com/nike-shox-shoes-c-158.htm : Nike shox OZ shoes
http://www.shoesbuying.com/nike-shox-shoes-c-159.htm : Nike shox R2 shoes
http://www.shoesbuying.com/nike-shox-shoes-c-160.htm : Nike shox R3 shoes
http://www.shoesbuying.com/nike-shox-shoes-c-163.htm : Nike shox R4 shoes
http://www.shoesbuying.com/nike-shox-shoes-c-164.htm : Nike shox R5 shoes
http://www.shoesbuying.com/nike-shox-c-171.htm : Nike shox TL3
http://www.shoesbuying.com/nike-trainers-lovers-c-118.htm : nike trainers lovers

http://www.racketsoffer.com/ : tennis rackets
http://www.racketsoffer.com/wilson-racquets-c-19/ : Wilson tennis rackets
http://www.racketsoffer.com/head-racquets-c-18/ : HEAD tennis rackets
http://www.racketsoffer.com/babolat-racquets-c-17/ : Babolat tennis rackets
 
Dear friend !
1.We can offer all kinds of wholesale watch. We offer safe delivery and best service,low price and good quality.
2.We have excellent customer service team, which could solve online various problems about wholesale watches we provide in 24 hours.
3.We have perfect Logistics system, which guarantees all the ordered replica watches are delivered to you in good shape as fast as possible.Communicate with us.You will be very satisfied! Hope we can do long-term business in near future.



Rolex Watches
Omega Watches
Tissot Watches
Panerai Watches
Piaget-Watches
Zenith Watches
Baume Mercier Watches
Bvlgari Watches
Concord Watches
Corum Watches
Longines Watches
Louis Vuitton Watches
Mont Blanc Watches
Oris Watches
Rado Watches

Raymond Weil Watches
Tag Heuer Watches
A Lange & Sohne Watches
Piguet Watches
Bregue Watches
Dior Watches
Gucci Watches
Jaeger LeCoultre Watches
Patek Philippe Watches
Vacheron Constantin Watches
Breitling Watches
Cartier Watches
Chopard Watches
IWC Watches

Best regards! Thank you !
 
Hello, everybody. I am a new hand to be here. So nice to meet you all. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
 
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!
 
Your blog is so excellent that I like it very much, you must be good at writing. And what do you think of cheap polo men t shirts, I think they are are very chic, especially the cheap polo t shirts, I love them very much.
polo t shirts
discount polo t shirts
polo t shirts on sale
polo t shirts cheap
cheap polo mens shirts
polo t shirts women
polo t shirts for women
polo shirts on sale
discount polo shirts
men's polo t shirt
polo men t shirts
 
Your blog is so excellent that I like it very much, you must be good at writing. And what do you think of cheap polo men t shirts, I think they are are very chic, especially the cheap polo t shirts, I love them very much.
polo t shirts
discount polo t shirts
polo t shirts on sale
polo t shirts cheap
cheap polo mens shirts
polo t shirts women
polo t shirts for women
polo shirts on sale
discount polo shirts
men's polo t shirt
polo men t shirts
 
dsquareddsquared
dsquared shoesdsquared shoes
dsquared jeansdsquared jeans
dsquared jeans 2011dsquared jeans 2011
dsquared shoes 2011dsquared shoes 2011
dsquared jeans womendsquared jeans women
asics shoes saleasics shoes sale
asics shoesasics shoes
asics running shoesasics running shoes
asics mexico 66asics mexico 66
onitsuka mexico 66onitsuka mexico 66
asics onitsuka mexico 66asics onitsuka mexico 66
asics whizzerasics whizzer
asics japanasics japan
asics ultimate 81asics ultimate 81
onitsuka coolidgeonitsuka coolidge
rotation 77rotation 77
asics topasics top
asics miniasics mini
asics olymposasics olympos
onitsuka kanuchionitsuka kanuchi
timberland bootstimberland boots
christian louboutin shoeschristian louboutin shoes
karen millen dresseskaren millen dresses
 
The christian louboutin store was designed for the ladies. Since the louboutin heelswas born, the ladies life become colorful.The elegant pattern, the delicate style all mold the ladies perfect leg profile. The christian louboutin evening pumps are the god's masterwork. Who invited the christian louboutin pumps? Seldom people knew, but I think every lady would be grateful for him. Among the countless pumps, the christian louboutin peep toe is the most outstanding ones. Flowers in the spring of 2011 creeping, up from hair to clothes continue to footwear, have had a brilliant up. In such a glamor, spring and summer flowers now here. Romance is a woman's mood, exquisite flowers just right of expression in our gestures, the woman, how can we not love the romantic temperament so that they distributed the flowers do? 2011 flowers bloom will enjoy different poses! The Christian Louboutin 2011 Sandals also can adds the hright of the ladies, it bring surprise to the short lady. Especially the red sole of the louboutin heels, magic and sexy, many ladies are crazy. The red sole, the first feature of the Christian Louboutin heels.
 
Christian Louboutin Outlet Online Store offer 2011 new-style Christian Louboutin Sale Outlet online store.We will provide you with all kinds of CL.The Christian Louboutin Ankle Boots serve different functions and keep your feet stylish and warm,Christian Louboutin High Heels can always show our body in the best charming way, making her legs look as long as possible.Christian Louboutin New Style are the best choice for you to choose the brand, especially you want to keep steps to the fashion world.Christian Louboutin Peep Toes that are considered as classic due to the fact they are comfortable.Buy cheap Christian Louboutin Outlet Online shoes, get your favorite.
http://www.christianlouboutinoutletonline.org
 
You wish for getting to create people nowadays worried about women? can you need a awesome coloring as women? We are Louboutin 2011store. we are able to provide you using a broad wide variety of Sale Louboutin. For example:Christian Louboutin Slingbacks,Christian Louboutin Peep Toe,Christian Louboutin Evenings,Christian Louboutin Pumps. andChristian Louboutin Mensis a very distinctive design, its design reflects its style. You put on it, that means you have its style.You put on it, that means you have its style.They are astonishingly psychological color. Please do not pass up an superb chance.
http://www.sale-louboutin-shoes.us
 
So how is the Nike mercurial soccer cleats changed? the original Nike Speed boot!New products Nike Football Boots for us.the new Vapor football boots have undergone the same new paint work as the Nike Mercurial Superfly boots and now feature the Nike Football Boots updated asymmetric.
 
Post a Comment

<< Home

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