Home > SQL Server, T-SQL > Fast Row Count for All Tables

Fast Row Count for All Tables


Using Count(*) to obtain a row count can be very slow on large tables. This is the only truly reliable way to obtain an accurate row count but, post SQL Server 2000, the mess that was sys.indexes, etc. was largely cleaned up and nowadays the row count can be obtained using the following code. Microsoft still states that the row count in sys.partitions is an “Approximate number of rows in this partition”, but it appears to be stable and accurate with appropriate caveats with regards to uncommitted transactions.


Select      s.Name
           ,Sum(p.rows) As Rows
From        sys.tables t
Inner Join  sys.schemas s On (s.schema_id = t.schema_id)
Inner Join  sys.indexes i On (i.object_id = t.object_id and i.index_id < 2)
Inner Join  sys.partitions p On (p.object_id = t.object_id)
Where       p.index_id In (0, 1)
Group By    s.Name
Order By    s.name
Categories: SQL Server, T-SQL Tags: , , ,
  1. No comments yet.
  1. No trackbacks yet.

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 )

Facebook photo

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

Connecting to %s

%d bloggers like this: