SQL and the like

Dave Ballantyne's blog. Freelance SQL Server database designer and developer at Clear Sky SQL

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.

Posted: Jul 27 2010, 01:13 PM by Dave Ballantyne | with 6 comment(s) |

Comments

RDouglas said:

Interesting, I was watching a Quest webcast last night with Kevin Kline and Buck Woody and Buck said that COUNT(*) did have impacts on performance due to performing a table scan. Unfortunately he didn't elaborate on it.

At a guess he was comparing it against the statistics in Sys.dm_db_partition_stats which should be quicker to query.

If you look at the query plans for the following code the first query takes 95% of the batch:

set nocount on

set statistics io on

select count(*) from Sales.Customer

select row_count from sys.dm_db_partition_stats where object_id = object_id('Sales.Customer') and index_id <=1

Statistics:

Table 'Customer'. Scan count 1, logical reads 36, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'sysidxstats'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Rich

# July 27, 2010 1:44 PM

JohnSansom said:

Keep in mind that count(*) will not always perform a table scan.

Provided that an index is available, it will use the smallest one to get the row count (using an Index Scan) for the table.

# July 27, 2010 1:57 PM

Paul White NZ said:

Excellent post Dave!

One more example: if you look in the XML plan, the count operation is shown as: <Aggregate AggType="countstar" Distinct="false" />

There may be a immeasurably small difference when a query is first parsed, bound and compiled: the column metadata might be expanded internally for COUNT(*), and instantly optimised away.  That's how it works for "EXISTS (SELECT * ..." versus "EXISTS (SELECT 1..."

I couldn't swear it was exactly the same, though.

Paul

# July 27, 2010 2:02 PM

Dave Ballantyne said:

@Rich,

Partition_stats is a quick way to get the count of the whole table.  Though the name implies partition not table, so if you have partitioned the table does that mean an row for each partition ?

@John,

Absolutely, the engine is a smart cookie and will attempt to minimize the amount of work/IO done. My point is that whatever count(*) does count(1) will do...

@Paul,

I dont believe that the * has anything to do with any column(s) and therefore any metadata.  Infact , if you count(<non nullable column>) you will get a countstar aggregate operation.  Count(<nullable column>) will , have to look to the column data though.

# July 27, 2010 2:17 PM

tonyrogerson said:

Recently saw that using SELECT * on an EXISTS clause actually failed because the user didn't have access to all the columns - I wonder if this is the case on COUNT as well? Though, an aggregation is different I guess - the EXISTS is probably just a qwerk of the optimiser doing its business.

# July 28, 2010 8:46 AM

GrumpyOldDBA said:

see my post from June  sqlblogcasts.com/.../analysing-indexes-count.aspx

# July 28, 2010 1:27 PM