Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Simplify SQL Server Database Development     ====>    Webcast Registration
 

Building SQL Server Indexes in Ascending vs Descending Order


By:   |   Read Comments (6)   |   Related Tips: More > Indexing

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.

query plan

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.

query plan

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.

query plan

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.

query plan

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] 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 the same as above, so there is no difference at all.

query plan

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.

query plan

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.

query plan

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 asc

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.

query plan

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 the improvements can be made if you create the index based on the way the data is accessed.


Last Update:

First Published: 2007-09-25


next webcast button


next tip button



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, August 02, 2018 - 2:45:43 PM - Greg Robidoux Back To Top

Hi Marc,

I did a quick test.  If the new dates are always increasing it looks like when the index is created in DESC order it causes more index fragmentation than when the index is created in ASC order.  

The test started with 4000 rows.  I then added 1000 new rows where the date kept getting more current.  The ASC fragmenation was 14% after the inserts where the DESC fragmentation was 33% after the inserts.

As you point out, there are always many factors that will influence our final design.

Thanks
Greg


Thursday, August 02, 2018 - 1:49:02 PM - MARC SCIRRI Back To Top

 Doesn't creating an index with a date column sorted in DESC instantly introduce fragmentation? And will searches lose any performance gains over a short period of time?


Wednesday, January 17, 2018 - 9:36:33 AM - pacho Back To Top

 Hi Greg,

Thank you for your response. Unfortunately, I forgot to mention that i am sorting by at least 2-3 columns. For example Order is sortered by Date, OrderId, Reference and for example by CreatedBy. The problem is that, that sort is always performed by AT LEAST 2 columns. my client needs sometimes to view orders in ASCENDING way and sometimes in DESCENDING way for DATE (datetime) column (also others, but i am focusing on that now). So if i put an index on that Date column ordered by ASC, i get performance issues, when i try to search for orders when they are sortered in DESCENDING way (the most fresh order to older orders...) AND i do not know how to deal with that issue.

 


Wednesday, January 17, 2018 - 9:25:45 AM - Greg Robidoux Back To Top

Hi Pacho,

If you are only sorting on that one column, it doesn't really matter if you create the index in ASC or DESC order.  Examples 3 and 4 show you get the same results.

If you are returning a large amount of records (10k), it will probably do a SCAN, but it will only scan the pages that are needed based on the index pages.

Thanks

Greg


Wednesday, January 17, 2018 - 5:10:05 AM - pacho Back To Top

Hey there,

What then if, for example, we have an Orders and they sometimes needs to be sorted ASC by Date and sometimes DESC by Date (getting the newest orders). How to deal with indexes in that case? Suppose we are returning around 10k orders 

 


Wednesday, April 15, 2015 - 4:18:26 PM - Artabandhu Satapathy Back To Top

Thanks to you Greg, Now I clearly understand the Use of Sort order in Index Columns.


Learn more about SQL Server tools