Descending sort order indexes

Question

The Database Engine Tuning Advisor has finally given up the ghost and can't help me any more, so I'm having to learn a little bit more about indexes (shouldn't it be indices?).

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

However... One thing still confuses me. When creating an index, there is a sort order to the index. I understand what this means, but I am struggling to think of a scenario where a reverse order index might be useful. My best guess is to speed up queries that retrieve rows that occur at the end of the forward sorted index, such as the most chronologically recent rows, but frankly, I'm more or less clueless.

Can anyone enlighten me?

Answer 1

The sort order of an index matters only for a multi-column index. For a single column, Sql Sever can just use the index in reverse order, if it wants DESC where the index is ASC.

For a multi-column search, the index sorting does matter. Say you have an index on:

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:

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

But for this query, Sql Server would need an additional in-memory sort:

select field1, field2 from table order by field1, field2

Answer 2

Here's the BOL on indexing sort orders:

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

Should help you understand the inner workings a little bit more as well as syntax.

Answer 3

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

Msdn article