Covering index in sql server

Covering indexes are one of the best feature in nonclustered indexes. Before covering indexes were introduced if you want multiple column search in some queries then you have to create nonclustered index on multiple column. And now specify you first column for nonclustered key value and rest column list goes with include clause.

Covering indexes are nonclustered index with include clause. Because creating wide or composite nonclustered index is lack of performance compare to covering index because it requires more storage space at top level and intermediate level of index b-tree.

CREATE NONCLUSTERED INDEX <index-name> ON <table-name> (<column-name>)
INCLUDE (<Columns>)

Above covering index is much faster in terms of execution compare to

CREATE NONCLUSTERED INDEX <index-name> ON <table-name> (<columns>)

We can take example:

Create table AB
(
    id int,
    name nvarchar(255)
)


create nonclustered index IX_AB on AB(id,name)

select * from AB where id = 1 and name = 'AA'



create nonclustered index CIX_AB on AB(id) include(name)

select * from AB where id = 1 and name = 'AA'