Difference between COUNT(*) and COUNT(1)

• crosspatch

  Hall of Fame

  Points: 3168

  More actions

  June 24, 2002 at 9:04 am

  #78690

  OK silly question but waht is the performance differnec between COUNT(*) and COUNT(1) if any and why

  I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961

  
  I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961

• danw

  SSCommitted

  Points: 1842

  More actions

  June 24, 2002 at 9:45 am

  #431473

  I've never seen a difference documented for SQL Server. In Oracle count(1) is much faster because count(*) enumerates all the columns in a table before counting the rows. It is possible that SQL Server has the same preprocessor column enumeration with count(*) and not with count(1).

  I try to use count(1) as a best practice.

  DanW

• crosspatch

  Hall of Fame

  Points: 3168

  More actions

  June 25, 2002 at 1:20 am

  #431540

  Thanks Dan. that's waht I thought, to be honest I've not noticed any performance gain using count(1) in SQLSERVER. I've seen a gain using the ROWS column from SYSINDEXES, but it can often give slightly different results

  I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961

  
  I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961