Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Count(*) or Count(1) ?

Every so often on the on-line forums, the question of : Which is better Count(*) or Count(1) ? will occur.  So can it categorically be proved than one is better than the other ?

What we need to do is populate a table and use both count(*) and count(1) and see if we get any performance difference between the two.

Code Snippet
  1. drop table largeTable
  2. go
  3. create table largeTable
  4. (
  5.   id integer not null
  6. )
  7. go
  8. declare @v integer
  9. insert into largeTable (id)
  10. Select top 1000000 ROW_NUMBER() over(order by @v)
  11. from sys.columns a cross join sys.columns b cross join sys.columns c
  12. go
  13.  
  14. select COUNT(*) from largeTable
  15. go 100
  16. select COUNT(1) from largeTable
  17. go 100

After executing the above code count(*) executes in an average of 107 milliseconds and count(1) executes in an average of 108 milliseconds.  So, there is no difference in performance.

What if though we use both count(*) and count(1) in the same statement ?  The average execution sound be in the order of 200 milliseconds, right ?

Code Snippet
  1. select COUNT(*),COUNT(1) from largeTable
  2. go 100

No, we still get the same average execution time, 100ms (ish).  Why is that ?  Two operations at 100ms each should equal a total of 200ms.  The answer to this, as a lot of answers do, lies within the execution plan.

image

Lets look at the properties for the highlighted compute scalar operation.

image

Both of the output columns are derived from the SAME Expression, Expr1006,  which is the result of the stream aggregate.  Looking at the properties of that we see

image

No mention at all of count(1) anywhere.  Indeed, if we look at the query plan of

Code Snippet
  1. select COUNT(1) from largeTable

we still see the count(*) scalar operator used. 

So not only now have we proved that there is no performance difference between the two,  but to the engine, they are the same operation.

Comments

Posted by Jason Brimhall on 27 July 2010

interesting results and thanks for sharing.

Posted by Steve Jones on 27 July 2010

I didn't think there was much difference. I thought the count(*) resolved internally to something similar to count(1)

Posted by Phil Factor on 30 July 2010

There was a time, a long time ago, when we had to do this. Old habits die hard.

Posted by David PROVOST on 1 August 2010

On Oracle, there was a time when difference was just a matter of compilation, where count(*) needed some permision SCHEMA version check. But I guess that if SQL Server translates the former into the latter during optimization, there's no such difference anymore. Thanks for sharing.

Posted by Charles Kincaid on 2 August 2010

I would love to see a version of COUNT() that requires no parameters.  Until then I've been telling my SQL writers to use COUNT(1).  The reason is our "Search and Destroy" tool in code review that specifically looks for * in SQL statements.  It triggers an automatic red flag audit of your code.

Posted by nguntert on 2 August 2010

This is interesting to me and a good article.  I have wondered about count(*) but not taken the time to do the tests.  I have heard that if you can do a count on an indexed column that it may eliminate the table scan and only read the index.  Since the index pages contain pointers to more rows than the data pages, the I/O time is decreased.  I would guess that in SQL Server that would mean doing a count on a "not null" column in an index other than the clustered index?  I'm still not taking the time to find out the answer, but I thought it might interest you.  

Posted by Nishit Mittal on 19 August 2010

Yes both are same operation .

Thanks for presenting this fact !!!

Leave a Comment

Please register or log in to leave a comment.