Quick Tip: Using SQL to return the number of records in a Table

Now I am sure there are people out there that will be thinking that this is a silly topic for a blog article. Surely everyone knows how to get the number of records from a SQL table using the COUNT(*) function?

Well, you would be correct that most people do know the COUNT(*) method as shown below:

SELECT COUNT(*) FROM GL30000

But that is not what this article is about.

I was working on a upgrade support case where we needed to check the number of records in the GL30000 table. The upgrade process was taking a long time and I wanted to know how many records the conversion script for the GL30000 table needed to work on. So we issued the line above and 10 minutes later we got the result that there were just over 1 million records in the table.

While working on the new build of the Support Debugging Tool, my beta testers noticed that the Resource Explorer lookups for "Associated Tables" and "Tables Containing Field" were slow when the tables returned had a large number of records. This is because the COUNT(*) function used to get the number of records is slow, it just counts the number of records returned by the SELECT statement limited by the WHERE clause (if provided).

If I am just interested in the total number of records in the table (without any WHERE clause), there must be a better way to get this information?

Well, yes there is... I am glad you asked.

For every table, there is a primary key and stored in the internal system tables for that key is the number of records contained in the key. So using a slightly more complex query, it is possible to obtain the number of records in a table instantaneously without needed to actually count the records themselves.

This is the SQL query to obtain an estimate of the number of records from the index data for a table:

SELECT objs.name, indx.rowcnt FROM sys.objects objs
INNER JOIN sys.sysindexes indx ON (objs.object_id = indx.id) AND (indx.indid < 2)
WHERE (objs.type = 'U') AND (objs.name = 'GL30000')

This is the code that the Support Debugging Tool now uses, which means it can display the number of records in tables without any performance delays.

Note 1: This method works best when the AUTO UPDATE STATISTICS option on the database is selected, otherwise the estimate of rows might not be as accurate.

Note 2: For SQL Server 2000, there is an issue where the rowcnt column of the sysindexes system table is not updated until a transaction is committed. For more information see https://support.microsoft.com/kb/308822/j

Hope you find this useful.

David