patternMinor

When you select from a table on an indexed column and order by that column in the opposite direction of the index, does the index help

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

indexordertheyoucolumndirectionhelpindexedthatdoes

Problem

Let's say I have a large table of transactions with a date field and an index on that field in ascending order. If I select that field from the table and order by in reverse order is it able to somehow use that index in reverse, or is it going to have to do a table scan or some other less efficient method of accessing those records and sorting them?

Solution

If the index is covering it's likely it will be used in conjuction with a sort operation in the query plan to reverse the order.

Edit: Following a little more thought!

It will depend on whether this is a trivial query or involves joins. If trivial:

SELECT x,y FROM MyTable ORDER BY y DESC

and the index order is Y ASC, a reverse scan of the index leaf level should avoid a sort.

If non-trivial:

SELECT x,y FROM MyTable mt INNER JOIN MyOtherTable mot ON mot.y = mt.y

it should depend on the sort order of MyOtherTable.y. If it's ASC as per MyTable.y then the two indexes would be read in index order and a sort applied after the join. If it's desc, in theory a reverse order index scan could be used for the join and an additional sort wouldn't be required to satisfy your order by clause.

Edit2: Couldn't recall if this would show up in the execution plan in SQL Server. The icon doesn't indicate this is a reverse scan, nor does the tooltip on hover. Properties however shows 'Scan Direction - Backward' or checking the plan XML reveals

ScanDirection="BACKWARD"

Code Snippets

SELECT x,y FROM MyTable ORDER BY y DESC

SELECT x,y FROM MyTable mt INNER JOIN MyOtherTable mot ON mot.y = mt.y

<IndexScan Ordered="true" --->ScanDirection="BACKWARD"<--- ForcedIndex="false" NoExpandHint="false">
            <DefinedValues>
              <DefinedValue>
                <ColumnReference Database="[TestDb]" Schema="[dbo]" Table="[MyTable]" Column="OtherId" />
              </DefinedValue>
            </DefinedValues>
            <Object Database="[TestDb]" Schema="[dbo]" Table="[MyTable]" Index="[IX_MyTable_OtherId]" />
          </IndexScan>

Context

StackExchange Database Administrators Q#5027, answer score: 5

Revisions (0)

No revisions yet.