Covering Index Vs Composite Index

  • Hi,

    I have a table with 3 columns.

    Table Name MyTable

    Columns are ID, Name, Address.

    There are two scenario I want to draw.

    1. I am creating an non-cluster index on ID, Name.

    2. I am creating an non-cluster index on ID and covering Name.

    For both the scenario I am not getting any difference in the execution plan.

    So what is the purpose / exact utilization scope of the covering index?

    Thanks in advance.

    Regards

    Arijit

  • Could you include you query? Is this the only query that will be executed or are there other queries?

    - Andras


    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • A covering index is an index that contains all the columns that a query references. An index may be covering for one query and not for another.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • You're misusing the word "covering" here. I think you mean "including".

    A column that's "included" in an index is kept at the bottom level of the index, but not in the upper levels of it. That makes it good for "select" and less useful for "where" or "join".

    Is "include" what you actually mean?

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • A covering index just means that the query does not have to go to the table for any attribute values. The attributes can either be part of the index, or just part of the included attributes.

    The more you are prepared, the less you need it.

  • Thanks All,

    It is really making sence to me. Thanks again for your grest advices.

    Regards

    Arijit

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply