patternsqlMinor
Can adding both Asc and Desc indexes improve performance?
Viewed 0 times
canaddingbothascindexesimproveperformancedescand
Problem
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?
Solution
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.
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.
Context
StackExchange Database Administrators Q#147237, answer score: 7
Revisions (0)
No revisions yet.