COALESCE

Question

COALESCE

PChiragS

SSCarpal Tunnel

Points: 4965
More actions
June 1, 2013 at 2:51 pm

#278436

Comments posted to this topic are about the item COALESCE
June 3, 2013 at 8:55 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 29 total)

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

TomThomson SSC Guru Points: 104773 More actions · Answer 1

I vastly prefer this to the previous QOTD. It's straightforward, nice clear and accurate explanation, and doesn't make my brain hurt.

Tom

Lokesh Vij SSChampion Points: 10836 More actions · Answer 2

An easy one Chirag with nice explanation. Thanks!

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

kapil_kk SSC-Insane Points: 21316 More actions · Answer 3

Good question chirag 🙂

thanks

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

sqlnaive SSCoach Points: 17437 More actions · Answer 4

Good question at the start of week. To check "the catch" i the question helped.

Thanks Chirag. 🙂

seatedElephant Say Hey Kid Points: 674 More actions · Answer 5

Thanks. Good question.

Dave Morris :alien:

"Measure twice, saw once"

Dana Medley SSCertifiable Points: 6764 More actions · Answer 6

Thank you for the great question and clear answer.

Everything is awesome!

Michael Poppers SSCrazy Points: 2149 More actions · Answer 7

Michael Poppers

SSCrazy

Points: 2149

June 3, 2013 at 7:53 am

#1620554

Thanks for the question.

NBSteve Hall of Fame Points: 3989 More actions · Answer 8

For anybody like myself who was still a bit unclear, it may help to think of COALESCE as having 2 separate aspects. It needs to return a value, and it needs to return a type. The rule for returning a value is simple enough... it returns the first non-NULL value. The rule for returning a type, however, is based on the highest data type precedence which means it may come from a different expression than the value comes from.

To illustrate this, consider these two examples:

COALESCE(NULL, 1, 'A')

COALESCE(NULL, 'A', 1)

The value returned by COALESCE comes from the first non-NULL expression, 1 in the first case, 'A' in the second case. However, the type returned comes from data type precedence, and since int is a higher precedence than varchar, both (try to) return an int type. Of course, trying to return 'A' as an int type throws an error, meaning the first statement will work, and the second will not.

Great question, thanks!

Ken SSCoach Points: 17191 More actions · Answer 9

Nice clear question Chirag! Thanks for an easy one to start off the week with.

Revenant SSC-Forever Points: 42467 More actions · Answer 10

Revenant

SSC-Forever

Points: 42467

June 3, 2013 at 9:57 am

#1620635

Thanks for this nice question, Chirag!

sknox SSChampion Points: 12388 More actions · Answer 11

Good question! I would clarify the answer a little bit, though:

... the result returns the data type of the expression with the highest data type precedence.

should probably be

... the result returns the data type of the non-NULL expression with the highest data type precedence.

This is why rows 1 and 2 pass -- even though zip is an int as defined by the column, its data type is not considered in these rows because it is NULL.

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 12

sknox (6/3/2013)
Good question! I would clarify the answer a little bit, though:
... the result returns the data type of the expression with the highest data type precedence.
should probably be
... the result returns the data type of the non-NULL expression with the highest data type precedence.
This is why rows 1 and 2 pass -- even though zip is an int as defined by the column, its data type is not considered in these rows because it is NULL.

No, that is incorrect. The explanation in the question is correct, yours is wrong. The data type is always the same. Has to be, because the data type of the column in the result set is part of the meta data for the result set as a whole, not for individual rows.

The first row passes because the first non-NULL value is already int, so no conversion needed.

The second row passes because the first non-NULL value is the string '1'. Converstion to int succeeds.

The third row fails because the first non-NULL value is the string 'a2', which cannot be converted to int.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

TomThomson SSC Guru Points: 104773 More actions · Answer 13

sknox (6/3/2013)
Good question! I would clarify the answer a little bit, though:
... the result returns the data type of the expression with the highest data type precedence.
should probably be
... the result returns the data type of the non-NULL expression with the highest data type precedence.
This is why rows 1 and 2 pass -- even though zip is an int as defined by the column, its data type is not considered in these rows because it is NULL.

No, it certainly shouldn't. The original explanation is correct. Your version is incorrect.

The types of any NULL arguments to coalesce and considered along with the types of the non-null arguments when determining what type the result must be.

The argument zip is considered every time, when determining the highest precedence type, whether it's null or not. If the third row considered had been (null','a2',null) instead of (null,'a2',111) we would have had exactly the same failure on exactly the same row for exactly the same reason as with the values in the question.

Don't forget that in SQL NULLs have type; a NULL value in an int column or for an int variable has type int, as of course does the constant expression cast(NULL as int).

Back in January I had a QotD aiming to help people learn how typed nulls and coalesce interact:Coalesce and Conversion[/url], it may help you to look at that.

Tom