Index with Included Columns
In SQL Server 2005, you can extend the functionality of nonclustered indexes by adding nonkey columns to the leaf level of the nonclustered index. By including nonkey columns, you can create nonclustered indexes that cover more queries. This is because the nonkey columns have the following benefits:
- They can be data types not allowed as index key columns.
- They are not considered by the Database Engine when calculating the number of index key columns or index key size.
An index with included nonkey columns can significantly improve query performance when all columns in the query are included in the index either as key or nonkey columns. Performance gains are achieved because the query optimizer can locate all the column values within the index; table or clustered index data is not accessed resulting in fewer disk I/O operations.
Note
When an index contains all the columns referenced by the query it is typically referred to as covering the query.
While key columns are stored at all levels of the index, nonkey columns are stored only at the leaf level. For more information about index levels, see Table and Index Organization.
Using Included Columns to Avoid Size Limits
You can include nonkey columns in a nonclustered index to avoid exceeding the current index size limitations of a maximum of 16 key columns and a maximum index key size of 900 bytes. The Database Engine does not consider nonkey columns when calculating the number of index key columns or index key size.
For example, assume that you want to index the following columns in the Document table in the AdventureWorks sample database:
Title nvarchar(50)
Revision nchar(5)
FileName nvarchar(400)
Because the nchar and nvarchar data types require 2 bytes for each character, an index that contains these three columns would exceed the 900 byte size limitation by 10 bytes (455 * 2). By using the INCLUDE clause of the CREATE INDEX statement, the index key could be defined as (Title, Revision) and FileName defined as a nonkey column. In this way, the index key size would be 110 bytes (55 * 2), and the index would still contain all the required columns. The following statement creates such an index.
USE AdventureWorks;
GO
CREATE INDEX IX_Document_Title
ON Production.Document (Title, Revision)
INCLUDE (FileName);
Index with Included Columns Guidelines
When you design nonclustered indexes with included columns consider the following guidelines:
- Nonkey columns are defined in the INCLUDE clause of the CREATE INDEX statement.
- Nonkey columns can only be defined on nonclustered indexes on tables or indexed views.
- All data types are allowed except text, ntext, and image.
- Computed columns that are deterministic and either precise or imprecise can be included columns. For more information, see Creating Indexes on Computed Columns.
- As with key columns, computed columns derived from image, ntext, and text data types can be nonkey (included) columns as long as the computed column data type is allowed as a nonkey index column.
- Column names cannot be specified in both the INCLUDE list and in the key column list.
- Column names cannot be repeated in the INCLUDE list.
Column Size Guidelines
- At least one key column must be defined. The maximum number of nonkey columns is 1023 columns. This is the maximum number of table columns minus 1.
- Index key columns, excluding nonkeys, must follow the existing index size restrictions of 16 key columns maximum, and a total index key size of 900 bytes.
- The total size of all nonkey columns is limited only by the size of the columns specified in the INCLUDE clause; for example, varchar(max) columns are limited to 2 GB.
Column Modification Guidelines
When you modify a table column that has been defined as an included column, the following restrictions apply:
- Nonkey columns cannot be dropped from the table unless the index is dropped first.
- Nonkey columns cannot be changed, except to do the following:
Change the nullability of the column from NOT NULL to NULL.
Increase the length of varchar, nvarchar, or varbinary columns.
Note
These column modification restrictions also apply to index key columns.
Design Recommendations
Redesign nonclustered indexes with a large index key size so that only columns used for searching and lookups are key columns. Make all other columns that cover the query included nonkey columns. In this way, you will have all columns needed to cover the query, but the index key itself is small and efficient.
For example, assume that you want to design an index to cover the following query.
USE AdventureWorks;
GO
SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
FROM Person.Address
WHERE PostalCode BETWEEN N'98000' and N'99999';
To cover the query, each column must be defined in the index. Although you could define all columns as key columns, the key size would be 334 bytes. Because the only column actually used as search criteria is the PostalCode column, having a length of 30 bytes, a better index design would define PostalCode as the key column and include all other columns as nonkey columns.
The following statement creates an index with included columns to cover the query.
USE AdventureWorks;
GO
CREATE INDEX IX_Address_PostalCode
ON Person.Address (PostalCode)
INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);
Performance Considerations
Avoid adding unnecessary columns. Adding too many index columns, key or nonkey, can have the following performance implications:
- Fewer index rows will fit on a page. This could create I/O increases and reduced cache efficiency.
- More disk space will be required to store the index. In particular, adding varchar(max), nvarchar(max), varbinary(max), or xml data types as nonkey index columns may significantly increase disk space requirements. This is because the column values are copied into the index leaf level. Therefore, they reside in both the index and the base table.
- Index maintenance may increase the time that it takes to perform modifications, inserts, updates, or deletes, to the underlying table or indexed view.
You will have to determine whether the gains in query performance outweigh the affect to performance during data modification and in additional disk space requirements. For more information about evaluating query performance, see Query Tuning.
See Also
Concepts
Creating Indexes (Database Engine)
Creating Indexes with Included Columns
General Index Design Guidelines
Index Design Basics
Maximum Size of Index Keys
Viewing Index Information