1

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?

  • MostSelective in this context means the one having most distinct values. Single-column unique keys are by definition always the most selective columns, while "Yes/No" type columns are least selective. – mustaccio Mar 3 '15 at 15:00
  • But what if I have Three columns (column1, column2 and column3). One is being used by ON clause, second is by WHERE and third is by ORDER BY all (3 columns) are MostSelective with same number of distinct values; there which column should be at first, 2nd and third place as candidate of index keys? – AA.SC Mar 6 '15 at 6:21
1

Columns specified in WHERE and JOIN clause predicates are the candidates for index keys. ORDER BY columns are also candidates if it can avoid an expensive SORT operator for many rows. Columns specified only in the SELECT clause are candidates for non-clustered index INCLUDED columns when the cost of the additional storage and maintenance outweigh the elimination of the key lookup in the plan. This may be the case for critical queries that is executed frequently.

Selectivity depends entirely on the data (i.e. high COUNT(DISTINCT column) compared to number of rows in table). The generality about most selective columns first is true when all of the keys are used seek predicates in the same index seek operator. You might find separate indexes more useful than a single composite index.

Generally speaking, indexing is most optimal when only those rows actually needed by the query are touched and expensive operators (e.g. large SORTs) are avoided. Tradeoffs are often involved.

  • if WHERE and JOIN clauses predicate are the candidates for index keys then why plan is showing Index seek to me though [Column6] in my index isn't part of index keys. – AA.SC Mar 3 '15 at 13:31
  • If you see a seek on column6, it looks like you have other indexes on the table besides the one in your question. – Dan Guzman Mar 4 '15 at 3:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.