Indexes with Included Columns in SQL

What are Indexes with Included Columns in SQL?

Indexes with Included Columns in SQL Server

Overview

In this post, I will explain all about Indexes with Included Columns in Microsoft SQL Server end to end along with the appropriate examples. So let’s start –

Indexes with Included Columns in SQL

In this session we will learn to improve the speed of queries using indexes with included columns.

Syntax:

CREATE UNIQUE INDEX index_name

ON table_name(key_column_list)

INCLUDE (included_column_list)

In above syntax we have to specify the name of the index after CREATE INDEX clause, if we need to create UNIQUE INDEX then we have to use the keyword UNIQUE and then specify the name of the table and a list of key columns list for the index after ON clause and the list a comma-separated list of included columns in the INCLUDE clause.

We will use here dbo.Stud table in our training database in which there are some data fields that I would like to know you – ID, Name, Class, City, email. Now we will create a unique index on ID column –

Syntax:

CREATE UNIQUE INDEX ix_City_Stud
ON dbo.Stud(City)

After creating the above index, we will execute the below query (SELECT statement) –

Syntax:

SELECT ID,Name,City

FROM dbo.Stud

WHERE City = ‘Satna’

We will execute this query and if we want then we can display the estimated execution plan for the above query –

As we can see that the query optimizer uses the index seek operation on the non clustered index to find the City and ID as shown in below snapshot –

again the query optimizer uses the key lookup on the RID (heap) operation to find the ID as shown in below snapshot –

We can see the cost for key lookup is about 50% of the query, which is quite expensive, So to reduce this key lookup cost Microsoft SQL Server allows us to extend the functionality of a non clustered index by including non-key columns. By including non-key columns in non clustered indexes we can create non clustered indexes that cover more queries.

Now we will drop this index – ix_City_Stud from dbo.Stud using DROP INDEX statement –

Syntax:

DROP INDEX ix_City_Stud

ON dbo.Stud

Now we will create a new index ix_City_Stud_Incl as we discussed that included columns –

Syntax:

CREATE UNIQUE INDEX ix_City_Stud_Incl

ON dbo.Stud(City)

INCLUDE (Name, Class,Email)

After creating the above index, we will execute the below query (SELECT statement) –

Syntax:

SELECT ID,Name,City

FROM dbo.Stud

WHERE City = ‘Satna’

We will execute this query and display the estimated execution plan for the above query, Now we can see the query optimizer is using this non clustered index to return the requested data of the query.

So we can say that an index included columns can really improve query performance because all columns in the query are included in the index. Query optimizer can locate all the columns values within the index without accessing a table or clustered index resulting in fewer disk I/O (Input/Output) operations.

Assuming this tutorial post has well-acquainted aspiring to the complete requirement.

Also requesting, if you have any ideas or suggestions for me or you need some important blogs/Videos or guidelines related to SQL Server, Excel, WordPress, SEO, Website technologies then please feel free to write me on below comment section or you can go through the Contact Page. I will definitely come back to you with your desired requirements.

Hope this tutorial post was helpful for you and you liked very well, I am happy to serve you better. If you liked really this tutorial post, please don’t forget to share on social media like – Facebook, Whatsapp, LinkedIn, Twitter, Instagram, etc as it may help some other needy peoples too.

Leave a Reply