mardi 3 mars 2015

Order of Columns in Index


I have following query which has ON clause on different column, Where clause on 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 matters.



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]
)




Aucun commentaire:

Enregistrer un commentaire