Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2007
    Posts
    41

    Unanswered: Regarding indexes

    What makes the difference between the below two create index statements????

    In my scenario, the second create index statement is giving me better performance.

    Conceptually, what is difference??? Why do we go for INCLUDE clause in the CREATE INDEX statement


    create index on
    (c1,c2,c3,c4,c5)

    create index on
    (c1,c2,c3)
    include (c4,c5)


    Thanks in Advance

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    All indexes must be considered in context of the DML statements acting upon them.
    Quote Originally Posted by frank.svs
    In my scenario, the second create index statement is giving me better performance.
    As such, there are queries for which the reverse would be true.

    Include columns are in the index leaf level only. The other columns are in the index B-Tree too. Essentially, this is just like the structure of a clustered index. The columns you specifiy for the index are in the B-Tree and the rest of the columns in your table appear only in the leaf pages.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Nov 2007
    Posts
    41
    So, how does the performance gets improved.

    Can you give me any small example supporting your argument which helps me lot.

    Thanks

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Again, it depends on the query. You would have to tell me your context.

    It also depends on the data. The two indexes are not so different if there are only 20 rows and c4 and c5 are bits. They are worlds apart if c4 and c5 are CHAR(200) and there are 5 billion rows.

    Post some more information (precise DDL, row counts, the queries you are seeing the improvement with) otherwise the best I can do is "the B-Tree is (most probably) smaller". I would bet if you SET STATISTICS IO ON you will see less IO for your second index.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    It could also be that the clustered index on the table is a pain to navigate (could be defined on several large columns).

    What the include statement does is includes the given columns at the lowest level of the index tree. This means these columns do not have to get sorted during inserts/updates, and do not clutter up the root pages of the index. It also means that the index has a greater chance of being used as a "covering index", so the Query Optimizer does not have to include a step to go back to the whole table to get the affected rows.

    I won't bother to give any sort of argument as to whether it should work or not. You seem to have an example, yourself.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by MCrowley
    It also means that the index has a greater chance of being used as a "covering index"
    There's no more chance the second index will be used as a covering index than the first based on the OP's original post.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Conceded .

  8. #8
    Join Date
    Nov 2007
    Posts
    41
    Thank you all

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •