[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> 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> _______________________________________________> dba-SQLServer mailing list> dba-SQLServer at databaseadvisors.com> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver> http://www.databaseadvisors.com>