creating ascending and descending indexes

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

The CREATE INDEX command, supports the keywords ASC (specifies ascending) and DESC (specifies descending) on individual columns in indexes:

CREATE TABLE Table
   (ID      int PRIMARY KEY,
    Name      char(10),
    Weight   decimal(9,3)
    )
CREATE NONCLUSTERED INDEX DescIdx ON
        Table(Name ASC, Weight DESC)

The INDEXKEY_PROPERTY function reports whether an index column is stored in ascending or descending order. 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 Table can completely eliminate the need for a SORT operator in the query plan if Name ASC, Weight 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. As an 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.

Defining the ASC or DESC on index is useful when you have more than one column (multiple columns) in single index. If you have only one column indexed then SQL Server query speed will be same in both ORDER BY column ASC or ORDER BY column DESC.

 



 3/18/2008


Add Comments
Name: *
Email: *
URL:
Comments: *
 


Advertisements

MySQL HOSTING
MySQL hosting provider, clustering and replication supported. DBA Services Included.

SQL SERVER HOSTING
Reliable, powerful SQL Server 2008 hosting with ASP.NET on Windows 2008 Servers.

SQL Server DBA

I am a SQL Server DBA with almost 9 years of experience in database technologies.

 I am again in process of redesigning this website using ASP.NET 2.0 and AJAX. The main purpose of new SQL DBA website is to offer more features and make it easy for me to update contents on regular basis.