CREATE INDEX ….. [ ASC | DESC ]


I came across a good post the other day that highlighted something I’d not seen or used in the CREATE INDEX statement. Columns can be specified to be sorted either ASC or DESC. From BOL:

[ ASC | DESC ]

Determines the ascending or descending sort direction for the particular index column. The default is ASC.

This is nowhere near as exciting as the INCLUDE statement I blogged about on 9th September, but for someone out there, it may make their application that little bit better! In case the post one day disappears, here is a summary:

“While SQL Server can traverse an index in either direction (since it is a doubly linked list,) sometimes it can be valueable to sort the keys of an index to match the sort order of some desired output.  For example:

USE AdventureWorks; GO

SELECT maritalStatus, hiredate
FROM   HumanResources.Employee
ORDER BY maritalStatus ASC, hireDate DESC

The plan for this query is:

  |–Sort(ORDER BY:([AdventureWorks].[HumanResources].[Employee].[MaritalStatus] ASC,
                  [AdventureWorks].[HumanResources].[Employee].[HireDate] DESC))
       |–Clustered Index Scan(OBJECT:([AdventureWorks].[HumanResources].[Employee].
[PK_Employee_EmployeeID]))

Next, create a typical index with the default (ascending) sort order:

CREATE INDEX employee_maritalStatus_hireDate ON HumanResources.Employee(maritalStatus,hiredate)

The plan changes to an Index Scan (since it can use the index to cover the query,) and still a sort.

    |–Sort(ORDER BY:([AdventureWorks].[HumanResources].[Employee].[MaritalStatus] ASC,
                    [AdventureWorks].[HumanResources].[Employee].[HireDate] DESC))
       |–Index Scan(OBJECT:([AdventureWorks].[HumanResources].[Employee].
[employee_maritalStatus_hireDate]))

Better, but not perfect.  Change that to an index sorted in the direction that the output is desired in:

DROP INDEX employee_maritalStatus_hireDate ON HumanResources.Employee
GO
CREATE INDEX employee_maritalStatus_hireDate ON HumanResources.Employee(maritalStatus ASC,hiredate DESC)

Now the Sort is gone:

|–Index Scan(OBJECT:([AdventureWorks].[HumanResources].[Employee].[employee_maritalStatus_hireDate]), ORDERED FORWARD)

Of course, to be sure this has limited applicability, and is not necessarily the best thing to do even if it improves a query or two, since this is now an index that will need to be maintained, which may end up being more costly than paying for the clustered index scan.”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s