The Ultimate Index Lister (Non-Pivoted)

  • YeshuaAgapao

    Hall of Fame

    Points: 3495

    Comments posted to this topic are about the item The Ultimate Index Lister (Non-Pivoted)

  • wchaster

    Old Hand

    Points: 392

    Where does this SP need to be created, I created it in Master and it only returns result from the dbo or sys schema's.

    😀
    ACN is the world's largest direct seller of telecommunications and essential services.
    http://helpu.acndirect.com/
    ACN Digital Phone Service customers automatically enjoy unlimited calling to 60 landline
    destinations around the world, including India, Mexico and the UK!
    :hehe:

  • YeshuaAgapao

    Hall of Fame

    Points: 3495

    You need to place it in the database you need to get results for. Had to make it that way to be sure to be able to get table names.

  • Manie Verster

    SSCertifiable

    Points: 7022

    Great article! This came at just the right time for me! I was trying to do exactly this but got stuck but now, thanks to you!, I don't need to battle anymore. I have one question. I am trying to understand exactly what indexes are and what impact they can have on the database and yes, I think I have that but what I don't quite get is the Included Columns. What impact can they have on a table or the database as a whole? I have, with the help of your sp, identified a table of which the indexes looks cosher but one index have nearly all of the table's columns as included columns. Is this right?

    :-PManie Verster
    Developer
    Johannesburg
    South Africa

    I can do all things through Christ who strengthens me. - Holy Bible
    I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)

  • YeshuaAgapao

    Hall of Fame

    Points: 3495

    Included columns are available only to cover to query (prevent bookmark lookups), but not for seeks. Included columns can assist in exploiting the fact that clustered indexes append its key as seeks (non-unique indexes; it appends as includes for unique indexes) at the end by limiting columns only needed to prevent bookmark lookups to the leaf level. I have several indexes with one or two seek columns and 3+ include columns (for covering the query) that rely on the clustered index key for 3rd or 4th seek columns. If these 'cover the query' only columns were seeks, it would ruin clustered index exploitation for additional seek columns.

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the script.

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

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