Welcome Guest | Register | Login

SQL SERVER – SELECT Count(1) vs SELECT Count(*)

SQL SERVER – SELECT Count(1) vs SELECT Count(*)

What is the performance differences in these statements?

  • sql server
Asked: 24 May 2012
Reputation: 810
Dev D
2 Answers

Let's analyze it: I have a table Employee with records 34395 which we will use to check the performance.

Set Statistics Time On
Select Count(*) From Employee
Set Statistics Time Off

I set statistics time on to check the different time taken by query in message window:

I used Count(*), Count(1) and Count(EmployeeId) to evaluate the time, and here is the result:

SQL Server Execution Times:
   CPU time = 46 ms,  elapsed time = 22 ms.

SQL Server Execution Times:
   CPU time = 47 ms,  elapsed time = 15 ms.

Count(EmployeeId)   -- Table Primary key column
Warning: Null value is eliminated by an aggregate or other SET operation.
SQL Server Execution Times:
   CPU time = 31 ms,  elapsed time = 33 ms.

When I am using EmployeeId I am getting a warning so be careful to use the column name when using count, it depends on your requirements

Answered: 02 Dec 2012
Reputation: 960

There is NO performance difference between SELECT COUNT(*), COUNT(1), COUNT(ColumnName)

For further detail check this link:

Performance operation on the desk of doctor of SQL

what's the difference between count(column) and count(*)?

Answered: 24 May 2012
Reputation: 2,395
Login to post your answer
viewed 2,425
asked 24 May 2012