INCLUDE Columns in Non-Clustered Index

This article explains how to use INLCUDE clause while creating the non-clustered index and how it helps to improve the query performance.

Non-clustered index is used to improve the performance of queries which are not covered by clustered index and while creating of non-clustered index, we consider only those columns which are being part of WHERE/ORDER BY/GROUP BY/JOIN statement.

CREATE NONCLUSTERED INDEX [DepartmentCode_IDX] ON [dbo].[Department]

(

   [DepartmentCode] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

ON [Primary]

In below SQL query, which fetches department information like name and location on based on input department code.

Select DepartmentName, DepartmentLocation From dbo.Department where DepartmentCode = @Code

In this query, the department record is quickly found with help of nonclustred index ‘DepartmentCode_IDX’ but to get department name and location information they have to fetch from table.

Built Non-Clustered Index-tree (B-Tree):

To improve the further performance of above select query, we can use INCLUSE clause in non-clustered index and specify the list of non-key columns which are part of Select statement like ‘ DepartmentName’, ‘DepartmentLocation’

CREATE NONCLUSTERED INDEX [DepartmentCode_IDX] ON [dbo].[Department]

(

   [DepartmentCode] ASC

) INCLUDE( DepartmentName, DepartmentLocation)

WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

ON [Primary]

Built Non-Clustered Index-tree (B-Tree):

You can see the Include Columns are not part of all Index nodes expects leaf nodes so overall it is not increasing index tree’s size and benefit to have include columns, it adds the extra data at leaf node and it helps to get select columns value from index leaf nodes instead of fetching data from table so in this way it improves the performance of query.

Other related Post:

SQL: Clustered, Non-Clustered and Index on Multiple Columns