The short answer is that if your query orders columns by a mix of ascending and descending order, back to back, then the index usually needs to match that same alternating order.

Now, for the long answer. When you create indexes, you can either create them in ascending order – which is the default:

Or descending order:

If your query orders the data in descending order, but your index is in ascending order, like this:

SQL Server can still use the index. I’ll demonstrate using the Stack Overflow database:

SQL Server can scan the index backwards, from the highest reputation to the lowest reputation value. You can see proof by right-clicking on the Index Scan and clicking Properties:

There’s a wee bit of a gotcha: backwards range scans can’t go parallel, as Paul White mentions in this post, but that’s rarely a dealbreaker.

In simple scenarios like this, you don’t need to screw around with DESCending order on your indexes.

Things change when you order by multiple columns in alternating orders.

Let’s say you want to build a leaderboard of the top Stack Overflow users. You want them in Reputation order descending, highest reputation first, and you want a tiebreaker column. If two people have the same Reputation score, you want to sort them by DisplayName. You’ve created an index to help:

Now things are a little different, as you can see in the actual execution plan:

SQL Server flat out ignored the index, read all 9 million rows in the table, and sorted all of them. The stats IO & time is horrifying, too:

For those of you who are horrified by this plan and don’t believe me, you’re welcome to reproduce it at home. I’m using a large Stack Overflow database, SQL Server 2019, 2019 compat mode.

SQL Server *COULD* use the index for this, mind you, as we can see with a hint, but the actual execution plan becomes even more terrifyingly insane:

And the statistics are bonkers:

You and I would design a better plan.

The execution plan you and I *WISH* we would get out of SQL Server is:

1. Scan the index from highest reputation to lowest, reading the first 100 rows and then immediately stopping. All 100 of the rows will match – remember, our query doesn’t even have a WHERE clause. Just read the first 100 rows, and call it a day.
2. Sort those 100 rows by our tiebreaker column, DisplayName.
3. Do 100 key lookups to satisfy the SELECT *.

However, it’s possible that the top 200 rows (not 100) all have exactly the same Reputation score. To handle that edge case scenario, we’d have to modify our execution plan a little:

1. Scan the index from highest reputation to lowest, reading the first 100 rows
2. Check the 101st row, and see if its Reputation matches the 100th row. If it does, go read another row from the index. Repeat that process until there are no more ties.
3. Sort the 100-110 (or 150 or whatever) rows by our tiebreaker column, DisplayName.
4. Do 100 key lookups to satisfy the SELECT *.

That would work, but…SQL Server just doesn’t build queries that way. The operator that sorts the data is a different operator than the one that’s reading the data:

The sort happens long after the data is read. And there’s another problem: key lookups are usually bound directly to their index operations. I think I can count on one hand the number of times in my life when I’ve seen SQL Server do an index operation (like a seek or scan), then another operation (like a sort), and then go back to the same table to do a key lookup. (Usually it involves an indexed view – more on that in another post.)

So if SQL Server uses the index’s sort of Reputation, then it insists on doing these stupid key lookups at the same time, reading more 8KB pages than there are in the table.

The fix: use DESC in the index to remove the sort.

If we build the sort into the index itself, then we don’t have to worry about tiebreakers on the TOP 100 ORDER BY Reputation DESC, DisplayName sort. Let’s try an index with explicit ordering built in:

This actual execution plan is way more elegant:

The query uses our new DESC index and runs way faster with zero CPU:

The takeaway: if your query orders columns by a mix of ascending and descending order, back to back, then the index usually needs to match that same alternating order. I don’t see this problem too often, and I can see why the missing index recommendations don’t fire off here. Querying by alternating sort orders on a single table is fairly unusual.

Alternating sort orders on multiple tables is a different story altogether. I describe that problem in the Indexing for Joins module in Fundamentals of Index Tuning class.