Composite index Vs Covering Indexes

Hi Nilesh, 

Thanks for you reply. I want to know how covering index with inculde is efficent than compiste indexes?

 

How do i decide to create composite index or covering index with Include?

Please provide few examples if possible. 

 

A covering index is an index that "covers" a query. That is it supplies all information for the query so that SQL Server does not have to touch the base tables. It may or may not be a composite index.

There is no difference in syntax, because you can not create an index as "covered".

 

You still use the standard CREATE INDEX command.Think of a covering index probably more like a "logical" construct on top of a physical one (if I can say so).

 

It really depends on the query if a composite index can act as a covering index. Simplified example:

 

Say you have a table T with col1, col2, and col3).

 

When you now create an index on T (col1, col2), you have a composite index because the index consists of 2 columns.

 

Suppose now you have a query like:

 

SELECT col1, col2

 

For such a query the newly created index could act as covering index, because SQL Server can satisfy the query by just touching the index.

 

There is no need to touch the underlying base table T. That is the index "covers" the query".