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.
| 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. |
 |
|
|
|
|
|
Article: Using a Covered Index