CODEWITHSUNDEEP
sqlsql-serverdatabaseselect
Aishwarya Shiva
Aishwarya Shiva

Reputation: 3406

Is COUNT(*) indexed?

I want to know just for curiosity that, does Select Count(*) from SomeTableName also traverses all the rows of the database as that of Select * from SomeTableName?

Or is there any other count field available in database's metadata that updates itself each time a row is added or deleted? And that field is accessed by the former query.

Also I want to know that which of the both queries is faster and how much?

Upvotes: 12

Views: 413

Answers (1)

Martin Smith
Martin Smith

Reputation: 453667

SELECT Count(*)
FROM   SomeTableName

will always count all rows. Though (unlike SELECT *) it does not have to read all columns and can use the narrowest (non filtered) index available to do so.

Unlike MySQL (MyISAM engine) it does not retrieve the value from metadata.

A rowcount value is available in the metadata and can be retrieved from sys.partitions but this is never used for COUNT queries and isn't always accurate.

Upvotes: 17

Related Questions