What is the fastest/quickest way of getting the total number of records (COUNT) in a SQL Server table?

Are you trying to get the total number of records in one of your tables? If yes, what is the best, or what is the quickest way of getting the number?

Generally we use COUNT aggregate function for retrieving the record count from the table. This does not take much time if the table contains low number of records. However, when COUNT is run against a table that contains millions of records, it can take a noticeable time. In a situation like that, if the table is a clustered table, the required result can be retrieved with lesser time using sys.indexes system catalog view.

The following query runs against a tables called Fact.Sales in ContosoRetailDW database (one of Microsoft sample database). This table is clustered table and it contains 3.4 millions records. Have a look on the query and the result;

USE [ContosoRetailDW]

GO

DECLARE @CurrentTime AS datetime = GetDate()

SELECT Count(*) NumberOfRecords 

FROM dbo.FactSales;

SELECT Cast(DateDiff(SECOND, @CurrentTime, GetDate()) AS varchar)

        + ','

        + Cast(DateDiff(MILLISECOND, @CurrentTime, GetDate()) AS varchar)

        + ','

        + Cast(DateDiff(MICROSECOND, @CurrentTime, GetDate()) AS varchar)

    AS ExecutionTime

GO

DECLARE @CurrentTime AS datetime = GetDate()

SELECT [rows] NumberOfRecords 

FROM sys.sysindexes 

WHERE id = OBJECT_ID('dbo.FactSales') and indid =1 

SELECT Cast(DateDiff(SECOND, @CurrentTime, GetDate()) AS varchar)

        + ','

        + Cast(DateDiff(MILLISECOND, @CurrentTime, GetDate()) AS varchar)

        + ','

        + Cast(DateDiff(MICROSECOND, @CurrentTime, GetDate()) AS varchar)

    AS ExecutionTime

As you see, the second method produces the result less than one microsecond while first method takes about 76 milliseconds. This proves that querying sys.indexes is more efficient than COUNT table when querying a clustered table. However, there is chance of seeing an inaccurate number with this method if statistics are not updated. In addition to that, if the table is a heap table, same performance may not be seen with second method.