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.

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…