SQL SERVER – COUNT(*) and Index Used

The best part of my job is working with my clients on the topic of SQL Server Performance Tuning when I am working with them on   Comprehensive Database Performance Health Check. Recently during a break between two sessions of Health Check Senior DBA mentioned to me that they are using lots of places COUNT(*) and suspect those operations are using lots of clustered index scans. Honestly, this is not always true. Let us discuss today COUNT(*) and Index Used.

Index Used for COUNT(*)

Lots of people assume that when COUNT(*) is used it is going to scan the entire table and for that, it will use either table scan (heap scan) or clustered index scan. Well, this can very well happen if you have a heap table without any index or a single clustered index on the table (and no other index).

However, we must remember that clustered index actually contains a whole table (and all the data).  In most cases when we create a nonclustered index on few columns of SQL Server. Unless we create a nonclustered index with all the columns of the table, it is usually a much narrower index.

SQL Server optimizer always prefers to use the index which is the most efficient to return results to the client. In my experiment when there clustered indexes and narrower nonclustered indexes, SQL Server has always used a nonclustered index for COUNT(*) operation.

Here are few queries which you can try out on the AdventureWorks sample database and you will find that in almost all cases, SQL Server uses a nonclustered index over clustered index.

USE AdventureWorks;
FROM [Person].[orders];
FROM [Sales].[SalesPerson];
FROM [Sales].[SalesOrderDetail];

Here are their execution plans.

SQL SERVER - COUNT(*) and Index Used countscan1

SQL SERVER - COUNT(*) and Index Used countscan2

SQL SERVER - COUNT(*) and Index Used countscan3

Well, that’s it for today. If you liked this video, please do not forget to subscribe to my YouTube Channel – SQL in Sixty Seconds.

Here are my few recent videos and I would like to know what is your feedback about them.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

Clustered Index, Execution Plan, SQL Index, SQL Scripts, SQL Server
Previous Post
SQL SERVER – Index Scans are Not Always Bad
Next Post
SQL SERVER – COUNT(*) Collection of Articles

Related Posts

2 Comments. Leave new

  • Irrespective of type of index used, I believe it still going to read all the rows..


Leave a Reply