The difference between duplicate indexes and overlap indexes is one of the indexes in the comparison can have more keys
For this example there are 2 table
Table A
Index Name |
Type |
Index Keys |
|---|---|---|
PK_Table_A |
Clustered Index |
a,b |
ix_a_d |
Non Clustered Index |
a, d |
ix_abc |
Non Clustered Index |
a, b, c |
ix_bcd |
Non Clustered Index |
b, c, d |
Table B
Index Name |
Type |
Index Keys |
|---|---|---|
PK_Table_B |
Clustered Index |
x |
ix_x_y |
Non Clustered Index |
x,y |
ix_y_z |
Non Clustered Index |
y,z |
Confirming the indexes on the tables:
output
Index Name |
Type |
Index Keys |
Output |
|---|---|---|---|
PK_Table_A |
Clustered Index |
a,b |
Shown |
ix_a_d |
Non Clustered Index |
a, d |
not shown missing b column |
ix_abc |
Non Clustered Index |
a, b, c |
Shown -overlap with PK_Table_A |
ix_bcd |
Non Clustered Index |
b, c, d |
not shown missing a column |
Index Name |
Type |
Index Keys |
Output |
|---|---|---|---|
PK_Table_B |
Clustered Index |
x |
Shown |
ix_xy |
Non Clustered Index |
x,y |
shown -overlap with PK_Table_B |
ix_yz |
Non Clustered Index |
y,z |
not shown missing x column |