Given a database table example:
ID Name DateOfBirth
Say I needed to produce a catalog on birth date, I would add the index on
DateOfBirth ascending. If my code now queries on DOB climbing, it'll run nice fast.
However, are databases intelligent enough to make use of the index backwards, i.e, basically queried
orderby DateOfBirth descending wouldn't it still have the ability to utilise that index, or must i create a different one particularly for climbing down ordering?
The index pages form a doubly linked list as proven below with pointers to both next and former page.
Thus SQL Server can traverse the index both forward and backward. The Scan direction is shown in the properties of the execution plan.
In which the
Desc becomes important is within composite indexes.
When the index was understood to be
ID ASC, Name ASC, DateOfBirth ASC this could still need a sort for that query
ORDER BY ID ASC, Name DESC, DateOfBirth ASC for instance.
Note you will probably have more composite indexes than you understand in addition to a non unique non clustered index will have the clustered index key value added to its key.