Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How To Get Table Row Counts Quickly And Painlessly


How To Get Table Row Counts Quickly And Painlessly

Author
Message
matt stockham
matt stockham
SSC Eights!
SSC Eights! (810 reputation)SSC Eights! (810 reputation)SSC Eights! (810 reputation)SSC Eights! (810 reputation)SSC Eights! (810 reputation)SSC Eights! (810 reputation)SSC Eights! (810 reputation)SSC Eights! (810 reputation)

Group: General Forum Members
Points: 810 Visits: 3178
rja.carnegie (9/2/2009)
Why doesn't "SELECT COUNT(*) FROM table" look at an index, which apparently it doesn't(?)


It should pick the smallest index (by page reads).
jvrakesh-858370
jvrakesh-858370
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 44
I ran count(*) and got the count as 1048912 and when I ran sp_spaceused I get 1048613 , the count doesnt match . Any Comments?
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: General Forum Members
Points: 51641 Visits: 40308
jvrakesh (9/2/2009)
I ran count(*) and got the count as 1048912 and when I ran sp_spaceused I get 1048613 , the count doesnt match . Any Comments?


Yes... see my previous post (2nd post in this thread).

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Reginald J Ray Jr
Reginald J Ray Jr
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 55
I had the same problem, different counts from sp_spaceused versus select count(*)

So I tried sp_spaceused with @updateusage = 'True', and I also ran DBCC UPDATEUSAGE for the table.

And the numbers never varied. My only thought is possibly a corrupt table.

Luckily I have little need for record counts from large tables :^)...
sqlservercentral-879774
sqlservercentral-879774
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 8
I find it baffling that the query planner can't provide a way to do count(*) quickly in the common case. It does so many other clever things; this one seems fairly straightforward to implement by comparison, and there's obviously a need for it, as the existence of this article shows.
Steve-524674
Steve-524674
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 114
If an index is available, it WILL use it [though not always the primary key].
jvrakesh-858370
jvrakesh-858370
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 44
It worked .
ansleypm
ansleypm
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 34
I thought

SELECT count(1)
FROM mytable
WITH (nolock)

was the preferred method, and certainly easier to remember



bvbellomo
bvbellomo
Old Hand
Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)

Group: General Forum Members
Points: 341 Visits: 2079
I remember seeing this, and ending up switching back to count(*), but I cannot remember why - it may have been for accuracy problems, and I am not sure which version of SQL I was using at the time (I know it was 2000 or 2005). I also wonder if Microsoft would have the query engine do a table/index scan if it doesn't really have to - select count(*) FROM '1 table' is a relatively common operation. My gut tells me if it is doing a scan, that is because it actually *needs to* do a scan. Of course if you don't care if you occasionally see 4,815,162,341 rows when there are actually 4,815,162,342 rows, then this is a good trick to know.
wbrianwhite
wbrianwhite
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 152
I had always heard that you should do a "select count(1) from table".

http://www.techonthenet.com/sql/count.php

"TIP: Performance Tuning

Since the COUNT function will return the same results regardless of what NOT NULL field(s) you include as the COUNT function parameters (ie: within the brackets), you can change the syntax of the COUNT function to COUNT(1) to get better performance as the database engine will not have to fetch back the data fields."
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search