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

T-sql - we created Message from app1 and trying to disable from app2 (2h)

SQL select Top 10 records for unique combination of two columns (16h)

SSRS Report Sorting with Grouping Issue (1d)

ORA-01476: divisor is equal to zero (1d)

Create new columns based on min and max values of a record with multiple rows (1d)

Memory Required for Reporting Services 2022 (1d)

Backup sql server large db on cloud (2d)

Selecting x columns but only displaying y columns (2d)

- Advertisement -