SQL SERVER – COUNT(*) and Index Used – Part 2

Yesterday I wrote a blog post on the topic SQL SERVER – COUNT(*) and Index Used, lots of people asked me to follow up question. The most question was around what would happen if there was a heap or only clustered index. Well, let us check that out in today’s blog post.

First, let us create a sample table. I am creating the table based on the Person.Address table of AdventureWorks database.

SELECT *
INTO TestTable
FROM [AdventureWorks].[Person].[Address]
GO

Please note that the table has currently no index at all. Now let us enable the execution plan.

Now run the following script to count the number of rows.

SELECT COUNT(*)
FROM TestTable
GO

Now check the execution plan. In the plan, you will see Table Scan.

Next, we will create clustered index on the table.

CREATE CLUSTERED INDEX [CI] ON [dbo].[TestTable]
([AddressID] ASC)
GO

Now once again run the command to count the number of rows and check the execution plan.

Here you will see Clustered Index Scan in an execution plan. This is because once you have clustered index, you will not have a table scan. I have often discussed this during Comprehensive Database Performance Health Check.

The next step is to create a nonclustered index and check the execution plan.

CREATE NONCLUSTERED INDEX [NCI] ON [dbo].[TestTable]
([StateProvinceID] ASC)
GO

In the execution plan, you will notice NonClustered Index Scan because that is created on a narrow column. Please understand that SQL Server Optimization Engine always builds the execution plan which is the most effective and efficient.

A nonclustered index with less number of column is always more efficient in the COUNT(*) operations.

Once you are done with the test experiment you can run the following command to drop the table.

DROP TABLE TestTable
GO

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.

• Optimize for Ad Hoc Workloads – SQL in Sixty Seconds #173
• Avoid Join Hints – SQL in Sixty Seconds #172
• One Query Many Plans – SQL in Sixty Seconds #171
• Best Value for Maximum Worker Threads – SQL in Sixty Seconds #170
• Copy Database – SQL in Sixty Seconds #169

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