[dba-SQLServer] Covering Indexes

Asger Blond ab-mi at post3.tele.dk
Fri Jul 25 18:32:30 CDT 2008


Agree.
Elaborating this:
Planning an index you have to distinguish two separate operations performed
by the query engine: criteria search and data capture.
For criteria search, column order is essential: if you create a compound
index then always place the supposed search-column in front.
For data capture, covering index is essential: a covering index means that
the wanted data can be captured inside the index, not forcing the engine to
make a bookmark lookup to the data pages outside the index.
To make the scenario more fascinating Arthur also mentioned an
index-creature called "included column". This is new to SQL Server 2005: an
included column is added to the leaf level of the index. This means that the
included column will aid the possibility of our index being a "covering
index" for queries, but it will lend no assistance in the search operation.

Conclusion: For compound indexes, always place the supposed search-column in
front. For includes columns, don't expect a performance gain for search
operations, but enjoy a performance plus for data capture.

Asger
-----Oprindelig meddelelse-----
Fra: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af Nancy Lytle
Sendt: 23. juli 2008 20:14
Til: Discussion concerning MS SQL Server
Emne: Re: [dba-SQLServer] Covering Indexes

Yes, my understaning is that column order does matter.Nancy Lytle
N_Lytle at terpalum.umd.edu



 EMAILING FOR THE GREATER GOODJoin me

> Date: Wed, 23 Jul 2008 14:32:32 -0300> From: fuller.artful at gmail.com> To:
dba-sqlserver at databaseadvisors.com> Subject: [dba-SQLServer] Covering
Indexes> > I have always thought that you should have an index for the PK
and every FK,> and perhaps also one or more compound indexes for frequent
searches. But> recent reading has given me pause, in particular the topic of
covering> indexes to avoid bookmark lookups, and also of the order of
columns in an> index. For example, here are the existing indexes on one of
my tables:> > Index Columns Included Columns PK_BESTTrade BESTTrade_ID>
IX_BESTTrade__BSID_PID_Act_BIID_BA_ID BESTSecurity_ID TransactionStatus_ID>
Portfolio_ID EstimatedShares> IsActive QuantityExecuted> BESTIdea_ID> >
BESTAction_ID> IX_BESTTrade__PID_Act_BAID_BSID_BIID Portfolio_ID
TransactionStatus_ID> IsActive SizeEuro> BESTAction_ID EstimatedShares>
BESTSecurity_ID QuantityExecuted> BESTIdea_ID>
IX_BESTTrade__PID_BAID_BIID_Act_TSID Portfolio_ID Currency_ID> BESTAc!
 tion_ID SizeEuro> BESTIdea_ID Consideration> IsActive> >
TransactionStatus_ID> > Given the redundancies (columns repeated in a
different order) in the> second, third and fourth indexes listed, I'm
wondering whether it might make> more sense to have just one index besides
the PK that contains and includes> one instance of each column mentioned.
Another way to ask the question is,> does column order in an index matter?
Won't the cost analyzer figure out> that it can make use of the index
anyway?> > Arthur> _______________________________________________>
dba-SQLServer mailing list> dba-SQLServer at databaseadvisors.com>
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver>
http://www.databaseadvisors.com> 
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com






More information about the dba-SQLServer mailing list