Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

When creating an index one can determine if an index column is sorted using ascending or descending order. BOL says that

[ ASC | DESC ]
Determines the ascending or descending sort direction for the particular index column. The default is ASC.

What is the primary reason for choosing between ASC or DESC?

share|improve this question
add comment

4 Answers

up vote 5 down vote accepted

Since indexes can be scanned in both directions most of the time there isn't much reason for choosing DESC. Some TOP queries could be helped by the DESC on some of the columns of a covering index but only testing will show if this helps.

One other difference is that the forward order scan can be run in parallel but the reverse order scan is always single threaded.

share|improve this answer
    
Do you know what the system does to optimise the physical I/O on a descending scan so it plays nicely with the disk rotation? –  ConcernedOfTunbridgeWells Jan 6 '12 at 14:25
    
The controllers and the drives themselves can re-order the requests, on SATA drives this is refered to as Native Command Queueing sata-io.org/technology/ncq.asp –  MartinC Jan 6 '12 at 14:36
    
So presumably it just issues scatter-gather I/O requests and lets the I/O subsystem return in whatever order it gets the data. –  ConcernedOfTunbridgeWells Jan 6 '12 at 15:15
add comment

If you have a frequently used query that returns items in descending order then you might want to create the index with a column ordered DESC.

An example of where this might be useful is with date stamped transactions or history where you frequently issue queries where you want to see the most recent ones first. However, in most cases you probably want to use the default, which is ASC.

share|improve this answer
add comment

Another thing that needs consideration is when the index is a clustered one. You generally want to have new rows added at the end of a b-tree index, to avoid fragmentation. So, it would usually be better to have ASC and not DESC for a clustered index, e.g. for an IDENTITY or a DATETIME one.

Unless off course you have a column like DaysTillTheEndOfTheWord INT, where new rows will have smaller values that older rows. A clustered index should better be DESC in that case.

share|improve this answer
add comment

There's a longer explanation on Technet.

share|improve this answer
    
Welcome to the site @Wojteq. Link only answers are not really encouraged though. Please try to expand with at least a short description of what this link has. –  ypercube Dec 25 '13 at 20:35
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.