The COUNT Function in T-SQL

  • Igor Micev

    SSC-Dedicated

    Points: 33109

    Comments posted to this topic are about the item The COUNT Function in T-SQL

    Igor Micev,
    My blog: www.igormicev.com

  • NParry

    Valued Member

    Points: 55

    Just tried these out, and this:

    /*(9)*/ SELECT DISTINCT(COUNT(1)) FROM #TmpCounts; -- 1 row

    Actually returns 6. This is on SQL Server 2008.

  • Igor Micev

    SSC-Dedicated

    Points: 33109

    NParry (7/12/2016)


    Just tried these out, and this:

    /*(9)*/ SELECT DISTINCT(COUNT(1)) FROM #TmpCounts; -- 1 row

    Actually returns 6. This is on SQL Server 2008.

    6 is the result from the COUNT(1) and then DISTINCT(). Only 1 row is returned with a value 6.

    Igor Micev,
    My blog: www.igormicev.com

  • pmadhavapeddi22

    SSCertifiable

    Points: 5306

    /*(1)*/ SELECT COUNT(*) FROM #TmpCounts; -- 6 rows

    /*(2)*/ SELECT COUNT(1) FROM #TmpCounts; -- 6 rows

    /*(3)*/ SELECT COUNT(Column1) FROM #TmpCounts; -- 5 rows

    /*(4)*/ SELECT COUNT(ALL Column1) FROM #TmpCounts; -- 5 rows

    /*(5)*/ SELECT COUNT(DISTINCT 1) FROM #TmpCounts; -- 1 row

    /*(6)*/ SELECT COUNT(DISTINCT Column1) FROM #TmpCounts; -- 4 rows

    /*(7)*/ SELECT COUNT('T') FROM #TmpCounts; --6 rows

    /*(8)*/ SELECT COUNT(CONVERT(int, NULL)) FROM #TmpCounts; -- 0 rows

    /*(9)*/ SELECT DISTINCT(COUNT(1)) FROM #TmpCounts; -- 1 row

    for all of the above sql statement one row is returned with a value.. count returns single row value.. i am not sure why "rows" are mentioned in the comments.

  • Igor Micev

    SSC-Dedicated

    Points: 33109

    pmadhavapeddi22 (7/12/2016)


    /*(1)*/ SELECT COUNT(*) FROM #TmpCounts; -- 6 rows

    /*(2)*/ SELECT COUNT(1) FROM #TmpCounts; -- 6 rows

    /*(3)*/ SELECT COUNT(Column1) FROM #TmpCounts; -- 5 rows

    /*(4)*/ SELECT COUNT(ALL Column1) FROM #TmpCounts; -- 5 rows

    /*(5)*/ SELECT COUNT(DISTINCT 1) FROM #TmpCounts; -- 1 row

    /*(6)*/ SELECT COUNT(DISTINCT Column1) FROM #TmpCounts; -- 4 rows

    /*(7)*/ SELECT COUNT('T') FROM #TmpCounts; --6 rows

    /*(8)*/ SELECT COUNT(CONVERT(int, NULL)) FROM #TmpCounts; -- 0 rows

    /*(9)*/ SELECT DISTINCT(COUNT(1)) FROM #TmpCounts; -- 1 row

    for all of the above sql statement one row is returned with a value.. count returns single row value.. i am not sure why "rows" are mentioned in the comments.

    Yes. In the comments it should write "rows counted".

    Thanks for the remark!

    Igor Micev,
    My blog: www.igormicev.com

  • Karl Klingler

    SSCertifiable

    Points: 5868

    Hello Igor,

    in the article you pose the question "Which Is Faster: COUNT(*) or COUNT(1)?", but actually you answer with "COUNT(*) versus COUNT(ID)" (which is not the same as count(1))

    Is there something missing in the article or did you just get the same results for COUNT(1) and COUNT(ID)?

    regards

    karl

    Best regards
    karl

  • Igor Micev

    SSC-Dedicated

    Points: 33109

    Karl Klingler (7/12/2016)


    Hello Igor,

    in the article you pose the question "Which Is Faster: COUNT(*) or COUNT(1)?", but actually you answer with "COUNT(*) versus COUNT(ID)" (which is not the same as count(1))

    Is there something missing in the article or did you just get the same results for COUNT(1) and COUNT(ID)?

    regards

    karl

    Hello Karl,

    Your remark is in place. Thanks.

    Actually it's the same, because ID is the PK of the demo table. COUNT(1) and COUNT(ID) perform the same here. I'll consider this to correct in the text.

    Igor Micev,
    My blog: www.igormicev.com

  • x

    SSC-Insane

    Points: 23485

    An ORDER BY clause can be used explicitly in the end as well. In that case the COUNT is not deterministic. See Deterministic and Nondeterministic Functions for more.

    but the page you referenced has

    For example, specifying an ORDER BY clause in a query does not change the determinism of a function that used in that query.

    Just wondering about your description of deterministic, it doesn't seem to make sense.

    edit:

    another snippet from the web page might shed some light on this:

    Functions that call extended stored procedures are nondeterministic, because the extended stored procedures can cause side effects on the database.

    I think a simple (maybe naïve) definition of "deterministic" could be that given the same input, the function will return the same output, but your writeup doesn't even match this. Heck strictly speaking, count(<whatever>) isn't even behaving as a function and I wouldn't even call it one. Maybe call it an aggregate or something catchy like that.

  • Igor Micev

    SSC-Dedicated

    Points: 33109

    patrickmcginnis59 10839 (7/12/2016)


    An ORDER BY clause can be used explicitly in the end as well. In that case the COUNT is not deterministic. See Deterministic and Nondeterministic Functions for more.

    but the page you referenced has

    For example, specifying an ORDER BY clause in a query does not change the determinism of a function that used in that query.

    Just wondering about your description of deterministic, it doesn't seem to make sense.

    edit:

    another snippet from the web page might shed some light on this:

    Functions that call extended stored procedures are nondeterministic, because the extended stored procedures can cause side effects on the database.

    I think a simple (maybe naïve) definition of "deterministic" could be that given the same input, the function will return the same output, but your writeup doesn't even match this. Heck strictly speaking, count(<whatever>) isn't even behaving as a function and I wouldn't even call it one. Maybe call it an aggregate or something catchy like that.

    Please read carefully the reference. The aggregate functions are not deterministic when used with Over and Order By.

    Igor Micev,
    My blog: www.igormicev.com

  • John Bigler

    Old Hand

    Points: 399

    Hi Igor,

    In the What Index is Used When Using COUNT? section of the article, you write, "If the table has no indexes (hash) ...". I believe that should be a table heap, and not the word hash.

    Regards,

    John B.

  • x

    SSC-Insane

    Points: 23485

    Igor Micev (7/12/2016)


    patrickmcginnis59 10839 (7/12/2016)


    An ORDER BY clause can be used explicitly in the end as well. In that case the COUNT is not deterministic. See Deterministic and Nondeterministic Functions for more.

    but the page you referenced has

    For example, specifying an ORDER BY clause in a query does not change the determinism of a function that used in that query.

    Just wondering about your description of deterministic, it doesn't seem to make sense.

    edit:

    another snippet from the web page might shed some light on this:

    Functions that call extended stored procedures are nondeterministic, because the extended stored procedures can cause side effects on the database.

    I think a simple (maybe naïve) definition of "deterministic" could be that given the same input, the function will return the same output, but your writeup doesn't even match this. Heck strictly speaking, count(<whatever>) isn't even behaving as a function and I wouldn't even call it one. Maybe call it an aggregate or something catchy like that.

    Please read carefully the reference. The aggregate functions are not deterministic when used with Over and Order By.

    Yes, I do see it now and I stand corrected. I still don't see why, but the pages back you up.

    edit: while this page doesn't address count, it does talk about a few different definitions of "deterministic".

    http://dba.stackexchange.com/questions/77639/are-rank-and-dense-rank-deterministic-or-non-deterministic

  • Igor Micev

    SSC-Dedicated

    Points: 33109

    John Bigler (7/12/2016)


    Hi Igor,

    In the What Index is Used When Using COUNT? section of the article, you write, "If the table has no indexes (hash) ...". I believe that should be a table heap, and not the word hash.

    Regards,

    John B.

    Correct! Thanks, l'll correct it in the text.

    Igor Micev,
    My blog: www.igormicev.com

  • wasay_76

    SSC-Addicted

    Points: 457

    Query 9 is returning 6 rows instead of one

    /*(9)*/ SELECT DISTINCT(COUNT(1)) FROM #TmpCounts; -- 1 row

  • Igor Micev

    SSC-Dedicated

    Points: 33109

    wasay_76 (7/12/2016)


    Query 9 is returning 6 rows instead of one

    /*(9)*/ SELECT DISTINCT(COUNT(1)) FROM #TmpCounts; -- 1 row

    Already noted. It counts 6 rows. The Distinct returns one row. I'll try to make this clearer.

    Thanks.

    Igor Micev,
    My blog: www.igormicev.com

  • ken.trock

    SSCertifiable

    Points: 5147

    Warning: Null value is eliminated by an aggregate or other SET operation.

    Good demo and explanation of this warning. I don't know how many times I've seen this and blown right thru it 😀

    Ken

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

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