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.
SQL Server Portal 