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.
SET STATISTICS TIME ON
SET STATISTICS IO ON
--Method-I Use sys.partitions
,SUM(Rows) NoOfRows --total up if there is a partition
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
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.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 28 ms
--Method-II --commonly used query use COUNT(*)
SELECT COUNT(*) FROM YourTableName
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.
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!