3

I have two non-clustered indexes on one of my dbs and their definition are:

CREATE NONCLUSTERED INDEX [NC_Index1] ON [dbo].[anyTable]
(
    [X] ASC,
    [Y] ASC,
    [Z] ASC
)
INCLUDE (A  ,
         B,
         C) 

and

CREATE NONCLUSTERED INDEX [NC_Index2] ON [dbo].[anyTable]
(
    [X] ASC,
    [Y] ASC,
    [Z] ASC,
    [colm1] ASC,
    [C] ASC
)
INCLUDE (A,
         colm2,
         colm3,
         colm4,
         colm5          
) 

I was just wondering if I could merge these two non-clustered indexes into single one by changing the definition of the second one by adding column B to include columns of the second one like:

CREATE NONCLUSTERED INDEX [NC_Index2] ON [dbo].[anyTable]
(
    [X] ASC,
    [Y] ASC,
    [Z] ASC,
    [colm1] ASC,
    [C] ASC
)
INCLUDE (A,
         B
         colm2,
         colm3,
         colm4,
         colm5          
) 

Also, would order of the included columns matter?

Thanks all.

  • 4
    Short answer: Yes you could! The 3rd index could be used instead of the first two. (With probably slightly less efficiency but still ok, depending on the size of the extra columns). The order of columns in the the INCLUDE does not matter. – ypercubeᵀᴹ Oct 31 '14 at 11:34
  • 1
    @Stackoverflowuser This script - from sqlskills.com will help you to find duplicate / redundant indexes. Analyze them and then see a possibility of trimming them down. – Kin Shah Oct 31 '14 at 13:39
4

Yes, as ypercube already said.

The only thing to look out is a wild discrepancy in the column actual data size. For instance if NC_Index1 is some 1Mb, and NC_Index2 is 200GB then you can introduce some potential scan perf problems. But, frankly, the chances of this being the case are, basically, 0. Or NULL, depending on your prefs.

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.