Count(*) vs. Count(0)

  • 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

  • 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!

  • 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/

  • 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

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply