October 21, 2011 at 4:55 pm
I'm assuming SQL Server is optimized for count(*) and a using a specific indexed column doesn't make a performance difference.
For example, if a table has a PK of ID then it would have a unique clustered index so better performance for selection than a non-clustered index.
However, I'm assuming that MS was smart enough to optimize * as a generic wildcard for aggregate functions on any table. I'm assuming the SQL Server engine uses some sort of optimized algorithm so count(*) is just as efficient as count(ID).
Can someone please confirm this?
October 21, 2011 at 5:00 pm
http://sqlinthewild.co.za/index.php/2009/04/14/on-counts/
p.s. Primary key != Clustered index.
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
October 21, 2011 at 5:23 pm
PK is the default clustered index isn't it?
October 21, 2011 at 5:42 pm
By default, yes, but only by default.
Oh, and if count used the unique clustered index instead of a nonclustered index it would have worse performance. Count(*) will use the smallest index on the table to scan, and that is not going to be the cluster if there are any nonclustered indexes
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply