Difference Between Select Count(*) and Count(1) in SQL Server

SQLServerF1

One of the most common question or confusion many DBAs or Developers have is about the difference between the below two commands in SQL Server

Select Count(*) from TableName
Select Count(1) from TableName

It is very common perception that the Count(1) perform better compared to Count(*), however it is not the case. If you test by looking at the execution plan, you will see same action being performed by both the commands and same number of rows being scanned. The time taken may be slightly different interms of CPU usage for count(*) , but is almost same as count(1). The confusion is generally because in older version of some RDBMS products like Oracle has difference in performance for select count(*) and count(1), but recent releases does not have any difference.

Below is sample execution plan which is same for both the commands on a test table with 10,000 rows.

Select Count(*) vs Select Count(1)

Select Count(*) vs Select Count(1)

Below is the IO statistics for both the operations which scans same number of records.

(1 row(s) affected)
Table ‘PCP_PhysicalDisk(_Total)’. Scan count 1, logical reads 9252, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)
Table ‘PCP_PhysicalDisk(_Total)’. Scan count 1, logical reads 9252, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Other confusions people assume is that Select Count(*) will pull all the data of the table into memory which is not correct, you need to understand the difference between count(*) and select * which are different. Another assumption is in select (1) the 1 refers to the first column of the table which is incorrect too.

Another comparison which people make is comparing count(*), Count(1) and count(Column). Count(Column) will return all non-null rows, where are other two count(*) and count(1) returns all rows weather they are null or not null.

Some combinations of Count function in SQL Server and their description.

COUNT(*) returns the number of items in a group. This includes NULL values and duplicates.
COUNT(1) returns the number of items in a group. This includes NULL values and duplicates.
COUNT(ALL expression) evaluates expression for each row in a group and returns the number of nonnull values.
COUNT(DISTINCT expression) evaluates expression for each row in a group and returns the number of unique, nonnull values.

For return values greater than 2^31-1, COUNT produces an error. Use COUNT_BIG instead.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Leave a Reply

Your email address will not be published. Required fields are marked *