SQL Server 2005 extends the functionality of non clustered indexes by adding non key columns to the leaf level of the non clustered index using the INCLUDE option in the CREATE INDEX statement. These INCLUDE index option is a slight variation of covering index for improved performance. By including non key columns, you can create non clustered indexes that cover more queries. The benefits of using the INCLUDE option (also called non key non clustered index) in the INDEX are
Advantages
- All data types are supported, except text, ntext, and image. So more data type options than a covering index.
- The maximum number of columns that can be included is 1024, where as only 16 in covering indexes.
- Included Columns are not considered by the Database Engine when calculating the number of index key columns or index key size.The actual index is narrower so the key can be more efficient and can offer better performance where as in covering index all of the columns are part of the key.
The include columns indexes are also having the same disadvantages of the covering indexes such as
- More space is required to store indexes with non key columns. Non key column data is stored at both the leaf level of the index and in the table itself.
- Larger indexes mean fewer rows can fit on a page, potentially increasing disk I/O.
- Index maintenance is increased for data modifications, potentially hurting performance if non key columns are large and the database experiences a high level of data modifications.
Syntax for INCLUDE column
CREATE INDEX IX_INDEX1
ON dbo.Employee (KEYCOLUMN1)
INCLUDE (NONKEYCOLUMN1, NONKEYCOLUMN2, AND NONKEYCOLUMN3);
However care should be taken before converting the covering indexes to non key / include column indexes. The execution plan of both the INDEX options should be compared before deciding the best INDEX option.
