SQL Server Performance

Composite Index VS. Covering Index

Discussion in 'SQL Server 2005 General Developer Questions' started by Radhika143, May 18, 2009.

  1. Radhika143 New Member

    Hi ...
    I have a doubt in my mind for above topic. I just read above index article but unable to find out what is the real difference between the Composite Index and Covering Index?
    Moreover I am unable to figure out at what circumstances I should use this?
    Can anybody please explain me properly so i will be grateful to him/her?
    UR's
    Radhika.
  2. dhamu294 New Member

  3. FrankKalis Moderator

    To add to dhamu294's link:
    A composite index is just an index defined over more than one column. It may or may not be a covering index as well.
    A covering index is an index that "covers" a query. That is it supplies all information for the query so that SQL Server does not have to touch the base tables. It may or may not be a composite index.
    Indexing is probably more of an art than a science and there is always a trade-off to consider, because index are beneficial for data retrieval operations but detrimental to performance in data manipulation operations. Finding the right balance here is your task and it's not an easy one. It requires extensive testing, and to a large degree "trial and error".
    [:)]
  4. Radhika143 New Member

    FrankKalis & dhamu,
    Nice to see your reference and explanation.
    Frank Can you please tell me the syntax for the both index?
    How to create composite index and how to create covering index?
    Please show me here.
    Thank you,
    Radhika.
  5. Radhika143 New Member

    FrankKalis & dhamu,
    Nice to see your reference and explanation.
    Frank Can you please tell me the syntax for the both index?
    How to create composite index and how to create covering index?
    Please show me here.
    Thank you,
    Radhika.
  6. FrankKalis Moderator

    There is no difference in syntax, because you can not create an index as "covered". You still use the standard CREATE INDEX command.Think of a covering index probably more like a "logical" construct on top of a physical one (if I can say so).
    It really depends on the query if a composite index can act as a covering index. Simplified example:
    Say you have a table T with col1, col2, and col3).
    When you now create an index on T (col1, col2), you have a composite index because the index consists of 2 columns.
    Suppose now you have a query like:
    SELECT col1, col2
    FROM T
    WHERE col1 = something AND col2 = something else
    For such a query the newly created index could act as covering index, because SQL Server can satisfy the query by just touching the index. There is no need to touch the underlying base table T. That is the index "covers" the query".
  7. dhamu294 New Member

    Ya that is very good eg from frank, Hope it will clear you now
  8. Radhika143 New Member

    FrankKalis, Thanks Man.
    now I am clear on the concept except [quote user="FrankKalis"]
    because SQL Server can satisfy the query by just touching the index. There is no need to touch the underlying base table T
    [/quote] what you mean by touching the index...??
    dhamu294, ya its good ex from Frank... I can see it....
    Thank you,
    Radhika.
  9. Adriaan New Member

    An index actually stores a copy of the data from the indexed columns.
    We say that an index is covering for a query when the query can read all the relevant data from the index, without having to read anything from the table.
  10. madhuottapalam New Member

    You already had enough KT on indexes. NOw its my turn ... :). As Frank already mentioned there is no ideal index architecture for any env or table. Complex index is nothing but the index which have more than one index key or column. Covering indexes are those which created to support specific query(s). Consider one table have 200 columns (very normal in legacy applicaiton) and what your query need is only 5-10 columns. Now if you see the execution plan for this query , since there is no indexes except Clustered Index (CI) , you can see a CI scan or seek (depends upon the rows to be return). CI scan is as good as table scan , you are going through all the data pages to get the required result. Traversing through all the rows which have 200 columns is not optimal. Now as a DB professional what you do is, create a covering index for this query which have let us say 10 columns. Covering index will contain all the columns in Select/where/join etc. So in this case you will have all the 10 column in the index . So indexes are nothing but auxillary table which stores subset of column value. Now you have index with 10 column which covers this query, if you run this query you can see that it uses this index to fetch the result.
    Now the benifit here is , you dont needs to traverse through all the datapages which have 200 column instead you traverse through the index pages which have 10 columns. Less IO so more performance.
    Hope it is more clear now
    Madhu
  11. FrankKalis Moderator

    [quote user="madhuottapalam"]
    You already had enough KT on indexes. NOw its my turn ... :).
    [/quote]
    Okay, I'll bite then.[:)]
    What does "KT" mean please?
  12. Adriaan New Member

  13. FrankKalis Moderator

    [quote user="Adriaan"]
    http://en.wikipedia.org/wiki/KT has it as "knowledge transfer".
    For a moment there, I thought it might have similar usage as "BS".
    [/quote]
    Hehe, thanks! Not going to ask which "BS" meaning you have in mind.
  14. Adriaan New Member

    Farmers have been using BS since the dawn of ages.

Share This Page