T-SQL counts

Question

Post reply

T-SQL counts

Samith C

Mr or Mrs. 500

Points: 519
More actions
October 1, 2013 at 8:12 pm

#298428

Comments posted to this topic are about the item T-SQL counts
[font="Verdana"] There is no Wrong time to do a Right thing 🙂 [/font]
October 2, 2013 at 4:53 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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Nice simple question

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Jeff Moden SSC Guru Points: 1004311 More actions · Answer 2

Actually, this is going to cause a mess for me when we upgrade from 2005 because NULL is considered to be a DISTINCT value. For 2005, the correct answer is "4". For 2008 and above, the correct answer is 3. The 2008+ version works as I would expect because just a simple COUNT(ID) would ignore the NULL. In this particular case, that would still return a 4 because even though the "2" is duplicated, there are 4 non-null values.

The reason why it's going to make a mess for me is because a lot of the developers used it fully expecting NULL to be considered a DISTINCT value. This is one of those changes in how SQL Server works that could produce some serious undetected problems if you built your code in 2005 and you upgrade later.

Not sure what happened but last night but I can't duplicate the problem.

--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)

vk-kirov SSCertifiable Points: 7686 More actions · Answer 3

Jeff Moden (10/1/2013)
For 2005, the correct answer is "4". For 2008 and above, the correct answer is 3.

I'm not sure which build of 2005 are you using, but I've just checked the script and got ~~"4"~~ "3" on all SQL Server verions/builds available to me (even on 2000 and 2005):

Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)

May 3 2005 23:18:38

Developer Edition on Windows NT 6.1 (Build 7601: Service Pack 1)

Microsoft SQL Server 2005 - 9.00.4053.00 (Intel IA-64)

May 26 2009 14:15:40

Developer Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

Microsoft SQL Server 2008 R2 (SP1) - 10.50.2550.0 (X64)

Jun 11 2012 16:41:53

Copyright (c) Microsoft Corporation

Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)

Dec 28 2012 20:23:12

Copyright (c) Microsoft Corporation

Enterprise Evaluation Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Edit: "4" crossed out, "3" written in.

Hany Helmy SSChampion Points: 13921 More actions · Answer 4

Easy.

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

ksatpute123 Hall of Fame Points: 3325 More actions · Answer 5

Count returns count of unique non null values. Easy one.

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

Easy one, thanks.

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

Dscheypie SSCommitted Points: 1545 More actions · Answer 7

Jeff Moden (10/1/2013)
Actually, this is going to cause a mess for me when we upgrade from 2005 because NULL is considered to be a DISTINCT value. For 2005, the correct answer is "4". For 2008 and above, the correct answer is 3. The 2008+ version works as I would expect because just a simple COUNT(ID) would ignore the NULL. In this particular case, that would still return a 4 because even though the "2" is duplicated, there are 4 non-null values.
The reason why it's going to make a mess for me is because a lot of the developers used it fully expecting NULL to be considered a DISTINCT value. This is one of those changes in how SQL Server works that could produce some serious undetected problems if you built your code in 2005 and you upgrade later.

vk-kirov (10/1/2013)
Jeff Moden (10/1/2013)
For 2005, the correct answer is "4". For 2008 and above, the correct answer is 3.
I'm not sure which build of 2005 are you using, but I've just checked the script and got ~~"4"~~ "3" on all SQL Server verions/builds available to me (even on 2000 and 2005):
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 6.1 (Build 7601: Service Pack 1)
Microsoft SQL Server 2005 - 9.00.4053.00 (Intel IA-64)
May 26 2009 14:15:40
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
Microsoft SQL Server 2008 R2 (SP1) - 10.50.2550.0 (X64)
Jun 11 2012 16:41:53
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
Dec 28 2012 20:23:12
Copyright (c) Microsoft Corporation
Enterprise Evaluation Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
Edit: "4" crossed out, "3" written in.

Hi, Jeff,

I checked that, too, on version 9.00.5057 Enterprise Edition.

Microsoft SQL Server 2005 - 9.00.5057.00 (X64)

Mar 25 2011 13:33:31

Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

I got also 3 distinct values. And thought I was working with this behaviour at least since the 80 version. Have you an idea where the difference could be?

Best regards

Jens-Peter

Edit: Added quote of vk-kirov. +1 for you, vk-kirov!

________________________________________________________
If you set out to do something, something else must be done first.

Ford Fairlane SSCertifiable Points: 7664 More actions · Answer 8

Nice easy one thanks.

Hope this helps...

Ford Fairlane
Rock and Roll Detective

Toreador SSChampion Points: 11365 More actions · Answer 9

Jeff Moden (10/1/2013)
Actually, this is going to cause a mess for me when we upgrade from 2005 because NULL is considered to be a DISTINCT value.

I suspect you may be getting mixed up with something else, as the function has always behaved this way (and gives a 'useful' warning message about null values being eliminated, which rarely fails to confuse people into thinking their code has produced an error).

Mike Hays SSCommitted Points: 1871 More actions · Answer 10

Mike Hays

SSCommitted

Points: 1871

October 2, 2013 at 4:44 am

#1655171

Good Question!!! Learn something....

tom.w.brannon Hall of Fame Points: 3976 More actions · Answer 11

I believe the problem is a difference in how distinct gets used in different contexts. For the following queries

select count(distinct id) from #temp_test;

select distinct id from #temp_test;

select count(*) from (select distinct id from #temp_test) a;

the results are 3, 4 rows returned, and 4. So NULL is a distinct value but not counted as a distinct value. Maybe somebody else can answer why this makes sense.

Ed Wagner SSC Guru Points: 287026 More actions · Answer 12

Simple questions are good sometimes. I always wonder if there's a hidden "gotcha" in there, though.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

tabinsc SSCommitted Points: 1812 More actions · Answer 13

Good QOTD. I think of NULL not as "no value" but as "I don't know".

Tony
------------------------------------
Are you suggesting coconuts migrate?