SQL Server indexes - ascending or descending, what difference does it make?

Question

When you create an index on a column or number of columns in MS SQL Server (I'm using version 2005), you can specify that the index on each column be either ascending or descending. I'm having a hard time understanding why this choice is even here. Using binary sort techniques, wouldn't a lookup be just as fast either way? What difference does it make which order I choose?

Answer 1

This primarily matters when used with composite indexes:

CREATE INDEX ix_index ON mytable (col1, col2 DESC);

can be used for either:

SELECT  *
FROM    mytable
ORDER BY
        col1, col2 DESC

or:

SELECT  *
FROM    mytable
ORDER BY
        col1 DESC, col2

, but not for:

SELECT  *
FROM    mytable
ORDER BY
        col1, col2

An index on a single column can be efficiently used for sorting in both ways.

See the article in my blog for details:

• Descending indexes

Update:

In fact, this can matter even for a single column index, though it's not so obvious.

Imagine an index on a column of a clustered table:

CREATE TABLE mytable (
       pk INT NOT NULL PRIMARY KEY,
       col1 INT NOT NULL
)
CREATE INDEX ix_mytable_col1 ON mytable (col1)

The index on col1 keeps ordered values of col1 along with the references to rows.

Since the table is clustered, the references to rows are actually the values of the pk. They are also ordered within each value of col1.

This means that that leaves of the index are actually ordered on (col1, pk), and this query:

SELECT  col1, pk
FROM    mytable
ORDER BY
        col1, pk

needs no sorting.

If we create the index as following:

CREATE INDEX ix_mytable_col1_desc ON mytable (col1 DESC)

, then the values of col1 will be sorted descending, but the values of pk within each value of col1 will be sorted ascending.

This means that the following query:

SELECT  col1, pk
FROM    mytable
ORDER BY
        col1, pk DESC

can be served by ix_mytable_col1_desc but not by ix_mytable_col1.

In other words, the columns that constitute a CLUSTERED INDEX on any table are always the trailing columns of any other index on that table.