How To Get Table Row Counts Quickly And Painlessly

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Kendal Van Dyke » How To Get Table Row Counts Quickly And...

108 posts, Page 2 of 11

««1 2 3 4 5 »»»

How To Get Table Row Counts Quickly And Painlessly

Rate Topic

Display Mode

Topic Options

Author

Message

matt stockham

Posted Wednesday, September 02, 2009 8:58 AM

Right there with Babe

Group: General Forum Members
Last Login: Yesterday @ 10:27 AM
Points: 750, Visits: 2,938

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

jvrakesh-858370

Posted Wednesday, September 02, 2009 9:05 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, April 09, 2012 9:16 AM
Points: 33, Visits: 31

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

Jeff Moden

Posted Wednesday, September 02, 2009 9:53 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:36 PM
Points: 32,931, Visits: 26,820

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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #781606

Reginald J Ray Jr

Posted Wednesday, September 02, 2009 10:05 AM

Grasshopper

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

sqlservercentral-879774

Posted Wednesday, September 02, 2009 11:19 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, September 03, 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

Steve-524674

Posted Wednesday, September 02, 2009 12:15 PM

Grasshopper

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

jvrakesh-858370

Posted Wednesday, September 02, 2009 1:00 PM

SSC Rookie

Group: General Forum Members
Last Login: Monday, April 09, 2012 9:16 AM
Points: 33, Visits: 31

It worked .

Post #781742

ansleypm

Posted Wednesday, September 02, 2009 2:40 PM

Grasshopper

Group: General Forum Members
Last Login: Monday, January 28, 2013 4:42 PM
Points: 16, Visits: 27

I thought

SELECT count(1)
FROM mytable
WITH (nolock)

was the preferred method, and certainly easier to remember

Post #781816

bvbellomo

Posted Wednesday, September 02, 2009 2:40 PM

Old 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

wbrianwhite

Posted Wednesday, September 02, 2009 3:12 PM

SSC 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 »

108 posts, Page 2 of 11

««1 2 3 4 5 »»»

Permissions