SQL SERVER - COUNT(*) and Index Used - SQL Authority with Pinal Dave

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.

Here are their execution plans.

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)