Share via

SQL Server–HOW-TO: quickly retrieve accurate row count for table

  • 07/15/2010

Recently, I’ve been involved in a very interesting project in which we need to perform operations on a table containing 3,000,000,000+ rows. For some tooling, I needed a quick and reliable way to count the number of rows contained within this table. Performing a simple

SELECT COUNT(*) FROM Transactions

operation would do the trick on small tables with low IO, but what’s the ‘best’ way (quick and reliable) to perform this operation on large tables?

I searched and found different answers, which I note here so it might be of use to someone… (My table was called ‘Transactions’)

Index

Query

Comment

1

SELECT COUNT(*) FROM Transactions

Performs a full table scan. Slow on large tables.

2

SELECT CONVERT(bigint, rows)

FROM sysindexes

WHERE id = OBJECT_ID('Transactions')

AND indid < 2

Fast way to retrieve row count. Depends on statistics and is inaccurate.

Run DBCC UPDATEUSAGE(Database) WITH COUNT_ROWS, which can take significant time for large tables.

3

SELECT CAST(p.rows AS float)

FROM sys.tables AS tbl

INNER JOIN sys.indexes AS idx ON idx.object_id = tbl.object_id and idx.index_id < 2

INNER JOIN sys.partitions AS p ON p.object_id=CAST(tbl.object_id AS int)

AND p.index_id=idx.index_id

WHERE ((tbl.name=N'Transactions'

AND SCHEMA_NAME(tbl.schema_id)='dbo'))

The way the SQL management studio counts rows (look at table properties, storage, row count). Very fast, but still an approximate number of rows.

4

SELECT SUM (row_count)

FROM sys.dm_db_partition_stats

WHERE object_id=OBJECT_ID('Transactions')   

AND (index_id=0 or index_id=1);

Quick (although not as fast as method 2) operation and equally important, reliable.

Comments

  • Anonymous
    August 03, 2010
    Nice Post thank you very much

  • Anonymous
    July 25, 2011
    EXACTLY what I was after!. We have a few tables with 20+ billion rows and I need to ascertain how many inserts we are getting daily. Solution 4 worked a treat. Thanks

  • Anonymous
    September 12, 2011
    Thanks a lot

  • Anonymous
    January 23, 2012
    Great stuff, thanks!  Went for option 4.  Any idea on how reliable "reliable" is in this context?  100%, or are there some exceptions?

  • Anonymous
    May 25, 2013
    Much needed basic information.Well explained !!..Thanks .

  • Anonymous
    February 01, 2014
    Comment for Number 1 is wrong. SQL Server just needs to scan the leaf level of the smallest non-clustered index, which can be very fast.

  • Anonymous
    June 19, 2014
    Very cool - thanks... This helps a lot

  • Anonymous
    July 29, 2014
    Very nice query thanks