Freigeben über

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

  • Artikel

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

  • Anonymous
    June 26, 2006
    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?
  • Anonymous
    June 26, 2006
    So why doesn't SQL Server automatically do this?
  • Anonymous
    June 26, 2006
    Why doesn't SQL Server already do this?
  • Anonymous
    June 26, 2006
    Irena Kennedy nous donne une m&#233;thode plus sympathique que le COUNT(*) (j'aurais plut&#244;t mit un COUNT sur...
  • Anonymous
    June 26, 2006
    Wow. Where's the catch? Or, if there's no catch, why doesn't COUNT(*) use this shortcut anyway?
  • Anonymous
    June 27, 2006
    As BOL states under sys.partitions :

    rows: Approximate number of rows in this partition.

    So, it's not accurate always.
  • Anonymous
    June 28, 2006
    With SQS 2000, you can do this....

    SELECT SUM(rows) NbRows
    FROM sysindexes
    WHERE OBJECT_NAME(id) LIKE 'tableName'

    Exactly the same result....