Creating Indexes with Included Columns

A nonclustered index can be extended by including nonkey columns in addition to the index key columns. The nonkey columns are stored at the leaf level of the index b-tree. Indexes that include nonkey columns provide the greatest benefit when they cover the query. This means the indexes include all columns referenced by the query.

Adding nonkey columns to the index uses more disk space to store the index. In particular, adding varchar(max), nvarchar(max), varbinary(max), or xml data types as nonkey columns may significantly increase disk space requirements, because the column values are copied into the index leaf level and also remain in the table or clustered index.

Performance gains are achieved in select operations because the query optimizer can locate all the required column data within the index; the table or clustered index is not accessed. However, having too many included columns may increase the time that is required to perform insert, update, or delete operations to the underlying table or indexed view because of increased index maintenance.

Covering a query

The following example creates a nonclustered index on the Person.Address table with four included columns. The index key column is PostalCode and the nonkey columns are AddressLine1, AddressLine2, City, and StateProvinceID.

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

This query will be covered by the index.

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

Exceeding the index size limitation

In the following example, the INCLUDE clause of the CREATE INDEX statement is used to index columns that would typically exceed the 900-byte maximum key column size limitation. The Production.ProductReview table contains these columns: ProductID (int), ReviewerName (nvarchar (50)) and Comments (nvarchar (3850)). These columns are frequently used in queries, but the Comments column is too large to participate as an index key column. However, by using the INCLUDE clause, the Comments column can be added as a nonkey column in the index.

CREATE NONCLUSTERED INDEX IX_ProductReview_ProductID_ReviewerName
ON Production.ProductReview (ProductID, ReviewerName)
INCLUDE (Comments);
GO

This query will be covered by the index.

SELECT Comments
FROM Production.ProductReview 
WHERE ProductID = 937;
GO