select count(*) or select count(OrderID)

Question

select count(*) or select count(OrderID)

adonetok

SSCrazy Eights

Points: 8395
More actions
August 11, 2010 at 8:28 am

#387550

Which one is fast to get count of records?
1)select count(*) from order
2)select count(OrderID) from order (OrderID is primary key)
I tested in 3894000 records table but both them are done in 0 second

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

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

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

adonetok (8/11/2010)
Which one is fast to get count of records?
1)select count(*) from order
2)select count(OrderID) from order (OrderID is primary key)
I tested in 3894000 records table but both them are done in 0 second

They'd have the same execution plans I'd imagine, so the same performance in this particular case.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

http://sqlinthewild.co.za/index.php/2009/04/14/on-counts/

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

CELKO (8/11/2010)
The table row count (NOT records! quit thinking this is a mag tape file system) is available at the table level, so three is no need to scan the table.

SQL will scan something to get a row count (from SELECT COUNT(*) or SELECT Count(<not nullable column>)). Not the table, unless there are no nonclustered indexes, but it will scan an index to do the row count, the one with the fewest leaf pages.

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