Given a database table example:


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.

enter image description here

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 Asc/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.