I have following query which has
ON clause on different column,
Where clause on different column and
Selection on different columns so my question is what should be Order of Columns in Index as according to my knowledge order of columns matter.
SELECT AA.[Column7], AA.[Column2], AA.[Column3], ISNULL(AA.[Column23], 0) AS [Column23], BB.[Column2] + '. ' + BB.[Column3] + ', ' + BB.[Column4] AS UserName, AA.[Column1], AA.[Column4], AA.[Column5], AA.[Column6], AA.[Column8], ISNULL(AA.[Column9], 0) [Column9], ISNULL(AA.[Column10], 0) [Column10], ISNULL(AA.[Column11], 0) [Column11], ISNULL(AA.[Column12], 0) [Column12], AA.[Column13], AA.[Column14], AA.[Column15], ISNULL(AA.[Column19], '') [Column19], CC.[Column1] FROM Table1 AA INNER JOIN Table2 BB ON AA.[Column6] = BB.[Column1] LEFT JOIN Table3 CC ON AA.[Column22] = CC.[Column1] WHERE AA.[Column5] = @Vailable1 ORDER BY AA.[Column2] DESC, AA.[Column1] DESC
So far I have tried below strategy but I am not sure whether it’s according to best practice or not.
CREATE NONCLUSTERED INDEX [IX_IndexName] ON [dbo].[Table1] ( [Column5] ASC, [Column2] DESC, [Column1] DESC, [Column22] ASC ) INCLUDE ( [Column3], [Column4], [Column6], [Column7], [Column9], [Column10], [Column11], [Column12], [Column13], [Column14], [Column19], [Column23], [Column15], [Column8] )
EDIT: During research on order of columns in index I found a link where there is a statement
Very often you'll find that a better index is on (MostSelective) include (SecondMost, Least) or on (MostSelective, SecondMost) include (Least)
How do I judge what are MostSelective columns, SecondMost and Least? Is this depending on data?