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

Checkmark for guaranteed SR = WR (11h)

How to connect to git in SQL Server 2016/2017 without using any third party tool (11h)

Sql restart (16h)

Excel column wise data save in rows (1d)

Date timzone conversion (2d)

Object cannot be cast from DBNULL to other types coming randomly in SSIS Package-Migrated from VS 2008 to 2015,SQL 2008R2 to SQL2016 on 1st run only (2d)

Error in sp procedure- Msg 50000, Level 16, State 1, Procedure spCheckDBInfo, Line 193 [Batch Start Line 0 (2d)

Two records into a single record? (2d)

- Advertisement -