T-SQL counts

  • Samith C

    Mr or Mrs. 500

    Points: 519

    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]

  • Ron McCullough

    SSC Guru

    Points: 63877

    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: 996444

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

  • vk-kirov

    SSCertifiable

    Points: 7686

    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.

  • Hany Helmy

    SSChampion

    Points: 13488

    Easy.

  • ksatpute123

    Hall of Fame

    Points: 3325

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

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    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

    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

    Copyright (c) 1988-2005 Microsoft Corporation

    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

    Nice easy one thanks.

    Hope this helps...

    Ford Fairlane
    Rock and Roll Detective

  • Toreador

    SSChampion

    Points: 11252

    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

    Good Question!!! Learn something....

  • This was removed by the editor as SPAM

  • tom.w.brannon

    Hall of Fame

    Points: 3956

    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: 286979

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

  • tabinsc

    SSCommitted

    Points: 1812

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

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

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

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