SQL SERVER – SELECT Count(1) vs SELECT Count(*)
What is the performance differences in these statements?
Let's analyze it: I have a table Employee with records 34395 which we will use to check the performance.
Set Statistics Time On
Go
Select Count(*) From Employee
Go
Set Statistics Time Off
Go
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:
Count(*)
SQL Server Execution Times:
CPU time = 46 ms, elapsed time = 22 ms.
Count(1)
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
5
|
Answered:
02 Dec 2012
Reputation: 960
|
![]() |
There is NO performance difference between SELECT COUNT(*), COUNT(1), COUNT(ColumnName)
For further detail check this link:
4
|
Answered:
24 May 2012
Reputation: 2,395
|
![]() |