October 17, 2013 at 2:37 pm
Hmmm....Try this
Create table #foo (col1 int null, col2 int null)
insert #foo
select NULL,1 union select 1,NULL
select COUNT(*) from #foo
select COUNT(1) from #foo
select COUNT(col1) from #foo
select COUNT(2) from #foo
select COUNT(col2) from #foo
select COUNT(1+1) from #foo
select COUNT(1/0) from #foo
select COUNT(col2/0) from #foo
Interestingly, SQL server doesn't seem to care what's in the () unless it's a column name. It's obviously not evaluating static numeric expressions, or COUNT(1/0) would fail. It's evaluating the expression if it contains a column name, which is why COUNT(col2/0) fails.
I think this sort of proves that COUNT(1) is the same as COUNT(*) in case an identical execution plan isn't proof enough.
October 17, 2013 at 3:18 pm
Jeff Gray (10/17/2013)
Hmmm....Try thisInterestingly, SQL server doesn't seem to care what's in the () unless it's a column name. It's obviously not evaluating static numeric expressions, or COUNT(1/0) would fail. It's evaluating the expression if it contains a column name, which is why COUNT(col2/0) fails.
Yes. COUNT(NULL) works too.
ATBCharles Kincaid
October 17, 2013 at 3:32 pm
I think we have beaten this horse to death, but here are a couple more considerations to enjoy 🙂
As much as possible I try to make the code self documenting. Using COUNT(1) is confusing, for example ORDER BY 1,2,3 is valid syntax, but it literally refers to column 1, 2 3. Thus when using COUNT(1), someone could interpret the intention to count the not null values of column #1. Another point is that COUNT(1) is not official ANSI SQL, the 1 is totally arbitrary (as several other people have pointed out here). If anyone has seen a reference to COUNT(*) in any official published SQL Reference or ANSI Standrds document or similar, please post it here. With that said and since there is no performance or other benefit of using COUNT(1), I would stay with the syntax that is universally documented as the correct ANSI SQL and that is COUNT(*). It provides the best performance and the code is universally understood and documented to mean the count of rows in the query without regard to column values in the result set.
October 17, 2013 at 4:58 pm
I get an error with SELECT COUNT(NULL), but that's probably the worst suggestion I have seen so far. I am not sure why some of you wants to do something non-standard, when standard ANSI SQL works the best? For job security you code something like SELECT COUNT('1 + x - 32 * 27 / 2') then the next programmer for sure wouldn't know you intended 🙂 But it works just as well as COUNT(1). The * has a built in meaning in relational databases and relational technology - 1 on the other hand is not part of any relational theory. There is only one standard way to do this - Use COUNT(*).
January 10, 2014 at 6:11 am
Yes, this article is largely pap, perpetrating the usual unhelpful performance myths. There is an entire MVP Deep Dives article on the subject, which blows all these myths away. Although, the conclusion of "just use COUNT(1)" is actually sound, but mostly not for performance reasons. There was a time when the very rare optimiser hiccup might have created problems with COUNT(*), picking a bad index or even table scan. But, those versions are long behind us, well mostly.
There is still a good reason to pick COUNT(1) instead, and that is when you automate code checking. One of the first things you want to check for is SELECT * ..., easy if you don't have COUNT(*) anywhere in your code - just look for *. I also use "where exists( select NULL .." for the same reason, rather than "where exists( select * ..". Although, "where exists( select 1 / 0 .." would also work, but I don't like tempting fate. Yes, the select list in an exists is completely ignored, to the extent you can put in 1 / 0. You could also use 1 instead of NULL, but I like a bit of variety every now and again.
This DBA says - "It depends".
January 29, 2015 at 9:49 pm
Thanks for posting but I think this has really sparked up the age-old question on whether there is a difference between COUNT(*) and COUNT(1). In my early days I believed there was a difference but now understand that they are actually synonymous - there is no difference. If my current understanding is wrong then i would really like to know more about it.
January 30, 2015 at 1:18 am
nvm, others said it better....
January 30, 2015 at 2:21 am
This also count every row
SELECT COUNT(coalesce([Name],'')) AS [COUNT(*) This is 8 too] FROM [CountTest];
January 30, 2015 at 2:47 am
Did anyone mention that variables are also accepted by COUNT()?
DECLARE @i int
SELECT COUNT(@i) FROM [CountTest]
-- returns 0
DECLARE @i int = 1
SELECT COUNT(@i) FROM [CountTest]
-- returns number of rows
character variable types always return 0.
January 30, 2015 at 3:39 am
Since I deal with different RDBMS's I use COUNT(1) or COUNT(primarykey).
In some SQL-compliant databases, COUNT(1) can return different results than COUNT(*).
January 30, 2015 at 5:09 am
SQLBoar (1/10/2014)
... There is still a good reason to pick COUNT(1) instead, and that is when you automate code checking. One of the first things you want to check for is SELECT * ..., easy if you don't have COUNT(*) anywhere in your code - just look for *. ...
Well... only if your coding policy also /* bans the use of comments. */ 😉
My big objection to COUNT(1) is that it implies it will count the single constant value "1", and hence should return 1.
One might perhaps argue that it should count the first column (bad idea in case someone alters the column layout of the table).
Or perhaps it could count values equal to 1 (but what values?).
The one thing COUNT(1) doesn't imply: is that it will count "all".
And since maintaining code (SQL or any other language) requires more time reading & understanding said code - the most important consideration in the majority of cases should be readability!
Conclusion:
In the absence of proof that COUNT(1) is more performant than COUNT(*) and said performance is essential for a particular use-case - there is absolutely no reason to use COUNT(1).
January 30, 2015 at 6:08 am
Yep, I missed the * in the block comments.
ATBCharles Kincaid
January 30, 2015 at 6:26 am
and what about Count(0) ? Is that the same result as Count(1)?
January 30, 2015 at 6:52 am
Under which conditions can count(*) be slow? I'm trying with a 5TB DB, several tables having millions of rows and always get the answer in less than 1 sec.
January 30, 2015 at 7:08 am
SELECT COUNT(ISNULL(Name,'')) from CountTest -- This will give you 8 too
Viewing 15 posts - 46 through 60 (of 113 total)
You must be logged in to reply to this topic. Login to reply