Index sort order

Question

Index sort order

terry999

SSCertifiable

Points: 5036
More actions
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

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

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

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 1

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.