Does Sort Order in Index Column Matters for Performance? – Interview Question of the Week #199

Question: Does Sort Order in Index Column Matters for Performance?

Answer: Wow, it has been a long time since I have heard this question and the matter of fact, I have never blogged about this subject on this blog. I have received this question during my recent Comprehensive Database Performance Health Check.

If you have ever seen any index creation syntax, you may notice that there is sort order in index column. Many often wonder if it really matters for the performance of query or not. Let me give you one-word answer – yes it does matter in some specific scenarios and let us see the example.

Let us first create a dataset.

CREATE TABLE MyTable (ID INT, Col1 CHAR(100),
Col2 CHAR(100))
GO
-- Insert into table
INSERT INTO MyTable (ID, Col1, Col2)
SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY a.name) RowID,
'Bob','Brown'
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO

Next, we will create a nonclustered index with two different columns and specify a different sort order for the columns.

CREATE NONCLUSTERED INDEX IX_MyTable
ON MyTable (Col1 DESC, Col2 ASC)
GO

After creating the index we can run following two queries with enabling execution plan and with SET STATISTICS IO ON.

SET STATISTICS IO ON
GO
-- Query 1
SELECT Col1, Col2
FROM MyTable
ORDER BY Col1 DESC, Col2 DESC
GO
-- Query 2
SELECT Col1, Col2
FROM MyTable
ORDER BY Col1 DESC, Col2 ASC
GO

When we see the execution plan, we can clearly see that the query where the ORDER BY columns and order of the sorting matches with the index created, gives us the best possible performance as there is no additional sort operator in the execution plan.

If you check the messages for the statistics IO, you can also see that in the first case where there is an additional sort operator there is Worktable which is essentially a Temporary Table.

Now you can run the following command to clean up the table which we have created earlier.

DROP TABLE MyTable
GO

Personally, when I go for consulting, I usually do not see such examples often. However, as this question was asked, I have created a demonstration to explain the concept.

Reference: Pinal Dave (https://blog.sqlauthority.com)