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.


Related Articles

Using Included Columns in SQL Server 2005 (14 January 2008)

SQL Server Indexes: The Basics (26 November 2007)

Using Indexed Computed Columns to Improve Performance (16 January 2007)

The Cost of GUIDs as Primary Keys (8 January 2005)

Microsoft SQL Server 2000 Index Defragmentation Best Practices (2 May 2004)

Optimizing Performance / Indexes on Temp Tables (5 January 2004)

MSDN: Improving Performance with SQL Server 2000 Indexed Views (5 October 2000)

MSDN: Index Tuning Wizard for Microsoft SQL Server 2000 (4 October 2000)

Other Recent Forum Posts

Missing Range from SQl Table - TSQL (3h)

Insert trigger to run an ASPX script passing record columns as parameters (20h)

Find the closest date with associated column (1d)

SQL Query Help with roll up (1d)

Need help creating trigger to capture single and multiple updates (1d)

Books and Resources (1d)

Update existing Flag based on the date (1d)

Convert varchar to time (1d)

- Advertisement -