COALESCE ?? Replacement?

Question

COALESCE ?? Replacement?

reymon26

SSC Journeyman

Points: 86
More actions
November 25, 2010 at 5:39 am

#241738

is there an updated approach on how to use the COALESCE keyword?
Or if there is a NEW keyword that will replace COALESCE on SQL 2005/2003?
thank you

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

No and no. Coalesce has not been replaced, there's no new keyword for it. It's still there and usable in SQL 2005/2008

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

reymon26 SSC Journeyman Points: 86 More actions · Answer 2

what i mean is that , are ther other new functions or function that works just like the use of a coalesce?

grahamc SSCertifiable Points: 6762 More actions · Answer 3

maybe we would be able to better assist you if you told us why. What are you trying do? COALESCE does what it says and does it well. Improper use of the COALESCE statement in a WHERE statement for example has negative "side-effects".

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

reymon26 (11/25/2010)
what i mean is that , are ther other new functions or function that works just like the use of a coalesce?

Why? What are you trying to do?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 5

isNull is similar, but what is your issue with coalesce ?

Clear Sky SQL
My Blog[/url]

Nevyn SSChampion Points: 14210 More actions · Answer 6

Nevyn

SSChampion

Points: 14210

November 26, 2010 at 12:55 pm

#1255883

This sounds a lot like a homework / test question

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

Nevyn (11/26/2010)
This sounds a lot like a homework / test question

Or an interview question.

I have to say that it's a bit of a trick question and that most people would probably pick ISNULL as the answer. But ISNULL only takes two operands where COALESCE takes 2 OR MORE operands. That would make ISNULL the wrong choice. Most folks can easily figure out what the correct answer is on their own and just in case this is homework or and interview question, I'll leave it up to the OP to discover it... 😛 Especially since I just gave the answer. :hehe:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)