SQL Server Portal

Today, we will discuss the efficient way to count records of any table. Lets suppose you have millions of records in a table. How will you calculate the record count quickly ?

Let me explain this with simple examples :

Example 1 :
First, lets use the traditional way to count records from the table.

SET STATISTICS TIME ON

Select Count(*) as [Total Records]
from [tbl_Sample]

--Result
SQL Server parse and compile time:
   CPU time = 2 ms, elapsed time = 2 ms.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

Total Records
-------------
44040192

(1 row(s) affected)

SQL Server Execution Times:
   CPU time = 5046 ms,  elapsed time = 26518 ms.

As per the results, the above query took almost 26 seconds.

Example 2 :
Lets go to SSMS to view how SQL Server calculates the record count.

Right click on the table and go to properties. It will give you a lot of information including record count in a fraction of seconds.

Now, we need to find out what query is running behind these properties. So lets open the SQL Server profiler. A lot of queries were running to calculate different information but I grabbed the query that calculates record count.

Given below is the query that SQL Server uses to calculate the record count of any table.

SET STATISTICS TIME ON

select SUM([rows]) as [Total records] from sys.partitions
where object_id=object_ID('tbl_Sample') And [index_id] =1

SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 3 ms.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.
Total records
--------------------
44040192

(1 row(s) affected)

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

Conclusion :
You can view a remarkable difference between both queries.
In Example 1 , the SQL needs to calculate the record count from the table but in Example 2, the Information is already calculated and you just need to query it.