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.

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:

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:

• The First Things I Look At On A SQL Server – Part 1
• The First Things I Look At On A SQL Server – Part 2
• Improve SQL Performance – Know Your Statistics
• Improve SQL performance – An Intelligent Update Statistics Utility
• Identifying your slowest SQL queries
• Improve SQL performance – find your missing indexes
• A Generic SQL Performance Test Harness
• A Generic SQL Server Compression Utility

Comments

Make a Comment or View Existing Comments Using Disqus

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.

<ASIN:1935182730>