7

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

 2 years ago
source link: https://blog.sqlauthority.com/2021/06/18/sql-server-count-and-index-used-part-2/?utm_campaign=sql-server-count-and-index-used-part-2
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

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.

SQL SERVER - COUNT(*) and Index Used - Part 2 indexused-800x238

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.

SQL SERVER - COUNT(*) and Index Used - Part 2 indexused1

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.

SQL SERVER - COUNT(*) and Index Used - Part 2 indexused2

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.

SQL SERVER - COUNT(*) and Index Used - Part 2 indexused3

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.

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


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK