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:

• Clustered index is present on table
• Clustered index not present on table, records are “organized” in heap. Other non-clustered indexes doesn’t affect heap, they only point to records in the heap.

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
DECLARE @table VARCHAR(250)
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