Topic last updated -- July 2003

When defining indexes, you can specify whether the data for each column is stored in ascending or descending order. If neither direction is specified, ascending is the default, which maintains compatibility with earlier versions of Microsoft® SQL Server™.

The syntax of the CREATE TABLE, CREATE INDEX, and ALTER TABLE statements supports the keywords ASC (specifies ascending) and DESC (specifies descending) on individual columns in indexes:

CREATE TABLE ObjTable
   (ObjID      int PRIMARY KEY,
    ObjName      char(10),
    ObjWeight   decimal(9,3)
    )
CREATE NONCLUSTERED INDEX DescIdx ON
         ObjTable(ObjName ASC, ObjWeight DESC)

The INDEXKEY_PROPERTY meta data function reports whether an index column is stored in ascending or descending order. In addition, the sp_helpindex and sp_helpconstraint system stored procedures report the direction of index key columns. The descending indexed column will be listed in the result set with a minus sign (-) following its name. The default, an ascending indexed column, will be listed by its name alone.

The ability to specify the order in which key values are stored in an index is most useful in cases where most queries referencing the table have ORDER BY clauses that specify different directions for the key columns. For example, the index defined previously on the ObjTable can completely eliminate the need for a SORT operator in the query plan if ObjName ASC, ObjWeight DESC are specified in the ORDER BY clause of the query.

The internal algorithms of SQL Server can navigate equally efficiently in both directions on a single-column index, regardless of the sequence in which the keys are stored. For example, specifying DESC on a single-column index does not make queries with an ORDER BY IndexKeyCol DESC clause run faster than if ASC was specified for the index.