February 11, 2015 at 10:50 am
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.
February 11, 2015 at 11:17 am
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...
February 18, 2015 at 2:00 am
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?
February 18, 2015 at 3:02 am
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?
February 18, 2015 at 3:22 am
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.
February 19, 2015 at 12:26 pm
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
May 7, 2015 at 12:57 pm
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).
May 18, 2015 at 8:41 am
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
May 18, 2015 at 1:04 pm
GPO (5/18/2015)
Er... Is this the right room for an argument?
No. This is abuse. You *****.
Viewing 9 posts - 106 through 113 (of 113 total)
You must be logged in to reply to this topic. Login to reply