Allpages-locked tables with clustered indexes

For allpages-locked tables with clustered indexes, order by queries that match the index keys are efficient if:

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