Creating Indexes with Included Columns
In SQL Server 2005, 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. For more information see Index with Included Columns.
Disk Space Requirements
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.
The process for determining disk space requirements for indexes with included columns is the same as that of nonclustered indexes. For information, see Determining Index Disk Space Requirements.
Performance Considerations
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.
Examples
A. 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.
USE AdventureWorks;
GO
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
B. 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.
USE AdventureWorks;
GO
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
To create an index with included columns
See Also
Concepts
Creating Nonclustered Indexes
General Index Design Guidelines