Decreasing Sort Order Indexes

Question

The Database Engine Tuning Advisor finally abandoned the ghost and can no longer help me, so I need to learn a little more about indexes (shouldn't these indexes?).

I think I'm more or less there. I know when to use composite indexes, what to include in my indexes, the difference between clustered and nonclustered indexes, etc.

However ... I'm still scared. When creating an index for an index, there is a sort order. I understand what that means, but I'm struggling to come up with a scenario in which a reverse order index might be useful. My best guess is to speed up queries that retrieve rows that occur at the end of a direct sorted index, for example, the most chronologically last rows, but frankly, I'm more or less inconsequential.

Can someone enlighten me?

Answer 1

Index sort order is only relevant for an index with multiple columns. For a single column, the Sql Sever can simply use the index in the reverse order if it wants DESC, where is the ASC index.

For multi-column search, index sorting matters. Say you have an index:

field1, field2 desc 

This would be useful for this query:

 select field1, field2 from table order by field1, field2 desc 

And for this query, where the index can be used in reverse order:

 select field1, field2 from table order by field1 desc, field2 

But for this request, the Sql server will need an additional view in memory:

 select field1, field2 from table order by field1, field2 

Answer 2

Determining the correct sort order can potentially eliminate the need for a sort step in the query plan when you define an order in a select statement.

Msdn Article

Answer 3

Here's the BOL for indexing the sort order:

http://msdn.microsoft.com/en-us/library/ms181154.aspx

Help you understand the inner workings a bit more, as well as the syntax.

Answer 4

You are sorting an index that decreases when developing this index for a query that will be sorted in descending order.

Answer 5

Often there are biases for the most recent data, so if you sort by date in descending order, you optimize this use case.

I guess this will make more sense for things that are numeric rather than alphabetic.