For allpages-locked tables with clustered indexes, order by queries that match the index keys are efficient if:
There is also a search argument that uses the index, the index key positions the search on the data page for first qualifying row.
The scan follows the next-page pointers until all qualifying rows have been found.
No sort is needed.
In Figure 22-5, the index was created in ascending order, and the order by clause does not specify the order, so ascending is used by default.
Figure 22-5: An order by query using a clustered index, allpages locking
Queries requiring descending sort order (for example, order by title_id desc) can avoid sorting by scanning pages in reverse order. If the entire table is needed for a query without a where clause, Adaptive Server follows the index pointers to the last page, and then scans backward using the previous page pointers. If the where clause includes an index key, the index is used to position the search, and then the pages are scanned backward, as shown in Figure 22-6.
Figure 22-6: An order by desc query using a clustered index
