COUNT(*) vs. COUNT(1) – Myth vs. Reality

You might have seen in many T-SQL scripts that some people use SELECT COUNT(*) FROM MyTable and some people use SELECT COUNT(1) FROM MyTable. Both of these return the same value and perform the same task of getting record count from a table. However, some techies believe that COUNT(1) is better in terms of performance when compared to COUNT(*). Let us see if this is a Myth or a Reality.
Query Execution Results:
Query Execution Plans:
In the above execution plans the Stream Aggregate (Read it Right to Left. Second from Right.) performs the count operation. Now when you click on the Stream Aggregate and go to the properties (press F4) window, you can see that the underlying query that gets executed uses COUNT(*) in both the above cases. Below is the screenshot of properties window of Stream Aggregate for both these execution plans.

From the above results, it is evident that there is no performance difference between COUNT(*) & COUNT(1) and both of them boil down to COUNT(*) in the background.
Advertisements

About Dattatrey Sindol (Datta)

Datta is a Microsoft BI Enthusiast, passionate developer, and a blogger. View Full Profile

Posted on November 17, 2010, in Comparisons, Interview Questions, SQL Server, T-SQL. Bookmark the permalink. 2 Comments.

  1. Thanks for the info.Wanted to know whether you have cleaned the cache after executing the count(*) statement and then executed the count(1) statement?.

  2. Hi There,I did clean the cache between each of the runs.Irrespective of that, if you look at the execution plan, it is exactly the same.Best Regards,Datta

What are your thoughts?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s