Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Best Practice : coalesce vs. isnull? Expand / Collapse
Author
Message
Posted Friday, December 11, 2009 12:22 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, November 14, 2012 4:59 AM
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

Thanx for any explanation, link, or you opinion
Post #832742
Posted Friday, December 11, 2009 2:21 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 10:29 AM
Points: 1,223, Visits: 9,618
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.
Post #832769
Posted Friday, December 11, 2009 7:12 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:22 PM
Points: 11,148, Visits: 12,889
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

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
Post #832914
Posted Friday, December 11, 2009 7:43 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #832948
Posted Sunday, December 13, 2009 9:07 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:42 AM
Points: 11,192, Visits: 11,089
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #833604
Posted Monday, December 14, 2009 8:26 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, December 5, 2012 7:23 AM
Points: 304, 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.
Post #833869
Posted Monday, December 14, 2009 8:29 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #833872
Posted Monday, December 14, 2009 8:30 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, December 5, 2012 7:23 AM
Points: 304, Visits: 457
To each their own.
Post #833876
Posted Monday, December 14, 2009 8:37 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:22 PM
Points: 11,148, Visits: 12,889
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

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
Post #833883
Posted Monday, December 14, 2009 8:58 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, December 5, 2012 7:23 AM
Points: 304, 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.
Post #833905
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse