Using a Covered Index

By Bill Graziano on 7 September 2000 | 4 Comments | 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.

Discuss this article: 4 Comments so far. Print this Article.

If you like this article you can sign up for our weekly newsletter. There's an opt-out link at the bottom of each newsletter so it's easy to unsubscribe at any time.

Email Address:

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

No results (0 Replies)

Converting 'yyyy-mm-dd' to 'dd/mm/yyyy' (3 Replies)

doc related to migration of DB oracle to sql serve (6 Replies)

migration oracle with XML to sql server (4 Replies)

system stored procedure to script table (4 Replies)

SELECT DISTINCT but up to 3? (5 Replies)

How to recover data from corrupt SQL database? (3 Replies)

DateAdd (2 Replies)

Subscribe to SQLTeam.com

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -