Faster Way to Get Count of Rows in a Table

The most common way that I see people get a count of rows in a table in SQL Server 2005 is SELECT COUNT(*) FROM dbo.TableName WITH (NOLOCK). This works, but is expensive from a logical IO perspective. One alternative is to query sys.partitions to get the same information.  Below are results for the two methods on a table with 43 million rows of data:

— Common Way
SELECT COUNT(*) FROM dbo.TableName WITH (NOLOCK)
— Table ‘TableName’. Scan count 1, logical reads 193614, physical reads 0, read-ahead reads 55, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
— Cost 100%
— Time 8-9 seconds

— Faster Way
SELECT SUM(Rows) AS Count
FROM sys.partitions
WHERE index_id < 2
AND OBJECT_NAME(object_id) = ‘TableName’
— Table ‘sysrowsets’. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
— Cost 0%
— nearly instantaneous

Technorati Tag: SQL Server