Composite index Vs Covering Indexes

Question

Hi I want to know about Composite index Vs Covering Indexes in case of NonClustered indexes. Which one is better performance wise? What are the scenarios for choosing the correct one?

Nilesh Shah · Answer

A composite index is just an index defined over more than one column. It may or may not be a covering index as well. 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 FROM T WHERE col1 = something AND col2 = something else 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.

Gagan Sharma · Answer

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.