Post reply

Benefits of indexes with included columns

  • July 15, 2010 at 8:50 am

    #221345

    Hello

    As part of an investigation into some long running queries I created a trace in Profiler and then submitted this to the Database Engine Tuning Adviser to see what suggestions it might come up with.

    It suggested creating a number of indexes and statistics that look reasonable, but it also suggested some indexes that, to me, don't make sense. For several tables it suggested creating a non clustered index on Col1 and include Col2, Col3, Col4 etc. At first glance this looks OK but when I check the existing indexes on the table Col1 is already the unique clustered index.

    I did wonder if despite regular re-indexing the indexes had become so fragmented that they were of no use, but the ones I have checked are less than 1% fragmented.

    It might be my lack of understanding, but is there any benefit in creating a non clustered index on the same column as the clustered index?

    I would be grateful for any advice.

    Thanks in advance

    Ian

  • July 15, 2010 at 10:15 am

    #1193612

    Salmon Hunter (7/15/2010)

    but is there any benefit in creating a non clustered index on the same column as the clustered index?

    Virtually none.

    It will be smaller, hence you may get a slight performance improvement. It's unlikely to be much and it's not common for the minor perforance gain to be worth the additional storage and overhead on modifications

    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

  • July 16, 2010 at 4:33 am

    #1193986

    Thanks Gail, that has confirmed what I thought but was not sure about.

Viewing 3 posts - 1 through 2 (of 2 total)

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