SQL - Index with included columns Index

This feature has been introduced with SQL Server 2005 which intends to the functionality of nonclustered indexes by adding nonkey columns to the leaf level of the nonclustered index. Nonkey columns can be used to create cover indexes. 

By using of nonkey columns, easy to create nonclustered indexes that will cover more queries and 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. 

The Database Engine does not consider nonkey columns when calculating the number of index key columns or index key size.

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.

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 into nonkey columns. In this way, you will have all columns needed to cover the query, but the index key itself is small and efficient.
• 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.

Syntax for Index with included columns Index

USE tempdb; GO -- Create a new table with three columns. CREATE TABLE Employee     (Name varchar(30) NULL,      Address varchar(50) NULL,      City Varchar(20) Null                  ); GO --- Creates a nonclustered index on the Employee Table --- having three included (nonkey) columns. --- index key column is Name --- and the nonkey columns are Address, City CREATE NONCLUSTERED INDEX Ind_Employee_EmpName     ON dbo.Employee (Name)                 INCLUDE (Address, City) GO ---- How to see the indexes in the table EXEC sp_helpindex Employee ---Insert value into the table Insert into Employee Values (1, 'Ryan Arjun', 'India') ---- alter non-clustered index ALTER INDEX Ind_Employee_EmpName ON Employee REORGANIZE; ---- Check table size into the database EXEC  sp_spaceused Employee ---- drop employee table drop table Employee

If a column - that is added as an included-column of index - is used in the restriction: As long as the index as such can be used (based on restriction against index-key-columns) - then Sql-server is matching the column-restriction against the index (leaf-node-values) instead of going the expensive way around the table itself.

Limitations and Restrictions

• Nonkey columns can only be defined on nonclustered indexes.
• Data  types such as text, ntext, and image cannot be used as nonkey columns.
• Computed columns that are deterministic and either precise or imprecise can be nonkey columns.
• Computed columns derived from image, ntext, and text data types can be nonkey columns as long as the computed column data type is allowed as a nonkey index column.
• Nonkey columns cannot be dropped from a table unless that table’s index is dropped first.
• Nonkey columns cannot be changed, except to do the following:

1. Change the null ability of the column from NOT NULL to NULL.
2. Increase the length of varchar, nvarchar, or varbinary columns.

Type of Indexes in SQL

The following types of indexes are available in SQL Server-

1)  Clustered Index

2)  Nonclustered Index

3)  Unique Index

4)  ColumnStore Index

5)  Index with Included Columns Index

6)  Index on Computed Columns Index

7)  Filtered Index

8)  Spatial Index

9)  XML Index

10) Full-text Index
11) Indexed View
12) Partition Indexes