Monday, August 27, 2012

SQL Server - retrieving record count instantaneously

So here it is, the first one of these not-so-original knowledge, but I didn't know until I needed it today.

In SQL Server it may take a long time to simply return a total record count using select count (*) from YourTable as the engine may decide to perform a scan instead of a seek.

Among other places this page helped me with a solution that retrieves the record count of a table instantaneously:

SQL 2005 or later
select sum (spart.rows)
from sys.partitions spart
where spart.object_id = object_id('YourTable')
and spart.index_id < 2

SQL 2000
select max(ROWS)
from sysindexes
where id = object_id('YourTable')

No comments: