|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:
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
SELECT COUNT(*) FROM tableName
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.
To minimise the effect of running this code on other queries, the first statement is
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
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:
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.
or email your comment to: firstname.lastname@example.org
|Last Updated ( Thursday, 07 November 2013 )|