March 28, 2012 at 11:21 pm
Hi,
Just as curiosity i executed following query
select count(*),count(1),count(-1),count(100) form Mytable
its completed execution successfully with same result for all :w00t:
So can anyone explain how all these count versions are working?
PS: I have only 10 columns are available in MyTable
March 28, 2012 at 11:40 pm
My understanding is that it doesn't matter whether you count all columns (*), a single column or a scalar value, the count returned is always the number of rows in the table (constrained by any WHERE clause of course).
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 28, 2012 at 11:44 pm
You are lucky because your columns did not contain any NULLs; that's why you are seeing the same result for all the columns!
Try this examples to know how COUNT(*) and COUNT(column_name) can vary depending the data contained in the columns.
IF OBJECT_ID('TempDB..#Temp') IS NOT NULL
DROP TABLE #Temp;
CREATE TABLE #Temp
(
ColA VARCHAR(2)
,ColB VARCHAR(2)
,ColC VARCHAR(2)
);
-- Test data set 1
-- the last 2 columns does contains NULL
-- now look at the COUNTs and you ll know the difference
INSERT INTO #Temp (ColA,ColB,ColC)
SELECT 'A' , 'A' , 'A'
UNION ALL SELECT 'B' , '' , NULL
UNION ALL SELECT 'C' , NULL , NULL
UNION ALL SELECT 'D' , 'D' , 'C'
;
SELECT Star = COUNT (*)
,[Count 1] = COUNT(-1)
,[Count -1] = COUNT(1)
,[Count 100] = COUNT(100)
,ColA = COUNT(T.ColA)
,ColB = COUNT(T.ColB)
,ColC = COUNT(T.ColC)
FROM #Temp T ;
TRUNCATE TABLE #Temp ;
-- Test data set 2
INSERT INTO #Temp (ColA,ColB,ColC)
SELECT 'A' , 'A' , 'A'
UNION ALL SELECT 'B' , 'B' , 'B'
UNION ALL SELECT 'C' , 'C', 'C'
UNION ALL SELECT 'D' , 'D' , 'D'
;
SELECT Star = COUNT (*)
,[Count 1] = COUNT(-1)
,[Count -1] = COUNT(1)
,[Count 100] = COUNT(100)
,ColA = COUNT(T.ColA)
,ColB = COUNT(T.ColB)
,ColC = COUNT(T.ColC)
FROM #Temp T ;
{Edited sample for better examples}
{Edited afte Sean pointed erorr in the statement}
March 28, 2012 at 11:46 pm
CRAP Reply, please ignore
March 28, 2012 at 11:56 pm
dwain.c (3/28/2012)
My understanding is that it doesn't matter whether you count all columns (*), a single column or a scalar value, the count returned is always the number of rows in the table (constrained by any WHERE clause of course).
COUNT(*) or COUNT(scalar number) will give you the same results ; but COUNT(ColumnName) will yeild result based on the values in that column! You can check my post above to understand more!
The thing behind is, for COUNT(*) and COUNT(1) uses COUNT(*) stream aggregate; like
<StreamAggregate>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1007" />
<ScalarOperator ScalarString="Count(*)">
<Aggregate AggType="countstar" Distinct="false" />
</ScalarOperator>
</DefinedValue>
</DefinedValues>
But Count(column) is diffrent; like this
<DefinedValue>
<ColumnReference Column="Expr1008" />
<ScalarOperator ScalarString="COUNT([tempdb].[dbo].[#Temp].[Count 1] as [T].[Count 1])">
<Aggregate AggType="COUNT_BIG" Distinct="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Temp]" Alias="[T]" Column="Count 1" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
Thats the difference!
March 29, 2012 at 1:02 am
ColdCoffee:
So I think what you're saying (I am not an XML Plan-master, nor even a Paduan) is that COUNT(column_name) returns the number of rows that have non-NULL values? I think I knew that but had forgotten for some reason:w00t:.
Just out of curiosity, finding such a thing rather inconceivable, suppose a row in the table contains NULL in every column. Is that row counted by COUNT(*)?
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 29, 2012 at 1:08 am
dwain.c (3/29/2012)
ColdCoffee:So I think what you're saying (I am not an XML Plan-master, nor even a Paduan) is that COUNT(column_name) returns the number of rows that have non-NULL values? I think I knew that but had forgotten for some reason:w00t:.
Exactly
Just out of curiosity, finding such a thing rather inconceivable, suppose a row in the table contains NULL in every column. Is that row counted by COUNT(*)?
Yes it would; * means "All" .. thus, any committed row in a table will be output to COUNT(*).
March 29, 2012 at 7:55 am
Try this examples to know how COUNT(*) and COUNT(1) can vary depending the data contained in the columns.
You have me a bit confused by this. Count(*) and Count(1) will always return the number of rows regardless of the data contained in any of the columns. I think you meant to say that Count(*) and Count(ColumnName) can vary?
I just wanted to clarify because it sounds like dwain.c might be a little confused at this point.
Just to demonstrate I took your sample query and change the data a little so we have a null in all of the columns and even included a row where every single column is null.
Count(*) and Count(1) will return the number of rows.
IF OBJECT_ID('TempDB..#Temp') IS NOT NULL
DROP TABLE #Temp;
CREATE TABLE #Temp
(
ColA VARCHAR(2)
,ColB VARCHAR(2)
,ColC VARCHAR(2)
);
-- Test data set 1
-- the last 2 columns does contains NULL
-- now look at the COUNTs and you ll know the difference
INSERT INTO #Temp (ColA,ColB,ColC)
SELECT 'A' , 'A' , 'A'
UNION ALL SELECT 'B' , '' , NULL
UNION ALL SELECT 'C' , NULL , NULL
UNION ALL SELECT Null, 'D' , 'C'
union all select Null, Null, null
;
SELECT Star = COUNT (*)
,[Count 1] = COUNT(-1)
FROM #Temp T ;
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 29, 2012 at 9:31 am
Sean Lange (3/29/2012)
Try this examples to know how COUNT(*) and COUNT(1) can vary depending the data contained in the columns.
You have me a bit confused by this. Count(*) and Count(1) will always return the number of rows regardless of the data contained in any of the columns. I think you meant to say that Count(*) and Count(ColumnName) can vary?
I meant to say "Count(*) and Count(ColumnName) can vary"; i have gone and changed my post. Infact in my follow-up post with XML exec plan, u can see i have mentioned that correctly. Thanks for pointing it out Sean
March 29, 2012 at 9:40 am
Interestingly, this yields 0 for a table that contains no null values and also uses COUNT(*) as the StreamAggregate operator. (It is also once noted by Paul White, but i could not get the post)
SELECT [Cast Null] = COUNT (CAST (NULL AS INT))
FROM #Temp
March 29, 2012 at 9:46 am
ColdCoffee (3/29/2012)
Interestingly, this yields 0 for a table that contains no null values and also uses COUNT(*) as the StreamAggregate operator. (It is also once noted by Paul White, but i could not get the post)
SELECT [Cast Null] = COUNT (CAST (NULL AS INT))FROM #Temp
It actually makes total sense. When counting a column that contains a null, the rows with null are not included in the count. This makes sense because NULL is nothing so there is nothing to count. In the above you have still said count null but in this case you have to cast the constant null to a datatype. This same behavior works regardless of the datatype you cast null to. In effect it is the same as counting a column with a null value. In other words, if NULL values are excluded from the count function it would only make sense to exclude them even if the value is hard coded instead of coming from a column.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 29, 2012 at 6:27 pm
I just wanted to clarify because it sounds like dwain.c might be a little confused at this point.
Actually after ColdCoffee's prior post, I was no longer confused. I was willing to take him at his word but it is always nice to see someone taking the time and making the effort to prove the point.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 29, 2012 at 6:34 pm
dwain.c (3/29/2012)
I just wanted to clarify because it sounds like dwain.c might be a little confused at this point.
Actually after ColdCoffee's prior post, I was no longer confused. I was willing to take him at his word but it is always nice to see someone taking the time and making the effort to prove the point.
Wow, that's a nice gesture, Dwain. Thanks! 🙂
March 29, 2012 at 8:09 pm
dwain.c (3/29/2012)
I just wanted to clarify because it sounds like dwain.c might be a little confused at this point.
Actually after ColdCoffee's prior post, I was no longer confused. I was willing to take him at his word but it is always nice to see someone taking the time and making the effort to prove the point.
Thanks from me too. You managed to toss two nice compliments into one short post. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 30, 2012 at 7:20 am
Count (*) , (1) or (-1) counts all rows even if there are NULL values in the table
Insert the Column Name in the Brackets (Col1) then it will only count rows with values in, and not count nulls.
For example
Simple Table Named count
Col1Col2Col3
1 1 1
2 NULL1
3 3 NULL
NULL4 4
select count (*) From [Count] (Returns 4)
select count (1) From [Count] (Returns 4)
select count (-1) From [Count] (Returns 4)
select count (Col1) From [Count] (Returns 3)
select count (Col1) WithName From [Count] (Returns 3 and will insert the column name WithName)
select count (Col2) as withName2 From [Count] (Returns 3 and will insert the column name withNam2)
Hope this is Clear and helps.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply