When would you define an index with a descending order?

Question

Looking through a few SQL implementations, I noticed that most DBMSs support defining an index on a column with a descinding order, e.g.

CREATE INDEX anIndex ON aTable (aColumn DESC);

When would this be advantageous over an ASC index? Why is ASC or DESC part of the index definition?

If the implementation of an index is efficient (B-tree or even a binary search in a sorted list), I can't see any material difference between an ASC or DESC index.

What am I missing?

Answer 1

If the table is clustered, the index in fact becomes this:

acolumn DESC, id ASC

and can be used in queries like

SELECT  TOP 1 *
FROM    mytable
ORDER BY
        acolumn DESC, id ASC

or

SELECT  TOP 1 *
FROM    mytable
ORDER BY
        acolumn ASC, id DESC

, but not in

SELECT  TOP 1 *
FROM    mytable
ORDER BY
        acolumn DESC, id DESC

For composite indexes, the columns can be ordered in opposite directions as well:

CREATE INDEX anIndex ON aTable (aColumn DESC, bColumn ASC);

Answer 2

1. When you think that users need to see the data in reverse order. Sometimes an index can be used to optimize an ORDER BY.

2. When you're trying to play tricky games with physical storage to get rows with certain properties to clump together.