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.

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

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.

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

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)

, , , ,
Previous Post
How to Find Size of the Index for Tables? – Interview Question of the Week #198
Next Post
How to Trim TIME Part in DATETIME Values? – Interview Question of the Week #200

Related Posts

2 Comments. Leave new

  • what if order by clause is not there in query. but we are searching latest records frequently from the table. in this case, DESC sort order mentioned in INDEX will improve performance?

    Reply
  • If you have table that will generally be written to, indexes should have order clause that match order in which new rows will appear – it will prevent page splits.

    Reply

Leave a Reply

Menu