If I take the Users table from any Stack Overflow database, put an index on Reputation, and write a query to find the top 100 users sorted by reputation, descending:
CREATE INDEX Reputation ON dbo.Users (Reputation); SELECT TOP 100 * FROM dbo.Users ORDER BY Reputation DESC;
It doesn’t matter whether the index is sorted ascending or descending. SQL Server goes to the end of the index and starts scanning backwards:
If you right-click on the Index Scan and go into Properties, you can see that the data is ordered, and SQL Server is scanning backwards:
You don’t need a