Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Best Practice : coalesce vs. isnull?


Best Practice : coalesce vs. isnull?

Author
Message
bambang
bambang
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 Visits: 143
Just wondering,
I like to use ISNULL(column_name, 0) while my friend like to use COALESCE(column_name, 0).
We both try using ISNULL and COALESCE in our query and there is no different result.

In Books online, it's said that both behave differently. There's also the explanation, but I just don't
understand it completely :p

Is there any article regarding this topic?
And which one you usually use (ISNULL or COALESCE) and why you use it?

For now, I'll keep my habit using ISNULL, so will my friend keep using COALESCE :-D

Thanx for any explanation, link, or you opinion :-)
HowardW
HowardW
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1211 Visits: 9892
Prior to SQL 2008 ISNULL was considerably faster than COALESCE. This makes sense as the code behind ISNULL has to deal with a fixed number of input variables (2) and COALESCE has to be designed to work with any number of variables, so this will invariably involve extra computations.

In SQL 2008, I've seen a thread where people say that the performance is now for all intent and purpose the same for both (I wonder if the optimiser just converts it to ISNULL if there are only 2 variables).

Personally, if I'm only working with two values, then I'll use ISNULL and if I need more I'll use COALESCE in the spirit in which the two functions were designed.
Jack Corbett
  Jack Corbett
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11014 Visits: 14858
Howard has provided a pretty good explanation. Use ISNULL when you only have 2 option, COALESCE with more than 2. They also handle data types and lengths differently. Try this:

SELECT  
ISNULL(Nullif('abc', 'abc'), '123456') AS using_isnull,
COALESCE(Nullif('abc', 'abc'), '123456') AS using_coalesce,
ISNULL(Nullif('abc', 'abc'), 123456) AS int_using_isnull,
COALESCE(Nullif('abc', 'abc'), 123456) AS int_using_coalesce





Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14369 Visits: 9729
Since I prefer as much consistency as possible, I try to stick to Coalesce. There are times when I can't use IsNull and have to use Coalesce, but there aren't times when I have to use IsNull and can't use Coalesce. So, I try to use Coalesce.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Paul White
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10338 Visits: 11350
Just to expand on Jack's point a bit, because it is an important one:

COALESCE( expression [ ,...n ] ) returns the data type of the expression with the highest data type precedence.
ISNULL(check_expression, replacement_value) returns the same type as check_expression.

So:


SELECT ISNULL(CAST(NULL AS INT), 5.5) -- Returns 5
SELECT COALESCE(CAST(NULL AS INT), 5.5) -- Returns 5.5

SELECT DATALENGTH(ISNULL(CAST(NULL AS VARCHAR(5)), N'Hello')) -- Returns 5
SELECT DATALENGTH(COALESCE(CAST(NULL AS VARCHAR(5)), N'Hello')) -- Returns 10





Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
BaldingLoopMan
BaldingLoopMan
Old Hand
Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)

Group: General Forum Members
Points: 312 Visits: 457
actually u can use isnull in all situations if u wanted to. A coalesce is a nested isnull.

coalesce(custname, last, first) = isnull( isnull( custname, last ), first )

Therefore a coalesce is basically a nested isnull.
GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14369 Visits: 9729
BaldingLoopMan (12/14/2009)
actually u can use isnull in all situations if u wanted to. A coalesce is a nested isnull.

coalesce(custname, last, first) = isnull( isnull( custname, last ), first )

Therefore a coalesce is basically a nested isnull.


True. Would be a pain to type out, be essentially unreadable, and I'd hate to have to maintain something that nested 5 or 10 deep, but it could be done.

I'll still stick with Coalesce.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
BaldingLoopMan
BaldingLoopMan
Old Hand
Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)

Group: General Forum Members
Points: 312 Visits: 457
To each their own.
Jack Corbett
  Jack Corbett
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11014 Visits: 14858
BaldingLoopMan (12/14/2009)
actually u can use isnull in all situations if u wanted to. A coalesce is a nested isnull.

coalesce(custname, last, first) = isnull( isnull( custname, last ), first )

Therefore a coalesce is basically a nested isnull.


That is not totally accurate as noted by the posts by myself and Paul White where you can see the IsNull and Coalesce functions are different. If you run this query:


DECLARE @test TABLE (colA CHAR(3))

INSERT INTO @test (
   colA
)
SELECT
'abc'
UNION ALL
SELECT
NULL

SELECT
ISNULL(colA, '123456') AS using_isnull,
COALESCE(colA, '123456') AS using_coalesce
FROM
@test



and look at the exection plan you will see that SQL Server keeps the IsNull function for ISNULL in Compute Scalar operator, but converts the COALESCE column to a CASE statement.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
BaldingLoopMan
BaldingLoopMan
Old Hand
Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)

Group: General Forum Members
Points: 312 Visits: 457
Yea. i didn't follow what u guys were saying before.


I see what your saying now. Wow. That's crazy and good to know.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search