[dba-SQLServer] Covering Indexes

Arthur Fuller fuller.artful at gmail.com
Wed Jul 23 12:32:32 CDT 2008


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
BESTAction_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



More information about the dba-SQLServer mailing list