Article Link.

Here's some additional info that might help. An index is only useful to a query if the WHERE clause of the query matches the column(s) that are leftmost in the index. So if you create a composite index, such as "City, State", then a query such as "WHERE City = 'Springfield'" will use the index, but the query "WHERE STATE = 'MO'" will not use the index.

Brad M. McGehee
http://www.sql-server-performance.com

Generally, covered indexes work best in a situation where a query is going to return a relatively small set of data from a table and the overall row size is large.

For instance, let's say you have a table like the following:
Yak
-----
YakID
YakName
(lots of other attributes about the Yak)

If you needed to do a query like
SELECT YakName, YakID
FROM Yak
WHERE YakName like 'Graz%'

Then a covering index on YakName, YakID (as opposed to just an index on YakName) would be beneficial, because the QP wouldn't need to go to the data page to retrieve the YakID... it could just get it from the index.

An inappropriate covered index is often worse than just a good index on the table coupled with fetches to the data page. This is because a covered index will tend to have a larger index key, which wastes space in the database and will slow the index access.

When in doubt, try the query both with and without the covered index and see which one results in less page I/O.

-SQLGuru

I did some research on this topic, and found that if the WHERE clause in a query does not specify the first column of an available index (which normally disqualifies the index from being used), if the index is a composite index and contains all of the columns referenced in the query, the query optimizer can still use the index, because the index is a covering index.

My first post focused on non-covering indexes.

Brad M. McGehee
http://www.sql-server-performance.com