Search:
Welcome Guest | Register | Login
logo

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
4
 
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
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
Flower

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(*)?

4
 
Answered: 24 May 2012
Reputation: 2,395
Hamden
Login to post your answer
viewed 3,020
asked 24 May 2012