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
