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 
Reblogged this on Chris Tierney and commented:
I ran into Muhammand’s blog on decreasing row count cost and thought it was a great tip to use in the future!
I wouldn’t be surprised if the count(*) slows things down a lot because it’s selecting all the columns. See if Select Count(1) as [Total Records]
or Select Count(id) as [Total Records] makes any difference.
Hi Brain,
Thank you for your feedback. You are absolutely right. Given below are my finding.
Select Count(*) –15051 ms
Select Count(1) –13953 ms.
Select Count(ID) –13756 ms.
SQL Server Execution Times:
CPU time = 4030 ms, elapsed time = 15051 ms.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 4219 ms, elapsed time = 13953 ms.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 4110 ms, elapsed time = 13756 ms.
Imran
Reblogged this on Sutoprise Avenue, A SutoCom Source.
I have always used ‘Count’ function to count the records but now will use your trick. Its time saving also. Thanks.
I am getting different total rows. Count (*) returns 1111173 whereas 2nd approach (sum from partition) returns 17778768.
Hi Jags,
Thank you for your feedback. Please add this script (And [index_id] =1) in the last of the 2nd approach.
Imran
[…] in each Memory optimized table in a particular database. I remember developing a solution earlier (How to get the number of records in a disk based tables. ). I tried this solution on memory optimized table but it did not work. Ooopssssssss. Given below […]
Thank you for the code. I’m relatively new to SQL Server and this will go in my bag of tricks. There is one thing I read on TechNet that should be mentioned. The description of the “rows” column is: “Indicates the approximate number of rows in this partition.” So, if you need exact row counts, you may need to be extra careful with this approach.
And for anyone who was curious as to what the “index_id = 1” does (like I was)… It goes after only the clustered index.
Hi Eric,
Thanks for sharing
Imran
[…] of different scripts that you can easily find in many blogs and sites. In fact, I have written a solution earlier. In this article, I will discuss how to find the largest object without writing a single […]