Using a column name in a COUNT function

  • craig 81366

    SSC Eights!

    Points: 808

    Brandie Tarvin (2/6/2015)[hr

    I think the argument here isn't "why NOT to use COUNT(*)" or "why to use COUNT(1)". It's more along the lines of "use either, the result and the query execution will be the same."

    This is where I disagree quite strongly.

    Yes the 2 options are functionally equivalent and have identical performance.

    But claiming that therefore it doesn't matter which is used completely ignores the consideration of code maintainability.

    Of the 2 options COUNT(*) and COUNT(1), which more intuitively implies "count all rows" versus "count one".

    Any developer who doesn't happen to know the peculiarity of the COUNT(1) syntax will do a double-take.

    The developer (if of the sort who'll do a proper due diligence) would have to spend time doing further investigation to understand what's going on and confirm that there are no unforeseen negative effects. <--- This immediately makes COUNT(1) an inferior choice.

  • SQLMaister

    SSC Enthusiast

    Points: 152

    I agree 100%. Any developer who fails to understand the importance of the cost of code development and maintenance and standardization related to it is really making a career limiting move. Mistakes are allowed, but someone who then insist on not fixing it or not changing a bad coding habit usually end up in a different career. Software developers are not immune to evolution - weak coders die out and strong coders succeed. Everybody can do what they want, but I wouldn't pay anyone to produce bad code...

  • andrew.ing

    SSC Eights!

    Points: 847

    Does the Question of the Day of 2015-02-18 have a bearing on this discussion?

    That is, COUNT(1) is the more reliable option because COUNT(*) will fail if the user is denied SELECT permission on one or more columns of the table?

  • craig 81366

    SSC Eights!

    Points: 808

    andrew.ing (2/18/2015)


    Does the Question of the Day of 2015-02-18 [/url] have a bearing on this discussion?

    That is, COUNT(1) is the more reliable option because COUNT(*) will fail if the user is denied SELECT permission on one or more columns of the table?

    No, because as per the question (and if you try it out): COUNT(1) behaves exactly the same as COUNT(*) in this regard.

    This is further evidence that under the hood both appear to be implemented identically.

    And hence further evidence that the only deciding factor between the two should be: which one yields more maintainable code?

  • andrew.ing

    SSC Eights!

    Points: 847

    I have tried it now and you're right, Count(*) and Count(1) behave the same on version 2008 R2 (RTM) - 10.50.1600.1 (X64). I misread the QotD text.

  • Charles Kincaid

    SSChampion

    Points: 13593

    craig 81366 (2/18/2015)


    And hence further evidence that the only deciding factor between the two should be: which one yields more maintainable code?

    Agreed.

    In the more recent versions of SQL Server there is no performance difference. Then there are matters of style. I'm not going to get into that. If you are interested I recommend Joe Celko's book on the matter.

    ATBCharles Kincaid

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Long ago I started using COUNT(0). It always works for me. As someone else pointed out if I want a distinct list of some field then I'll do COUNT(distinct columnname).

  • GPO

    SSCarpal Tunnel

    Points: 4450

    Er... Is this the right room for an argument?

    ...One of the symptoms of an approaching nervous breakdown is the belief that ones work is terribly important.... Bertrand Russell

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    GPO (5/18/2015)


    Er... Is this the right room for an argument?

    No. This is abuse. You *****.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Viewing 9 posts - 106 through 114 (of 114 total)

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