SYSK 144: A Faster Way to Get a Total Number of Rows in a Table

  • Article

How many times you wanted to know the number of rows in a large table before doing some operations, but had to wait for some time till the usual

SELECT COUNT(*) FROM <tablename>

returns a result?

 

With SQL 2005, there is an alternate way to do the same using sys.partitions which stores the count of rows in the column ROWS for the table, index and partitions.

SELECT OBJECT_NAME(object_id) TableName, rows FROM sys.partitions WHERE OBJECT_NAME(object_id) = <tablename>

 

Here are few things to note:

-   The value of column Index_ID is 0 if there is no index in the table

-   The value of column Index_ID is 1 if there is a clustered index in the table

 

There could be more than one row in the sys.partitions table under these circumstances

1. If there is one or more Non Clustered Index

2. If there is a partition in the table

 

Here is an example to see the difference in cost for getting the no. of rows from a table having 142115 rows.

 

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

SET STATISTICS TIME ON

SET STATISTICS IO ON

--Method-I Use sys.partitions

SELECT

         OBJECT_NAME(object_id) TableName

        ,SUM(Rows) NoOfRows --total up if there is a partition

FROM sys.partitions

WHERE index_id < 2 --ignore the partitions from the non-clustered index if any

AND OBJECT_NAME(object_id) IN (‘YourTableName') --Restrict the Table Names

GROUP BY object_id

--Statistics IO

Scan count 1, logical reads 2, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

--Statistics Time

SQL Server Execution Times:

   CPU time = 0 ms, elapsed time = 28 ms

--Method-II --commonly used query use COUNT(*)

SELECT COUNT(*) FROM YourTableName

--Statistics IO

Scan count 1, logical reads 2286, physical reads 0, read-ahead reads 2285, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

--Statistics Time

SQL Server Execution Times:

   CPU time = 60 ms, elapsed time = 761 ms.

SET STATISTICS TIME OFF

SET STATISTICS IO OFF

 

Mark the difference, which is huge in terms of the IO cost and CPU time as well. So next time there is a need to check the no. of rows hit sys.partitions instead of using COUNT(*).

 

Special thanks for Balaji Mishra for this tip!