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