Using a Covered Index

By Bill Graziano on 7 September 2000 | Tags: Indexes

Sandro writes "I learned that if I have an SQL statement such as "SELECT Code,Name,Price FROM MyTable WHERE Code=1234" it would increase the performance to have a single index that would include all the fields needed: Code, Name and Price. Is this true? Does the order of the fields matter? Thank you."

You've described a covered index. It's named because the index "covers" all the fields in the select statement. It can improve performance but only in certain situations and I don't think this is one of them.

Indexes are best when they are very small. Integers make good fields for indexes. An INT is four bytes long. These use less space to construct the index and result in fewer disk reads (or cached disk reads) to select a record. A smaller index also needs less memory to cache the first few levels of the tree. Books Online has an article on "Index Architecture" you can look at that describes indexes in some detail.

I think since your index would include the NAME field it might not make a good covering index. I'm guessing the NAME is a character field. Unless you are selecting or sorting based on name I woudn't include it in the index.

If you had many queries that just needed CODE and PRICE that might be a possible covered index. You'd need a large number of queries to make this worthwhile though.