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

 

Partitions

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…

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.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

 

-- 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

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

 

OBJECT_SCHEMA_NAME(p.object_id)

 

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:

 

columns

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.

 

 

Conclusion

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:

 

 

 

espbook

 

Comments




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.

Banner

<ASIN:1935182730>

 

 

 

 

 

 

 

 

 

 

Last Updated ( Thursday, 07 November 2013 )