Can adding both Asc and Desc indexes improve performance?

Question

Was wondering if adding an ascending and descending index (on the same column) could allow SQL to search any faster by utilizing both indexes at the same time?

Answer 1

The leaf pages of the index form a doubly linked list with pointers to the next and previous pages. This means indexes can be scanned both forward and backwards.

There is a slight difference in that currently only scans in the forward direction can be parallelised but it is extremely unlikely to justify the cost of maintaining both just for that edge case.

could it allow SQL to search any faster by utilizing both indexes at the same time?

No SQL Server won't split a single index access into a multiple index access in some sort of attempt to parallelise the work.

Answer 2

No, it won't help. Remember that each index is a B-Tree (balanced tree). SQL is going to start at the top and work it's way down regardless.

        M
     /      \
   E  H   K   X
  /\ /\   /\  /\
 A F G I J  L M Z

SQL Can easily work it's way down the tree in any direction it chooses. In fact having both indexes will have the negative effect of requiring both indexes to be maintained.

Answer 3

I don't think so. You pick one way or the other to reduce fragmentation.

If you compare query plans with one index sort both ways the cost is split 50 50.