[dba-SQLServer] Covering Indexes

Perhaps you can help me understand the distinction between columns indexed
and columns included. Frankly I don't understand the difference. I could for
example create an index on PK column, and decide to include columns a, b, c.
What does that actually mean?

Am I better off creating separate indexes for a, b and c? Or should I
include them?

Let's suppose that a typical query does Where colA = @colA and colb = @colb
and colC = @colC.

Some of the reading I've done suggests that the index ought to be ordered in
terms of frequency of hits. (i.e. a column called IsActive which is a bit
column should be the last examined) -- this says look for the least
frequency first (Portfolo_ID for example) and then include IsActive.

I dont' yet understand the difference between declaring a column as part of
an index and including a column.

If you have any insights into this distinction they would be most
appreciated.

Thanks!
A.

On Fri, Jul 25, 2008 at 8:32 PM, Asger Blond <ab-mi at post3.tele.dk> wrote:

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