Problem
When building indexes, often the default options are used to create an index which creates the index in ascending order. This is usually the most logical way of creating an index, so the newest data or smallest value is at the top and the oldest or biggest value is at the end. Although searching an index works great by creating an index this way, but have you ever thought about the need to always return the most recent data first and ways you can create an index in descending order, so the most recent data is always at the top of the index?
Let’s take a look at how this works and the advantages of creating an index in descending vs. ascending order.
Solution
When creating an index, you have the option to specify whether the index is created in ascending or descending order. This can be done simply by using the key word ASC or DESC when creating the index as follows. The following examples all use the AdventureWorks sample database.
Here is sample code that shows how to create indexes in descending or ascending order.
Create index in descending order:
CREATE NONCLUSTERED INDEX [IX_PurchaseOrderHeader_OrderDate]
ON [Purchasing].[PurchaseOrderHeader] ( [OrderDate] DESC )
Create index in ascending order:
CREATE NONCLUSTERED INDEX [IX_PurchaseOrderHeader_OrderDate]
ON [Purchasing].[PurchaseOrderHeader] ( [OrderDate] ASC )
Let’s take a look at a couple of queries and query plans to see how this differs and if there is any advantage.
Example 1
In this example we are using the PurchaseOrderHeader table to select the top 10 records and just the OrderDate column from the table sorted by OrderDate in ascending order. There is no index on the OrderDate column.
SELECT TOP 10 OrderDate FROM Purchasing.PurchaseOrderHeader ORDER BY OrderDate
This query does a Clustered Index Scan and has a cost of 0.124344.
Example 2
In this example we are using the PurchaseOrderHeader table to select the top 10 records and just the OrderDate column from the table sorted by OrderDate in descending order. There is no index on the OrderDate column.
SELECT TOP 10 OrderDate FROM Purchasing.PurchaseOrderHeader ORDER BY OrderDate desc
This query does a Clustered Index Scan and has a cost of 0.124344 which is the same as above.
Example 3
In this example we create an index on the OrderDate in ascending order.
CREATE NONCLUSTERED INDEX [IX_PurchaseOrderHeader_OrderDate]
ON [Purchasing].[PurchaseOrderHeader] ( [OrderDate] asc )
We are using the PurchaseOrderHeader table to select the top 10 records and just the OrderDate column from the table sorted by OrderDate in ascending order.
SELECT TOP 10 OrderDate FROM Purchasing.PurchaseOrderHeader ORDER BY OrderDate asc
This query does an Index Scan on the new index and has a cost of 0.0033056 which is much better than our previous value of 0.124344.
So from this we can see that adding the index does make this query much faster.
Example 4
In this example we will use the new index again on OrderDate in ascending order.
We are using the PurchaseOrderHeader table to select the top 10 records and just the OrderDate column from the table sorted by OrderDate in descending order.
SELECT TOP 10 OrderDate FROM Purchasing.PurchaseOrderHeader ORDER BY OrderDate desc
This query does an Index Scan on the new index and has a cost of 0.0033056 which is the same as Example 3.
Take note that though the index was created in ascending order, getting the data in descending order is just as fast as getting the data in ascending order.
Example 5
Even though we already showed that selecting the data in descending order against an ascending index does not make a difference, let’s just do a check to make sure.
In this example we created an index on the OrderDate in descending order. We will drop the index and recreate it.
DROP INDEX [IX_PurchaseOrderHeader_OrderDate] ON [Purchasing].[PurchaseOrderHeader]
GO
CREATE NONCLUSTERED INDEX [IX_PurchaseOrderHeader_OrderDate]
ON [Purchasing].[PurchaseOrderHeader] ( [OrderDate] desc )
We are using the PurchaseOrderHeader table to select the top 10 records and just the OrderDate column from the table sorted by OrderDate in ascending order.
SELECT TOP 10 OrderDate FROM Purchasing.PurchaseOrderHeader ORDER BY OrderDate asc
This query does an Index Scan on the new index and has a cost of 0.0033056 which is the same as above, so there is no difference at all.
Another thing to look at is having the need to sort some of the columns in ascending order and other columns in descending order.
Example 6
In this example we create an index on the OrderDate in ascending order and SubTotal in ascending order.
DROP INDEX [IX_PurchaseOrderHeader_OrderDate] ON [Purchasing].[PurchaseOrderHeader]
GO
CREATE NONCLUSTERED INDEX [IX_PurchaseOrderHeader_OrderDate]
ON [Purchasing].[PurchaseOrderHeader] ( [OrderDate] ASC, [SubTotal] ASC )
We are using the PurchaseOrderHeader table to select the top 10 records and the OrderDate and SubTotal column from the table sorted by OrderDate in ascending order and the SubTotal in ascending order.
SELECT TOP 10 OrderDate, SubTotal FROM Purchasing.PurchaseOrderHeader ORDER BY OrderDate asc, SubTotal asc
This query does an Index Scan and has a cost of 0.0033123.
Example 7
In this example we will use the index we created on the OrderDate in ascending order and SubTotal in ascending order.
We are using the PurchaseOrderHeader table to select the top 10 records and the OrderDate and SubTotal column from the table sorted by OrderDate in ascending order and the SubTotal in descending order.
SELECT TOP 10 OrderDate, SubTotal FROM Purchasing.PurchaseOrderHeader ORDER BY OrderDate asc, SubTotal desc
This query does an Index Scan and has a cost of 0.102122. Having this index this way does not help much, since 84% of the work is done in the Sort operation.
Example 8
In this example we created an index on the OrderDate in ascending order and SubTotal in descending order.
DROP INDEX [IX_PurchaseOrderHeader_OrderDate] ON [Purchasing].[PurchaseOrderHeader]
GO
CREATE NONCLUSTERED INDEX [IX_PurchaseOrderHeader_OrderDate]
ON [Purchasing].[PurchaseOrderHeader] ( [OrderDate] ASC, [SubTotal] DESC )
We are using the PurchaseOrderHeader table to select the top 10 records and the OrderDate and SubTotal column from the table sorted by OrderDate in ascending order and the SubTotal in descending order.
SELECT TOP 10 OrderDate, SubTotal FROM Purchasing.PurchaseOrderHeader ORDER BY OrderDate asc, SubTotal desc
This query does an Index Scan and has a cost of 0.0033123. Having this index created this way helps out in a big way. The Sort operation has now disappeared.
Summary
As we have shown creating an index in ascending or descending order does not make a big difference when there is only one column, but when there is a need to sort data in two different directions one column in ascending order and the other column in descending order the way the index is created does make a big difference.
This was last tested with SQL Server 2017.
Next Steps
- Next time you create indexes keep the above in mind. Don’t worry so much about creating your single column index in either ascending or descending order.
- If you have the need to create a multi-column index keep in mind that improvements can be made if you create the index based on the way the data is accessed.
