SA0056 : Index has exact duplicate or overlapping index

Index has exact duplicate or overlapping index

The rule matches exact duplicating or partially duplicating indexes.

The exact duplicating indexes must have the same key columns in the same order, and the same included columns but in any order. These indexes are sure targets for elimination.

The overlapping indexes share the same leading key columns, but the included columns are ignored. These types of indexes are probable dead indexes walking.

Considerations:

– Before dropping the duplicating indexes,check for index hints referencing the particular duplicating index.

– Be careful when dropping a partial duplicate index if the two indexes differ greatly in width.

Consider setting appropriate value to the MaximumNonOverlappingKeyColumns in order the rule to filter only the indexes having small differences in the number of indexed columns.

For example:

If Index1 is a very wide index with 12 columns, and Index2 is a narrow two-column index that shares the first two columns,

you may want to leave Index2 as a faster, compact, narrower index.

The rule has a ContextOnly scope and is applied only on current server and database schema.

The rule requires Analysis Context. If context is missing, the rule will be skipped during analysis.

Design Rules, Bugs

How can you tell if an index is REALLY a duplicate?

Other Resources