Count Records Fast

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

 

SELECT T.name,
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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s