COUNT, COALESCE, ISNULL, DISTINCT

Question

COUNT, COALESCE, ISNULL, DISTINCT

Viewing 9 posts - 16 through 23 (of 23 total)

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

Ford Fairlane SSCertifiable Points: 7664 More actions · Answer 1

raulggonzalez (11/7/2013)
Thanks for the question!
Considering that it states "choose 3 answers" and there was no name "JAMES", for me was pretty clear that the author assumed a case insensitive scenario.

+1 good question. thanks

Hope this helps...

Ford Fairlane
Rock and Roll Detective

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

I worked it out in my head, got it right, then ran the query on my system and got a different result: error in query. On my system NAME and Name are two different columns, because the server collation is case sensitive. But I am used to that - many QotD authors never test their questions on case sensitive servers. When making the necessary changes to make the code run, I noticed that two of the COALESCE functions use NULL instead of Name - a fact I had overlooked when working it out in my head (that luckily did not affect the result). Was this intentional??

The case insensitivity of the data was not an issue for me - the "select three" made it obvious that James = JAMES in this question.

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/

Hany Helmy SSChampion Points: 13921 More actions · Answer 3

raulggonzalez (11/7/2013)
Considering that it states "choose 3 answers" and there was no name "JAMES", for me was pretty clear that the author assumed a case insensitive scenario.

True; as the server default is CI.

Good question, thanks.

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

Dhruvesh Shah SSCrazy Points: 2303 More actions · Answer 4

Phew that took me about 10 minutes to do calculation on my mind.

Great question.

Marcia J SSCertifiable Points: 5649 More actions · Answer 5

Marcia J

SSCertifiable

Points: 5649

November 11, 2013 at 11:34 am

#1665542

I like questions like this that make me think.

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 6

Nice question, thanks.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Toreador SSChampion Points: 11358 More actions · Answer 7

Hugo Kornelis (11/9/2013)
On my system NAME and Name are two different columns, because the server collation is case sensitive.

Really surprised nobody else has picked up on this, instead questioning the case sensitivity of the data. On my server (case sensitive collation), the correct answer was an error. But since we had to choose 3 answers, I made the correct assumption and got it right 🙂

marlon.seton SSCrazy Points: 2623 More actions · Answer 8

murray-906152 (11/6/2013)
Steve,
you state 'NOTE: You will notice that James was case insenstive (COLLATE SQL_Latin1_General_CP1_CI_AS )' in your answer but this is very much site specific.
(I answered on the basis that you were assuming a case insensitive environment.)
If the database one was playing in was defined with collation SQL_Latin1_General_CP1_CS_AS (for instance), as inherited from the model database on creation, then the results would be difference up front.
To avoid ambiguity, you could have forced the collation on table creation with:
CREATE TABLE TEST ( NAME VARCHAR(20) COLLATE SQL_Latin1_General_CP1_CI_AS);
Regards, Murray.

All our databases are defined as SQL_Latin1_General_CP1_CS_AS so I certainly wouldn't have got the 'correct' answer if I'd run the code.

psingla Hall of Fame Points: 3840 More actions · Answer 9

Good Question ,,Thanks Steve

Pramod
SQL Server DBA | MCSE SQL Server 2012/2014
in.linkedin.com/in/pramodsingla/
http://pramodsingla.wordpress.com/