HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Overlapping indexes

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
overlappingindexesstackoverflow

Problem

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.

Solution

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.

Context

StackExchange Database Administrators Q#81577, answer score: 4

Revisions (0)

No revisions yet.