July 11, 2016 at 11:12 pm
Comments posted to this topic are about the item The COUNT Function in T-SQL
Igor Micev,My blog: www.igormicev.com
July 12, 2016 at 2:48 am
Just tried these out, and this:
/*(9)*/ SELECT DISTINCT(COUNT(1)) FROM #TmpCounts; -- 1 row
Actually returns 6. This is on SQL Server 2008.
July 12, 2016 at 3:53 am
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
July 12, 2016 at 4:33 am
/*(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.
July 12, 2016 at 5:02 am
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
July 12, 2016 at 5:31 am
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
July 12, 2016 at 5:40 am
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
July 12, 2016 at 6:10 am
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.
July 12, 2016 at 6:29 am
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
July 12, 2016 at 6:42 am
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.
July 12, 2016 at 7:00 am
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".
July 12, 2016 at 7:09 am
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
July 12, 2016 at 7:20 am
Query 9 is returning 6 rows instead of one
/*(9)*/ SELECT DISTINCT(COUNT(1)) FROM #TmpCounts; -- 1 row
July 12, 2016 at 7:56 am
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
July 12, 2016 at 8:47 am
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 24 total)
You must be logged in to reply to this topic. Login to reply