sp_IndexCleanup: keeps a strict unique index over one with IGNORE_DUP_KEY = ON, so duplicate inserts start to fail #916

Two unique indexes or unique constraints can have the same keys, with IGNORE_DUP_KEY = ON on only one of them. In that case, sp_IndexCleanup does not look at IGNORE_DUP_KEY when it picks the one to keep. Rules 2 and 7.5b pick by index_priority, then by name. Rule 7 keeps the nonclustered index and drops the constraint.

If the generated scripts remove the IGNORE_DUP_KEY structure, an INSERT of a duplicate key behaves differently afterwards. Before the scripts run, SQL Server skips the duplicate row with a warning. After the scripts run, the INSERT fails.

What SQL Server does when both are present

I measured this on SQL Server 2022. When a strict structure and an IGNORE_DUP_KEY structure share their keys, the IGNORE_DUP_KEY one decides the outcome. SQL Server skips the duplicate row, and the strict structure raises no error. I tested 16 combinations:

• a strict constraint with an IGNORE_DUP_KEY index, and the reverse
• two unique indexes, created in both orders
• a single-row insert, a 500-row insert, and 500-row and 50,000-row inserts with a forced wide plan (QUERYTRACEON 8790)

All 16 skipped the duplicates with no error. So if sp_IndexCleanup keeps the IGNORE_DUP_KEY structure, nothing changes. If it keeps the strict one, inserts of duplicate keys start to fail.

What sp_IndexCleanup does

Each test table has the two structures on one key column, k. I ran each procedure with @dedupe_only = 1.

I ran the t22, t23 and t25 scripts inside a transaction and rolled it back. Before the scripts, a duplicate insert was skipped. After them, it failed with the error in the table. In t23 to t26, both structures get the same score, so the names decide the result.

The results are the same on dev before #915, with #915, and with #914. Rule 5 (same keys, different includes) picks its winner the same way. I did not test Rule 5.

To reproduce the Rule 2 case:

CREATE TABLE dbo.t23 (id integer NOT NULL CONSTRAINT pk_t23 PRIMARY KEY, k integer NOT NULL, x integer NULL);
CREATE UNIQUE INDEX ux23_a ON dbo.t23 (k);
CREATE UNIQUE INDEX ux23_b ON dbo.t23 (k) WITH (IGNORE_DUP_KEY = ON);

sp_IndexCleanup returns ALTER INDEX [ux23_b] ON [...].[dbo].[t23] DISABLE; for this table.

The same decision as #917

In #913, stroexd suggested that the index that backs a foreign key win on purpose, not by alphabetical luck, "probably as a term in index_priority". stroexd wrote it up as #917. This issue needs the same lever. In both cases, the score does not see a property that decides which structure has to stay.

The two terms also interact. Take a strict unique index that backs a foreign key, and an IGNORE_DUP_KEY duplicate that backs nothing. If either one is removed, something changes: the foreign key is disabled, or duplicate inserts start to fail. One result removes the duplicate and changes nothing: keep the index that backs the key, and run ALTER INDEX ... SET (IGNORE_DUP_KEY = ON) on it.

I tested this on SQL Server 2022. The foreign key stayed enabled and trusted, a duplicate insert was skipped, and an orphan insert failed with error 547. We recommend one change for both terms, because the order of the two terms decides this case.

Two ways to fix it

1. Make the IGNORE_DUP_KEY structure win. Add a term to index_priority that outranks the usage and include terms. Then Rules 2, 5 and 7.5b keep the IGNORE_DUP_KEY structure. If Rule 7 drops a constraint that has IGNORE_DUP_KEY = ON, rebuild the replacement index with IGNORE_DUP_KEY = ON. sp_IndexCleanup: carry index options through rebuilds and skip no-op merges #914 already does this when the replacement index is not unique yet (MAKE UNIQUE). sp_IndexCleanup: carry index options through rebuilds and skip no-op merges #914 writes no script for a replacement index that is already unique. In that case, the rebuild adds no column and no uniqueness.
2. Do not pair two unique structures whose IGNORE_DUP_KEY settings differ. Keep both, and report nothing for the pair.

Option 1 keeps the cleanup and the insert behavior. Option 2 changes less code, but it leaves a redundant structure in place.