Covering Indexes

Covering index is a new type of index developed in SQL Server 2005.

It helps in covering queries means Indexes With Included Columns without primary key.They are used for frequent queries.

Indexes with Included Columns are nonclustered indexes that have the following benefits.

1. Columns defined in the include statement means columns included in the where clause, called non-key columns, are not counted in the number of columns by the Database Engine.

2. Columns which are previously not be used in queries, like nvarchar(max), can be included as a non-key column.

3. Maximum limit of non key columns we can add in covering index is 1023.

4. As with traditional non-clustered indexes, the non-key columns are added to the leaf level of the index, meaning these indexes have the same affect on disk space, I/O (due to index maintenance), and cache efficiency. This should be taken into account when contemplating index creation using included columns.

Pawan Kumar