Difference between count(*) and count(1) – Part 1

Almost everyone have used both the functions count() and count(1). I heard different views about the difference between count() and count(1) functions. Some people says that count(1) only counts the number of rows of tables first column whereas count(*) counts rows of table by FULL TABLE SCAN. So in this post I am going to check whether is there any difference between the executions of these two functions.
I have created an EMPLOYEE table with below structure with around 31000 rows.

Difference between count(*) and count(1)

I am going to check the difference between these two function queries by considering the below three scenarios which will be three different parts of the complete post.

Employee table without any index – Part 1

Employee table having unique index on ID column – Part 2

Employee table having with Primary Key on PROJECT column and unique index on ID column – Part 3

Scenario 1: Employee table without any index – Part 1

In this scenario, there is no index on the employee table and also there is no any constraint on the table. Now let’s check how these queries work with execution plan and auto-trace statistics. I am flushing the buffer cache before executing both the queries so that we can get accurate results.

select count(*) from employee;

select count(1) from employee;

Observations:

There is no any difference between the execution plans of both the queries. Both the queries are going for FULL TABLE SCAN on Employee table.

Both the queries have performed the same number physical reads and consistent gets.

Conclusion:

In this scenario it is confirm that there is no any difference between the executions of count() and count(1) functions. We are going to check the difference of count() and count(1) functions for two more scenarios in upcoming two posts.