Does a database index work in reverse?

Given a database table example:

ID
Name
DateOfBirth

Say I wanted to create an index on date of birth, I might add the index on DateOfBirth ascending. If my code now queries on DOB ascending, it will run nice and fast.

However, are databases intelligent enough to use the index in reverse, i.e, if I queried orderby DateOfBirth descending would it still be able to utilise that index, or should I create a开发者_JAVA百科nother one specifically for descending ordering?

The index pages form a doubly linked list as shown below with pointers to both the next and previous page.

Thus SQL Server can traverse the index both backwards and forwards. The Scan direction is shown in the properties of the execution plan.

Where the Asc/Desc becomes important is in composite indexes.

If the index was defined as ID ASC, Name ASC, DateOfBirth ASC this would still require a sort for the query ORDER BY ID ASC, Name DESC, DateOfBirth ASC for example.

Note that you may have more composite indexes than you realise as well as a non unique non clustered index will have the clustered index key value added to its key.