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.