Counting records can be a long process, even disturbing other processes from performing well. There is an alternative that is way faster. It does not count the records itself, but it uses the partition information that every stored object has. This has nothing to do with database or table partitioning, the term partition is overloaded. Each database object, such as a table, view, index, etc., uses storage space, and this storage space is referred to as a partition. A system view shows us the row count of all tables, and also the way they are compressed (a feature that is available in SQL Server 2008 and above):
/* Determine row count and compression of tables.
Compression is only in SQL Server Developer and Enterprise editions.*/
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
T.type, T.type_desc, P.index_id, P.rows, P.data_compression_desc
FROM sys.tables AS T
JOIN sys.partitions AS P
ON T.object_id = P.object_id AND P.index_id < 2 — See note below
— WHERE T.name LIKE ‘%’
Note: P.index_id Indicates the ID of the index within the
object to which this partition belongs.
0 = heap (= unindexed physical table)
1 = clustered index (= physical table)
2 or greater = nonclustered index
Although the Micorosft documentation says that it’s an approximate number, I have yet to encounter an incorrect number of records used by this method. And the result is there in a split second! Very nice indeed.