9

I was reading through the comments of this answer on another question, and it got me wondering if the ordering of columns in the INCLUDE clause of an index (on SQL Server specifically but also any database engine) truly matters.

I haven't been able to find a definitive answer to this question searching online. I know that the order of columns in the main definition of the index matters a lot, but can't find any details about the INCLUDE portion of an index.

Does the order of columns listed in the INCLUDE clause of an index matter for index performance?

0

1 Answer 1

11

For index key columns the column ordering is significant.

For included columns on the whole it doesn't matter. The order that the non key columns are stored on the index leaf pages won't affect the seek or ordering characteristics of the index.

For variable length columns there is a fairly mild argument for putting NOT NULL columns or columns very unlikely to be null/empty first though.

As shown by

CREATE TABLE #T
(
ID INT IDENTITY PRIMARY KEY,
A VARCHAR(1) NULL,
B VARCHAR(1) NULL,
C VARCHAR(1) NULL,
D VARCHAR(1) NULL,
E VARCHAR(1) NULL,
F VARCHAR(1) NULL,
G VARCHAR(1) NULL,
H VARCHAR(1) NOT NULL DEFAULT 'H'
)

CREATE INDEX IX1 ON #T(ID) INCLUDE (A,B,C,D,E,F,G,H)

CREATE INDEX IX2 ON #T(ID) INCLUDE (H,A,B,C,D,E,F,G)

INSERT #T DEFAULT VALUES

SELECT max_record_size_in_bytes, index_id
FROM sys.dm_db_index_physical_stats
(2, object_id('tempdb..#T'), null, null, 'detailed')
WHERE index_type_desc = 'NONCLUSTERED INDEX'

which returns

max_record_size_in_bytes index_id
28 2
14 3

This is as space is saved for 2 bytes per column for 7 columns with the second arrangement and this contrived example for the same reasons as explained here.

Columns declared as INCLUDE columns can also be silently added to the key anyway in the case that the index is a non unique non clustered index and they are in the clustered index key but my testing indicates that the order they are listed as INCLUDE columns makes no difference to this and it just inherits the ordering from the clustered index when these columns are implicitly added to the key level.

CREATE TABLE #T2
(

A CHAR(1) NOT NULL,
B CHAR(1) NOT NULL,
C CHAR(1) NOT NULL,
D CHAR(1) NOT NULL,
PRIMARY KEY (A ASC, B DESC, C ASC, D DESC)
)

CREATE INDEX IX1 ON #T2 (A DESC) INCLUDE (D,C,B)

--Execution plan uses forward ordered scan of IX1 with no sort
SELECT *
FROM #T2
ORDER BY A DESC, B DESC, C ASC, D DESC

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.