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

Question: Does ASC or DESC in an index key matter for query performance?

Answer: Yes, particularly when a query needs a mixed ordering across several key columns. A suitable index can supply the required order without an extra Sort operator.

A customer asked this during a Comprehensive Database Performance Health Check. It is not a situation I run into every day, so I built a demonstration around an index on (Col1 DESC, Col2 ASC).

The original rows all contained Bob and Brown. This smaller private-table version uses varying values so the result order itself is worth inspecting. Enable actual execution plans in SSMS:

SET NOCOUNT ON;
IF OBJECT_ID('tempdb..#SortDirection') IS NOT NULL
    THROW 50001, 'The private demonstration table already exists.', 1;
CREATE TABLE #SortDirection (ID int, Col1 int, Col2 int);
INSERT #SortDirection
SELECT TOP (10000) ROW_NUMBER() OVER (ORDER BY a.object_id,b.object_id),
       a.object_id % 100, b.object_id % 100
FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b;
CREATE NONCLUSTERED INDEX IX_SortDirection
ON #SortDirection (Col1 DESC, Col2 ASC);
SET STATISTICS IO ON;
SELECT Col1,Col2 FROM #SortDirection
ORDER BY Col1 DESC,Col2 DESC OPTION(MAXDOP 1);
SELECT Col1,Col2 FROM #SortDirection
ORDER BY Col1 DESC,Col2 ASC OPTION(MAXDOP 1);
-- A backward scan can provide the complete reversed order.
SELECT Col1,Col2 FROM #SortDirection
ORDER BY Col1 ASC,Col2 DESC OPTION(MAXDOP 1);
SET STATISTICS IO OFF;
DROP TABLE #SortDirection;

The second ordering matches the index’s forward order. The third reverses every key direction, which a backward scan can provide. The first changes only one direction, so that index does not supply the complete requested ordering. Inspect the chosen plans because the optimizer can choose another route according to cost.

In the current 10,000-row test, DESC/DESC used a Table Scan followed by a Sort. The matching DESC/ASC ordering used an ordered forward Index Scan, and ASC/DESC used an ordered backward Index Scan. The properties below belong to that smaller test, not the original million-row example.

An extra Sort is real work, but it does not automatically mean a spill or a populated Worktable. Check the actual plan and IO rather than inferring either from the operator’s presence. Also, the index does not promise row order when the query has no ORDER BY. The original point remains useful: matching the required key sequence and directions can remove unnecessary sorting.

Reference: Index key order and backward scans.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.