Descending index scan optimization is a performance enhancement that can improve the performance of queries that use the desc keyword in order by queries to return result sets in descending order. In earlier releases of SQL Server, descending result sets required a worktable and a sort. In Adaptive Server 11.5, the optimizer can choose to use the index and avoid the sort step, if the strategy reduces the query cost.
Descending index scans can speed the use of both clustered and nonclustered index access, reduce the tempdb space required for temporary tables, save the CPU time required for sorts, and shorten the time that locks are held, if descending scans use holdlock or transaction isolation level 3. However, there can be an increased chance of deadlocking in some applications.
See Chapter 11, “Setting Configuration Parameters,” in the System Administration Guide for information on the allow backward scans configuration parameter, which can be used to disable this optimization if deadlocking is a problem. Descending scans are enabled by default.
This feature does not change the syntax for the order by clause; it only changes the way that order by clauses with the desc keyword can be optimized.
