question

minijebu avatar image
minijebu asked

using COUNT(1) instead of COUNT(*)

Is there any performance benefit of using COUNT(1) instead of COUNT(*) in the SELECT statement?
aggregates
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
JohnM avatar image
JohnM answered
Not that I'm aware of. You can test this with something like this: DECLARE @t TABLE( col INT ) INSERT INTO @t VALUES (1) INSERT INTO @t VALUES (2) SELECT COUNT(*) FROM @t SELECT COUNT(1) FROM @t Borrowed from: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/31795984-ea62-4b2c-8c78-6e986f2bcea0/count-1-vs-count- Look at the properties of the stream aggregate of the execution plan on Count(1) and you'll see that the Count(1) is actually translated into Count(*)
6 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Whether you use count(\*) or count(1), the engine will check whether your table has any narrow **nonclustered** indexes, and if it does then it will pick the one which is the most narrow and scan it. If your table does not have any nonclustered indexes then it will pick the clustered index for scanning. If the table has no indexes at all then it will perform a table scan (file, page, slot combination which makes up "index 0"). Some people (myself is guilty as charged) still prefer to use count(1) even though it does not offer any benefits whatsoever. This usually comes from the "old habits die hard". For example, early versions of Oracle had this interesting behaviour when COUNT('X') performed better then count(\*). This is no longer the case of course, but still if it is tattooed in the brains to use the literal "just in case" then it is hard to stop using it. There is another consideration when choosing what to use for count. In the rare scenarios when the database user has permissions to select from the table, but is explicitly denied select on some of the table's columns then both count(1) and count(\*) will fail while count(some\_not\_nullable\_column) will work just fine.
4 Likes 4 ·
@KenJ I was shocked when I read about it for the first time (on this site). This is all true. Suppose I have a user named **some\_user** who is a member of public role, so, in AdventureWorks:
grant select on HumanResources.Department to some_user;
go

-- now this works:
execute as user = 'some_user';
select count(1) RecordCount 
    from HumanResources.Department; 
revert;
go

-- deny select on just one column:
deny select on HumanResources.Department(ModifiedDate) 
    to [some_user];
go

-- the same script does not work:
execute as user = 'some_user';
select count(1) RecordCount 
    from HumanResources.Department; 
revert;
go
Interestingly enough, even though this is a tiny table with only 16 records (all fitting on one page), the engine still uses nonclustered index scan (AK\_Department\_Name) to calculate the number of rows because it knows that generally speaking, there are more records on each nonclustered index page then the number of records on the clustered index page (because the latter is **the data**) so there are less pages to scan when scanning the nonclustered index. The count is still denied even though the index being scanned does not include ModifiedDate.
2 Likes 2 ·
Good reference to the execution plan
1 Like 1 ·
Did I hear someone say execution plan?
1 Like 1 ·
Thanks John..
0 Likes 0 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.