Count(*) vs. Count(0)

• latingntlman

  SSCarpal Tunnel

  Points: 4348

  More actions

  January 11, 2010 at 9:48 am

  #137956

  Is there any advantage/disadvantage as far as rendering, performance, etc. by using either select count(*) or select count(0) to get total records on a table?

  I've always used count(*) but somebody just asked me that question and had me stumped, so I thought I ask some of the experts.

  Let's start the weigh in...

  thx,

  John

• Lowell

  SSC Guru

  Points: 323524

  More actions

  January 11, 2010 at 9:51 am

  #1101916

  there is no difference; SQL is smart enough to know there is no reason to retrieve rows for the count() function, and does it the fastest way possible; count(*),count(someColumn) and count(1) are all the same as far as SQL server is concerned.

  if you look at the execution plan of each query, you'll see that they are identical if the only thing you changed was what is inside the parenthesis of the count() statement

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• steveb.

  SSC-Forever

  Points: 46733

  More actions

  January 11, 2010 at 11:00 am

  #1101945

  Lowell (1/11/2010)

  ---

  there is no difference; SQL is smart enough to know there is no reason to retrieve rows for the count() function, and does it the fastest way possible; count(*),count(someColumn) and count(1) are all the same as far as SQL server is concerned.

  if you look at the execution plan of each query, you'll see that they are identical if the only thing you changed was what is inside the parenthesis of the count() statement

  Count(*) can be faster as count(someColumn) may need to check if there are nulls in the column if the column is nullable

  http://sqlinthewild.co.za/index.php/2009/04/14/on-counts/

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  January 11, 2010 at 1:00 pm

  #1102010

  Lowell (1/11/2010)

  ---

  count(*),count(someColumn) and count(1) are all the same as far as SQL server is concerned.

  Not true at all.

  Count(*) and count(<constant>) both count the number of rows in the resultset.

  Count(someColumn) counts the number of rows in the resultset where someColumn is not null

  They are functionally different and perform differently as well.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass