Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Site Related Forums
 Article Discussion
 Article: Using a Covered Index

Author  Topic 

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2000-08-15 : 21:31:12
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."

Article Link.

caractacus
Starting Member

2 Posts

Posted - 2006-03-22 : 11:52:43
If the leftmost index columns are not specified in your query, the index can still be scanned.

Index scanning involves sequentially reading through the index leaves. When the time required to do this exceeds the query timeout, your query will fail. Index scanning is a scalability concern and when the number of rows in a table can get very large, should be avoided at all costs.

If the leftmost index columns are specified, keyed lookup provides rapid access to the relevant index leaves. If the columns required by the query are in the leaves (SQL2005 provides a neat feature for including additional columns in the leaves) then a bookmark lookup is not required (this means lookup of the row in the clustered index).

Covering indexes are more accessible in SQL2005 due to the included index column feature. When large numbers of bookmark lookups can be avoided this is a sound strategy, especially if index scanning is avoided.



Go to Top of Page
   

- Advertisement -