RMJCS Logo
 

Quick Row Count

 
 

The simple way to find out how many rows are in a table is to execute SELECT COUNT(*) FROM MyTable. Whilst this is fine for small tables, and is the only way to get a transactionally consistent result, it uses a table scan behind the scenes which may take some time and cause un-wanted IO on very large tables.

Some times a 'reasonable' figure is sufficient, in which case there is an alternative approach. For SQL Server 2005 onwards, the sys.dm_db_partition_stats DMV contains the row_count column that will provide this information. The figure is only reasonable, rather than transactionally consistent, because it is not updated after every INSERT or DELETE statement.

For earlier versions of SQL Server a similar result can be achieved using the sysindexes system table. The KB article "INF: Description of sysindexes and sp_spaceused" discusses sysindexes and explains why for performance reasons sysindexes is not updated until a checkpoint occurs, hence the rowcnt figure is not guaranteed.

 
     
 

Quick Row Count, SQL Server 2005+

 
 
--
-- Get row counts (per partition) for all user tables in the 
-- current database without scanning table data.
--
select
   quotename(s.name) + N'.' + quotename(o.name) as [Table Name], 
   p.partition_number as [Partition Number],
   ps.row_count as [Row Count]
from 
   sys.dm_db_partition_stats ps 
      inner join sys.objects o on o.object_id = ps.object_id
	     inner join sys.schemas s on s.schema_id = o.schema_id 
      inner join sys.partitions p on p.partition_id = ps.partition_id
where 
   ps.index_id in (0,1)
   and o.type = 'U'
order by 
   s.name, o.name, p.partition_number
 
     
 

Quick Row Count, SQL Server 2000

 
 
--
-- Get row counts for all user tables in the 
-- current database without scanning table data.
--
select 
   quotename(su.name) + N'.' + quotename(so.name) as [Table Name],
   si.rowcnt as [Row Count]
from
   dbo.sysindexes si
      inner join dbo.sysobjects so on so.id = si.id
         inner join dbo.sysusers su on so.uid = su.uid
where 
   si.indid in (0,1) 
   and so.type = 'U'
order by 
   su.name, so.name
 
     
Copyright 2007-2018 RMJCS