Indexes and Column Sorting

When you’re working on an index for a query ordering by one column in ascending order and another column in descending order, do you have your index created to match? Did you know you can specify ASC or DESC in an index? Let’s go through a scenario where ordering in an index makes a noticeable difference.

Order Up

We’ll use the StackOverflow2013 database for today’s example.

Let’s press CTRL-M to to enable “Include Actual Query Plan” and turn on statistics with:

SET STATISTICS IO, TIME ON;

Let’s run the SELECT statement below:

SELECT TOP 100 DisplayName, CreationDate
FROM Users
ORDER BY DisplayName, CreationDate DESC;
GO

We have the execution plan used for our query as well as statistics taken from the Messages tab and pasted into https://statisticsparser.com/ to read more easily.

We see that we scanned the PK_Users_Id clustered index to gather up our results. Before we got to our results, SQL Server also had to sort our data based on our ORDER BY. Let’s see what we can do to avoid the Sort in our plan.

Index #1

We can improve our plan this by creating an index with DisplayName and CreationDate:

CREATE INDEX IX_DisplayName_CreationDate
ON Users (DisplayName, CreationDate);
GO

We’re still needing to sort our data. Why? Because our query ORDER BY doesn’t match the index we just created.

Index #2

Our query is sorting DisplayName ascending and CreationDate descending. Our index is sorting DisplayName ascending and CreationDate ascending. If we made a slight change to the index and create it specifying CreationDate DESC (and ASC on DisplayName so see it):

CREATE INDEX IX_DisplayName_CreationDate_Desc
ON Users (DisplayName ASC, CreationDate DESC);
GO

And run our original query one more time:

No more Sort! We went from 44,000 logical reads to 3 logical reads. We went from a few seconds to a few thousandths of a second. That’s an exciting progress.

The numbers don’t lie. The first index led to an improvement but the second index with matching ASC/DESC specifications made a far greater improvement. Keep this in mind if you are in a situation where you have to tune a particular query with particular ordering.

Thanks for reading!