SQL Server: Quickly get row counts for tables, heaps, indexes, and partitions
Written by Ian Stirk
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…
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.
, i.name AS [Index]
, p.rowsAS [Row Count]
, i.type_desc AS [Index Type]
INNERJOINsys.indexes i ON p.object_id= i.object_id
AND p.index_id = i.index_id
ORDERBY [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 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: