Home Forums SQL Server 2008 SQL Server 2008 - General Effect of Clustered Index on Non-Clustered Index RE: Effect of Clustered Index on Non-Clustered Index

  • July 6, 2013 at 11:12 pm

    #1630260

    GilaMonster (7/6/2013)

    Jeff Moden (7/6/2013)

    If there is no unique clustered index, then an 8 byte "uniquefier" is also added to the index.

    Not quite

    If there's no clustered index, the 8-byte RID is added to all nonclustered indexes as the row location. That's all that's added and it's only when there's no clustered index at all. It's not a uniquifier, it's the physical RID, the combination of File ID, Page Number, Slot index.

    Same holds true for when a non-unique clustered index is used except it's worse. In such cases, the columns of the clustered index are added to the columns of the non-clustered index and then the 8 byte uniquifier is added to that which could make a single column clustered index quite wide and comparatively slow.

    When there is a clustered index (of any form) then the clustered index key (the entire of it) is added to all nonclustered indexes as the row location. When a clustered index is not unique, the clustered index key gets an additional nullable 4-byte uniquifier added to the clustered index key (only actually has a value on duplicate rows). Since it's part of the clustered index key, the uniquifier is added to all nonclustered indexes as well.

    I certainly blew that one out of my nose. :blush: Thank you VERY much for the education, Gail.

    Back to the books.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)