May 9, 2011 at 2:35 am
Can someone tell me the difference b/w below queries.
'select * from db.table_name'
'select count(*) from db.table_name'
Here, the count of the records are varying. Basically, running this on a linked server.
Any reason why we see the difference in count values. Kindly share your inputs.
M&M
May 9, 2011 at 3:54 am
Count(*) counts all the rows in the result set. Select * returns all columns for the rows in the resultset.
If the count is changing it's probably there are inserts and deletes happening against the destination table.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 9, 2011 at 4:09 am
GilaMonster (5/9/2011)
Count(*) counts all the rows in the result set. Select * returns all columns for the rows in the resultset.If the count is changing it's probably there are inserts and deletes happening against the destination table.
in select *, will it be possible that some rows are excluded due to NULL values etc.
M&M
May 9, 2011 at 4:14 am
mohammed moinudheen (5/9/2011)
in select *, will it be possible that some rows are excluded due to NULL values etc.
Absolutely not. If there's no where clause on the query, select * from tbl will return all columns, all rows. The presence of nulls changes nothing.
Likewise Count(*) counts all rows, regardless of null values in the resultset.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 9, 2011 at 11:01 am
Shouldn't SELECT COUNT(1) be the preffered method here over (*)? In larger tables I've noticed drastic performance improvements by choosing only 1 column...
Also, linked servers can be sporadically "quirky"...Do you get the same results when you run your COUNT and SELECT on the destination box locally as when you run it via an OPENQUERY?
To echo what Gail said about changing data
If the count is changing it's probably there are inserts and deletes happening against the destination table.
try throwing a WITH(READUNCOMMITTED) at the end of your queries and see if your numbers change...it could very well be off because of changing data
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
May 9, 2011 at 11:26 am
MyDoggieJessie (5/9/2011)
Shouldn't SELECT COUNT(1) be the preffered method here over (*)? In larger tables I've noticed drastic performance improvements by choosing only 1 column...
No. The * in count(*) does not mean all columns, it simply means to count the rows without caring about their values. Count(*) and Count(1) are completely synonymous.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 9, 2011 at 11:37 am
GilaMonster (5/9/2011)
MyDoggieJessie (5/9/2011)
Shouldn't SELECT COUNT(1) be the preffered method here over (*)? In larger tables I've noticed drastic performance improvements by choosing only 1 column...No. The * in count(*) does not mean all columns, it simply means to count the rows without caring about their values. Count(*) and Count(1) are completely synonymous.
Just to pile on more proof...
Run this and look at the xml plan generated :
SELECT COUNT(*) FROM sys.columns
UNION ALL
SELECT COUNT(1) FROM sys.columns
UNION ALL
SELECT COUNT('Whatever string') FROM sys.columns
The plan comes up with the same operations for all 3 queries :
<ScalarOperator ScalarString="Count(*)">
<Aggregate AggType="countstar" Distinct="false" />
</ScalarOperator>
May 9, 2011 at 11:43 am
Yes Jessie, selecting one column will always be faster than selecting the entire table.
select * from Mytable vs select [hello] from Mytable will return back results quicker. 🙂
May 9, 2011 at 11:45 am
I've got to say I'm really shocked at some of the stuff people have said here around a very basic subject. (select * excluding nulls, count(1) and count(*) being different etc etc) Fair play the OP doesn't know and that fine but guys, pls do some research at least before you post
May 9, 2011 at 11:45 am
stormsentinelcammy (5/9/2011)
Yes Jessie, selecting one column will always be faster than selecting the entire table.select * from Mytable vs select [hello] from Mytable will return back results quicker. 🙂
I'm not sure you guys are talking about the same thing. Jess is talking about count(1) vs count(*).
Obviously doing less work usually takes less time :w00t:.
May 9, 2011 at 11:47 am
Take a gander at the post, its a count(). What you've said is obviously correct but wasnt the question that was asked 🙂
May 9, 2011 at 11:52 am
Dave Morrison (5/9/2011)
Take a gander at the post, its a count(). What you've said is obviously correct but wasnt the question that was asked 🙂
Who are you talking to? Not sure if this is for me...
May 9, 2011 at 11:53 am
Dave Morrison (5/9/2011)
Take a gander at the post, its a count(). What you've said is obviously correct but wasnt the question that was asked 🙂
No, but he wasn't trying to answer the OP's question. He was proving the answer to Jess's question "Shouldn't SELECT COUNT(1) be the preffered method here over (*)?"
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 9, 2011 at 11:57 am
Yeah sorry quoted the wrong post, my bad 🙂
May 9, 2011 at 11:57 am
Dave Morrison (5/9/2011)
Yeah sorry quoted the wrong post, my bad 🙂
How could you have quoted the wrong post when you quoted no post?... sounds like a null debate to me :w00t:.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply