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 «««7891011»»»

How To Get Table Row Counts Quickly And Painlessly Expand / Collapse
Author
Message
Posted Tuesday, February 9, 2010 3:51 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 12:47 AM
Points: 9,928, Visits: 11,205
bijayanix24 (2/9/2010)
...

'Team Mindfire' might like to read the other comments in this discussion. Much better solutions exist.




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #862312
Posted Thursday, January 27, 2011 10:38 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 6, 2012 10:54 AM
Points: 22, Visits: 5
Can anyone one told me what will be the shortest and fastest way of find the
count of table data

i am using

SELECT COUNT(*) FROM TblEmployee -- i want only count not else

what can i do for that
Post #1055080
Posted Thursday, January 27, 2011 10:56 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, December 15, 2014 9:37 AM
Points: 1,016, Visits: 1,052
U can Use below sql query to count the record from any user table
select rows
from sys.sysindexes
where id = object_id('Table_Name')
and indid < 2
Post #1055085
Posted Friday, January 28, 2011 12:07 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, May 23, 2014 5:59 AM
Points: 329, Visits: 470
If you use versions prior to 2005, you need to use dbcc updateusage to get accurate count
http://beyondrelational.com/blogs/madhivanan/archive/2007/11/02/different-ways-to-count-rows-from-a-table.aspx




Madhivanan

Failing to plan is Planning to fail
Post #1055111
Posted Friday, January 28, 2011 2:22 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 9:25 AM
Points: 953, Visits: 2,626
Since SQL 2005 I've tended to use Sys.Partitions over a Select Count(*), especially when I need to get row numbers across all tables in a specified DB and also across numerous DB's.

However, in future I will be adding the code to Update Stats prior to running the process, just to be sure the counts are as acurate as possible, may be with a 10-20 second delay.

My question is, would the Recovery Model for the DB impact the accuracy of the sys.Partitions and sys.dm_db_partition_stats views?

TIA.


_________________________________________________________________________
SSC Guide to Posting and Best Practices
Post #1055161
Posted Friday, January 28, 2011 2:48 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, December 12, 2014 7:15 AM
Points: 990, Visits: 819
rja.carnegie (9/2/2009)
I find the report of DBCC CHECKDB useful.

Why doesn't "SELECT COUNT(*) FROM table" look at an index, which apparently it doesn't(?)


For 'select count(*) from [table]' the 2008 optimiser will scan the PK of the table if it has one. Not as good as (some of) the DMV queries, but not as bad as a full table scan.
Post #1055176
Posted Friday, January 28, 2011 6:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 3, 2011 12:45 PM
Points: 1, Visits: 3
Intresting post but not so much useful. Most of the time we need to know the count agaist with some filtered criteria. For me, I never come across the requirment like, show the count of table.
Anyway, good to know the way if required to get the total count of table.
Post #1055262
Posted Friday, January 28, 2011 6:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 2, 2011 6:24 AM
Points: 1, Visits: 13
I agree. I too am curious under what circumstances it would be useful to know the row-count from all tables.

Also under what circumstances is it useful to use 6 or 7 lines of query versus a single SELECT statement.

As a peek into the guts of the SQL Server system and thinking behind it, this is fascinating, but seems it would rarely be of use.
Post #1055296
Posted Friday, January 28, 2011 7:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 4, 2012 6:39 PM
Points: 7, Visits: 165
I agree with previous posters that this seems like a good way to find row counts for all tables in a database, but not a very good (easy, efficient, etc...) alternative to COUNT(*) on a single table within a query. I created the following proc and tested with my largest tables - hundreds of millions of rows with a non-sequential GUID pk and the results were generally 1 sec for COUNT(*) and 0 sec for the proc. Considering how I use COUNT(*) in my logic (and it is rarely used), it doesn't seem worth it - merely a nifty way to complicate things.

CREATE PROC spUtil_GetRowCount
(
@TableName VARCHAR(200)
)
AS

SELECT
ddps.row_count
FROM
sys.indexes i
INNER JOIN sys.objects o
ON i.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.dm_db_partition_stats ddps
ON i.OBJECT_ID = ddps.OBJECT_ID
AND i.index_id = ddps.index_id
WHERE
i.index_id < 2
AND o.is_ms_shipped = 0
AND o.name = @TableName
Post #1055333
Posted Friday, January 28, 2011 8:21 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, October 17, 2014 1:30 PM
Points: 22, Visits: 344
Nice article, but for SQL 2005 / 8 / r2, I just right-click and run the table usage report from sms. It also gives space used/free.
Post #1055344
« Prev Topic | Next Topic »

Add to briefcase «««7891011»»»

Permissions Expand / Collapse