patternsqlMinor
Overlapping indexes
Viewed 0 times
overlappingindexesstackoverflow
Problem
I have two non-clustered indexes on one of my dbs and their definition are:
and
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:
Also, would order of the included columns matter?
Thanks all.
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.
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.