Post reply

Index sort order

  • March 11, 2011 at 3:37 am

    #248034

    Hi

    I have tblOrder table with clustered PK on OrderID which is an autoincrementing int field

    This is a foreign key in tblOrderDetails.

    In tblOrderDetails there is an non-clustered index on OrderID DESC

    Most of the queries I will write queires they will ALWAYS order by OrderID Desc i.e. most recent first.

    What are the implications of creating the 2 OrderID indices as DESC?

    1. In tblOrder will inserts be even slower/fragmented because the next OrderID is always the max current value and has to be inserted at the front rather than end if it were ASC.

    2. In tblOrderDetails Desc should just have the desired effect of being faster than an ASC index. No fragmentation issues

    Thanks Terry

  • March 11, 2011 at 5:59 am

    #1297563

    terryshamir (3/11/2011)

    I have tblOrder table with clustered PK on OrderID which is an autoincrementing int field

    This is a foreign key in tblOrderDetails.

    In tblOrderDetails there is an non-clustered index on OrderID DESC

    Most of the queries I will write queires they will ALWAYS order by OrderID Desc i.e. most recent first.

    What are the implications of creating the 2 OrderID indices as DESC?

    1. In tblOrder will inserts be even slower/fragmented because the next OrderID is always the max current value and has to be inserted at the front rather than end if it were ASC.

    2. In tblOrderDetails Desc should just have the desired effect of being faster than an ASC index. No fragmentation issues

    DESCending order indexes may help to avoid sorting when affected index is a multi-column one. For single column indexes SQL Server engine is able to navigate the index in both directions depending on the ORDER BY clause of each query.

    Bottom line is, for single column indexes do not bother in over engineering and define index as ASCending.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

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

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