SQL Server: Quickly get row counts for tables, heaps, indexes, and partitions
Written by Ian Stirk   
Thursday, 07 November 2013

How do you quickly get a count of the number of rows in a table? This small utility will enable you to quickly get row counts for tables, heaps, indexes and partitions. It works with SQL Server 2005 and higher.


There are various reasons why you might need to know the row count of various database structures (tables etc), including:


  • Determine if an application has loaded data

  • Estimating how long a query might take to run

  • Estimating how long update statistics might take to run

  • Estimating how long create index might take to run

  • Deciding why a query plan has chosen a particular join type



As tables grow, maintenance often becomes a problem. Splitting (partitioning) a table horizontally, can be a solution – enabling you to perform maintenance at the partition level. An added benefit is query performance may be improved too.

A word about nomenclature, a table whose rows are ordered is also called a clustered index; a table whose rows are unordered is called a heap; non-clustered indexes are additional indexes; and partitions are tables or indexes whose data has been split horizontally. Tables that are not partitioned, have only one partition, and its value is 1.

Sometimes I use


to obtain the number of rows in a table, however for large tables, this can take a long time to run.

Previously, I’ve used the rowcount column in the sys.sysindexes view, to quickly get the row count, however Books Online says sys.sysindexes is deprecated, i.e. it will be removed from future versions of SQL Server. So another way of getting row counts is needed…

The utility

It is possible to use the catalog views sys.partitions and sys.indexes to quickly get row counts for tables, heaps, indexes and partitions.

The code for this utility is given below and can be downladed from the CodeBin.


-- Do not lock anything, and do not get held up by any locks.



-- Quickly get row counts.

SELECT OBJECT_SCHEMA_NAME(p.object_id) AS [Schema]

    , OBJECT_NAME(p.object_id) AS [Table]

    , i.name AS [Index]

    , p.partition_number

    , p.rows AS [Row Count]

    , i.type_desc AS [Index Type]

FROM sys.partitions p

INNER JOIN sys.indexes i ON p.object_id = i.object_id

                         AND p.index_id = i.index_id

WHERE OBJECT_SCHEMA_NAME(p.object_id) != 'sys'

ORDER BY [Schema], [Table], [Index]


To minimise the effect of running this code on other queries, the first statement is


this ensures no locks are taken out by the subsequent code, and the code itself does not honour any locks.

The main statement joins the sys.partitions catalog view with the sys.indexes catalog view, on their object_id and index_id columns. Note we filter out any system entries by ensuring




is not equal to the value 'sys'.


The columns output are Schema, Table, Index, partition_number, Row Count, and Index Type. An example of the output is given in the screenshot below:



Click for larger version


In the screenshot, you can see:

  • Row 1 is a non-clustered index on table LEA_ManVarConversionFactor, having 9 rows

  • Row 2 is the clustered index (i.e. the table) LEA_ManVarConversionFactor, having 9 rows

  • Row 3 is a clustered index, PLVector_CVaR_Staging, having 1,648 rows

  • Rows 4 to 8 show a clustered index (PLVector_DVaR_LE_Staging) having 5 partitions, each with a different number of rows

  • Row 9 is a heap (called SystemConstant), having 1 row


It is possible to amend the code to include only the table you are interested in by adding:


AND OBJECT_NAME(p.object_id) = 'YourTableName',


and replacing the value of YourTableName.




I hope you find this code useful in quickly getting the row counts of tables, heaps, indexes and partitions.


Ian Stirk is the author of SQL Server DMVS in Action (see side panel, and our review)  and  the following articles:




kotlin book



or email your comment to: comments@i-programmer.info


To be informed about new articles on I Programmer, subscribe to the RSS feed, follow us on Google+Twitter, Linkedin or Facebook or sign up for our weekly newsletter.













Last Updated ( Thursday, 07 November 2013 )