Printed 2017/08/20 12:43AM

Fastest row counting method

By Jakub Dvorak, 2011/07/26

SELECT COUNT(*) is most common method (and exact) how to find out how many records is in table. There is also another method which is not exact but is way faster especially when you are expecting zillion of rows to be counted. This method is based on fact that SQL Server internally tracks how many rows is maintaned by each index.

There are two situations in table which are important for what I am talking about:

For both situations SQL Server knows how many records are contained either in the clustered index or in the heap. This information is held in sys.indexes system table and maintained by updating statistics. This is the reason why number of rows in sys.indexes table is not guaranteed – if statistics are not updated by any reason, this number is not actual.

Here’s code:

USE AdventureWorks

SET @table = 'Person.Address' -- name of your table

SELECT rowcnt
FROM sys.sysindexes
WHERE id = OBJECT_ID(@table)
      AND (indid = 0 OR indid = 1) -- clustered index or heap

Copyright © 2002-2017 Redgate. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.