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.








--Method-I Use sys.partitions


         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(*)




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






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

Skip to main content