Freigeben über

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

  • Artikel

David Meego - Click for blog homepageNow 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

Comments

  • Anonymous
    November 13, 2011
    Great piece of information! We can definitely make use of this logic in other procs that we may write too. Thanks David... :)

  • Anonymous
    November 13, 2011
    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). It is slow, but not because of the reasons provided. It's slow because a COUNT() performs a table scan. In tables with large amount of records, this can be a performance killer. "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". It's an estimate and by definition, not necessarily accurate. MG.- Mariano Gomez, MVP

  • Anonymous
    November 14, 2011
    What a silly topic for a Blog article!  Just kidding David - that's very cool.  Thanks for posting this.

  • Anonymous
    November 14, 2011
    Hi Mariano Yes, it counts the number of records using a table scan. I did mention that the SQL Query provided an estimate, but if the statistics are kept up to date, the estimate can be accurate. On my system I am yet to find a table where the "Estimate" gives a different result to the COUNT(*). David

  • Anonymous
    November 14, 2011
    Hi Michael Glad you liked my "silly" topic. This method made a significant performance difference to a number of windows in the Support Debugging Tool. David

  • Anonymous
    November 14, 2011
    Posting from Jivtesh Singh at About Dynamics, Development and Life. www.jivtesh.com/.../everything-dynamics-gp-46.html

  • Anonymous
    November 15, 2011
    The comment has been removed

  • Anonymous
    November 15, 2011
    Posting from Mark Polino at DynamicAccounting.net msdynamicsgp.blogspot.com/.../quick-tip-using-sql-to-return-number-of.html

  • Anonymous
    November 15, 2011
    Beat - Please ensure you run the query in the company database. The query does not return records if the table which is being queried is not present in the specific database you are running the query against. Hope this helps... :)

  • Anonymous
    November 16, 2011
    Posting from Vaidy Mohan at Microsoft Dynamics GP - Learn & Discuss www.vaidy-dyngp.com/.../using-sql-to-retrieve-number-of-records.html

  • Anonymous
    November 17, 2011
    It's even easier in SQL Server 2008 R2 and forward, with sp_spaceused. This will give you some other basic information about the object size as well. msdn.microsoft.com/.../ms188776.aspx ex: exec sp_spaceused 'GL30000'