Insert on a clustered index - is column ordering (ASC or DESC) important ?

  • Let's say that i have a clustered index with a INT column for which I insert data with a ever growing number. That column is not an identity column because that number is sequenced in another table as an identity.

    I want to know :

    if i specify my column in my clustered index as DESC, does that help to lower fragmentation or not ?

    What i mean is when i add data, is this data added on top or at the bottom of the index ? If it's added at then end, i'm guessing that my index will get fragmented fast ? Maybe SQL Server is aware of this and work accordingly ?

    Thanks for you time and input !

  • Added at the end (past highest current value in index) is about the best for minimising fragmentation and page splits. unless you absolutely need it desc for some queries, leave it.

    Added randomly throughout is what causes high page splits and fast fragmentation

    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
  • My goal of using DESC was to always have the most recent ID at the top. When the index is clean and not fragmented i would guess it would find the data for that ID sooner than if i used ASC ??

    What you are saying is that usually i should leave it ASC because new data is added at the bottom and therefore if i have that column in the clustered index at DESC, it would fragment my index continually ?

  • moris7 (3/10/2011)


    My goal of using DESC was to always have the most recent ID at the top. When the index is clean and not fragmented i would guess it would find the data for that ID sooner than if i used ASC ??

    using DESC will have the newest data at the beginning of the index (not the top), using ASC will have it at the end (indexes are a b-tree structure)

    There are few cases where there's a major speed difference. Feel free to test this situation out, but don't assume what the results would be without testing.

    What you are saying is that usually i should leave it ASC because new data is added at the bottom and therefore if i have that column in the clustered index at DESC, it would fragment my index continually ?

    End, not bottom. A clustered index on an ascending key minimises fragmentation. That's why it's one of the recommendations for a clustered index

    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
  • Thanks a lot Gail, i will do a test case later on today !

    Have a nice day !

  • That's indeed what i've found during my simple test. The only thing to check out now is the impact on queries of having that index changed.

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

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