Unnecessary Index?

  • Syed Jahanzaib Bin hassan (7/1/2011)


    Dont use Clustered index column in the NonClustered index this is overhead and leaf level of this nonclustered index will be same as your index when you will create the single column index on col2

    What is your assumption based on? Please provide more details.

    Based on what I can see, the leaf level of the clustered index will include col_1, col_2, col_3 and col_4 and the nonclustered index will only have col_1 and col_2. So how those can be identical?

    Also, please explain how including the clustered index column into a nonclustered index will add any overhead. If you don't mind, also explain how to perform a Bookmark Lookup without having the clustered index column(s) (I'm not talking about an RID lookup on a heap though).



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • GilaMonster (7/1/2011)


    GSquared (7/1/2011)


    The main advantage I see to the non-clustered index in this case, is that it will have more rows per page than the clustered index (since it's narrower), and if it's got everything you need for a query (covering index), then you're using less I/O, and less RAM, because of less pages.

    If the table was wider I'd agree with you, but 4 byte row (+ header & other bits) vs 12 byte row (+ header & other bits), not likely to make that much difference (unless we're talking billions of rows)

    Yeah, but it's the only advantage I can see at all to that index. Probably not significant in most cases, but could be in some.

    - 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

  • Thanks to all who answered. It is truly appreciated.

    Chris

  • You're welcome.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • No it's not. SQL includes the clustering key in all nonclustered indexes anyway whether you specify it or not

    I agreed,but there is no need to mentioned in the nonclustered index,it will not take pointer space in the nonclustered index file ?

    I don't agree with that. These are not the same. It depends on the query.

    you can use sp_helpindex8 from the http://www.SQLSKILLs.com then it will give you an idea about leaf level of the index then you can remove or add indexes with the help of this new helpful store procedure

    Regards,
    Syed Jahanzaib Bin Hassan
    BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

    My Blog
    www.aureus-salah.com

  • Syed Jahanzaib Bin hassan (7/4/2011)


    No it's not. SQL includes the clustering key in all nonclustered indexes anyway whether you specify it or not

    I agreed,but there is no need to mentioned in the nonclustered index,it will not take pointer space in the nonclustered index file ?

    If it's needed it should be mentioned. In a scenario like the following the clustering key CANNNOT be omitted without changing the definition of the index:

    CREATE CLUSTERED INDEX idx_C on T (Col1)

    CREATE NONCLUSTERED INDEX idx_1 ON T (Col1, Col2)

    In that case (which matches the OP's question) omitting Col1 from the nonclustered index changes the index's definition.

    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
  • CREATE NONCLUSTERED INDEX idx_1 ON T (Col1, Col2)

    the leaf level will be Col1,Col2

    CREATE NONCLUSTERED INDEX idx_1 ON T (Col2)

    the leaf level will be same too as Col2,Col1

    for the Leaf Level of this current scenerio

    http://sqlconsultant.files.wordpress.com/2011/07/leaf-level.jpg

    for the sp_helpindex8

    http://www.sqlskills.com/BLOGS/KIMBERLY/category/sp_helpindex-rewrites.aspx

    this SP is very helpfull to remove duplicate indexes with the help of

    [column in tree]

    [column in leaf]

    Regards,
    Syed Jahanzaib Bin Hassan
    BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

    My Blog
    www.aureus-salah.com

  • Syed Jahanzaib Bin hassan (7/4/2011)


    CREATE NONCLUSTERED INDEX idx_1 ON T (Col1, Col2)

    the leaf level will be Col1,Col2

    CREATE NONCLUSTERED INDEX idx_1 ON T (Col2)

    the leaf level will be same too as Col2,Col1

    An index on Col1, Col2 is not the same as an index on Col2, Col1. Column order in indexes matters a lot. They are different indexes with different usages.

    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
  • Syed Jahanzaib Bin hassan (7/4/2011)


    CREATE NONCLUSTERED INDEX idx_1 ON T (Col1, Col2)

    the leaf level will be Col1,Col2

    CREATE NONCLUSTERED INDEX idx_1 ON T (Col2)

    the leaf level will be same too as Col2,Col1

    for the Leaf Level of this current scenerio

    http://sqlconsultant.files.wordpress.com/2011/07/leaf-level.jpg

    for the sp_helpindex8

    http://www.sqlskills.com/BLOGS/KIMBERLY/category/sp_helpindex-rewrites.aspx

    this SP is very helpfull to remove duplicate indexes with the help of

    [column in tree]

    [column in leaf]

    If the selectivity on Col2 is different than Col1, then those two indexes will NOT perform the same in many cases.

    - 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

Viewing 9 posts - 16 through 23 (of 23 total)

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