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


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!


 

Comments (8)

  1. Gabe says:

    Can’t remember the last time I needed to count the number of total rows in a table. I often need to count the number of rows in a subset of the table. I doesn’t appear you can use the technique to count only certain rows. Am I wrong?

  2. So why doesn’t SQL Server automatically do this?

  3. Why doesn’t SQL Server already do this?

  4. CoqBlog says:

    Irena Kennedy nous donne une m&#233;thode plus sympathique que le COUNT(*) (j’aurais plut&#244;t mit un COUNT sur…

  5. richardguk says:

    Wow. Where’s the catch? Or, if there’s no catch, why doesn’t COUNT(*) use this shortcut anyway?

  6. Xied75 says:

    As BOL states under sys.partitions :

    rows: Approximate number of rows in this partition.

    So, it’s not accurate always.

  7. white_mage says:

    With SQS 2000, you can do this….

    SELECT SUM(rows) NbRows

    FROM sysindexes

    WHERE OBJECT_NAME(id) LIKE ‘tableName’

    Exactly the same result….