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