Have you ever heard of Included Index in SQL Server? Surprisingly, many people are still unaware of this powerful feature. In this article, we will explore what Included Index is and how it can improve the performance of your SQL Server queries.

In SQL Server 2005, non-clustered indexes were enhanced by introducing the concept of non-key columns at the leaf level. These non-key columns, also known as included columns, can be added to the non-clustered index to create cover indexes. By including non-key columns, you can create non-clustered indexes that cover more queries.

One of the advantages of using included columns is that they do not count towards the maximum limit of 16 key columns or the maximum index key size of 900 bytes. This allows you to include additional columns in the index without worrying about exceeding these limitations.

Another benefit of using included columns is that you can include data types that are not allowed as index key columns. For example, if you have a column with a data type of varchar(400), which exceeds the maximum index key size, you can still include it in the index using the INCLUDE keyword.

Let’s take a look at an example:

USE AdventureWorks
GO

CREATE INDEX IX_Document_Title ON Production.Document (Title, Revision) INCLUDE (FileName)

In this example, we are creating a non-clustered index on the “Title” and “Revision” columns of the “Production.Document” table. We are also including the “FileName” column as an included column in the index.

It’s important to note that included columns can only be added to non-clustered indexes. Additionally, column names cannot be repeated in the INCLUDE list, and columns cannot be defined in both the key column and the INCLUDE list.

When using included columns, it is crucial to avoid adding unnecessary columns. Adding too many index columns, whether key or non-key, can negatively impact performance. It can lead to fewer index rows fitting on a page, increased I/O, reduced cache efficiency, and increased disk space usage. It can also affect the time it takes to perform modifications, inserts, updates, or deletes on the underlying table or indexed view.

Let’s consider another example:

USE AdventureWorks
GO

CREATE NONCLUSTERED INDEX IX_Address_PostalCode ON Person.Address (PostalCode) INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID)

In this example, we are creating a non-clustered index on the “PostalCode” column of the “Person.Address” table. We are also including the “AddressLine1”, “AddressLine2”, “City”, and “StateProvinceID” columns as included columns in the index.

To test the performance improvement of the included index, you can compare the execution time of a query before and after creating the index. For example:

SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
FROM Person.Address
WHERE PostalCode BETWEEN '98000' AND '99999';

By creating the included index, you should observe a significant improvement in the query’s performance.

In conclusion, Included Index is a powerful feature in SQL Server that allows you to include non-key columns in non-clustered indexes. By using included columns, you can create cover indexes that cover more queries and improve query performance. However, it is important to carefully consider the columns to include and avoid adding unnecessary columns that may negatively impact performance.

Reference: Pinal Dave (https://blog.sqlauthority.com)