When do you define an index in decreasing order?

Question

Looking through several implementations of SQL, I noticed that most DBMSs support index definition in a column with a write-off order, for example

CREATE INDEX anIndex ON aTable (aColumn DESC);

When will it be beneficial for the ASC index? Why is ASC or DESC part of the index definition?

If the implementation of the index is effective (B-tree or even binary search in a sorted list), I do not see any significant difference between the ASC or DESC index.

What am I missing?

Answer 1

If the table is clustered, the index actually becomes the following:

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 indices, columns can be ordered in opposite directions:

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

Answer 2

• , . ORDER BY.

• , , .