SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Using a Covered Index
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 08/15/2000 :  21:31:12  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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.

Anonymous
Starting Member

0 Posts

Posted - 09/08/2000 :  10:21:19  Show Profile  Reply with Quote
Order of columns in an index

Here's some additional info that might help. An index is only useful to a query if the WHERE clause of the query matches the column(s) that are leftmost in the index. So if you create a composite index, such as "City, State", then a query such as "WHERE City = 'Springfield'" will use the index, but the query "WHERE STATE = 'MO'" will not use the index.

Brad M. McGehee
http://www.sql-server-performance.com

Go to Top of Page

sqlguru
SQLTeam CoFounder

USA
20 Posts

Posted - 09/08/2000 :  15:33:53  Show Profile  Visit sqlguru's Homepage  Reply with Quote
More on covering indexes

Generally, covered indexes work best in a situation where a query is going to return a relatively small set of data from a table and the overall row size is large.

For instance, let's say you have a table like the following:
Yak
-----

YakID
YakName
(lots of other attributes about the Yak)

If you needed to do a query like
SELECT YakName, YakID
FROM Yak
WHERE YakName like 'Graz%'

Then a covering index on YakName, YakID (as opposed to just an index on YakName) would be beneficial, because the QP wouldn't need to go to the data page to retrieve the YakID... it could just get it from the index.

An inappropriate covered index is often worse than just a good index on the table coupled with fetches to the data page. This is because a covered index will tend to have a larger index key, which wastes space in the database and will slow the index access.

When in doubt, try the query both with and without the covered index and see which one results in less page I/O.

-SQLGuru

Go to Top of Page

Anonymous
Starting Member

0 Posts

Posted - 09/08/2000 :  17:47:53  Show Profile  Reply with Quote
Follow-up to my first comment

I did some research on this topic, and found that if the WHERE clause in a query does not specify the first column of an available index (which normally disqualifies the index from being used), if the index is a composite index and contains all of the columns referenced in the query, the query optimizer can still use the index, because the index is a covering index.

My first post focused on non-covering indexes.

Brad M. McGehee
http://www.sql-server-performance.com

Go to Top of Page

caractacus
Starting Member

2 Posts

Posted - 03/22/2006 :  11:52:43  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000