Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

How To Get Table Row Counts Quickly And Painlessly Expand / Collapse
Author
Message
Posted Wednesday, September 2, 2009 8:58 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 6:34 PM
Points: 750, Visits: 3,159
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).
Post #781551
Posted Wednesday, September 2, 2009 9:05 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, June 26, 2014 9:28 AM
Points: 33, Visits: 35
I ran count(*) and got the count as 1048912 and when I ran sp_spaceused I get 1048613 , the count doesnt match . Any Comments?
Post #781560
Posted Wednesday, September 2, 2009 9:53 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:47 PM
Points: 35,215, Visits: 31,667
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #781606
Posted Wednesday, September 2, 2009 10:05 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 21, 2013 8:12 AM
Points: 17, 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 :^)...
Post #781616
Posted Wednesday, September 2, 2009 11:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 3, 2009 5:34 AM
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.
Post #781654
Posted Wednesday, September 2, 2009 12:15 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 22, 2011 1:46 PM
Points: 16, Visits: 114
If an index is available, it WILL use it [though not always the primary key].
Post #781705
Posted Wednesday, September 2, 2009 1:00 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, June 26, 2014 9:28 AM
Points: 33, Visits: 35
It worked .
Post #781742
Posted Wednesday, September 2, 2009 2:40 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 12, 2014 10:36 AM
Points: 16, Visits: 34
I thought

SELECT count(1)
FROM mytable
WITH (nolock)

was the preferred method, and certainly easier to remember



Post #781816
Posted Wednesday, September 2, 2009 2:40 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 20, 2011 10:32 AM
Points: 341, Visits: 2,079
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.
Post #781817
Posted Wednesday, September 2, 2009 3:12 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 24, 2013 12:02 PM
Points: 42, 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."
Post #781836
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse