sp_IndexCleanup: make a foreign key's backing index the deliberate winner of its dedupe pair, not the alphabetical one #917

Your second suggestion from the #913 review, written up so it is not lost in a PR thread. Happy to take it.

Where it stands after #913

#913 keeps both indexes of the pair when the loser backs a foreign key. The useful half of what you suggested already happens, though, and only by luck. Measured on 2022 CU27 with two mirror fixtures:

backing index is ux_z_code (sorts last)   -> KEPT - FOREIGN KEY on ux_z_code, no DISABLE, duplicate stays
backing index is ux_a_code2 (sorts first) -> DISABLE on ux_z_code2, duplicate cleaned up, no KEPT row

Same shape of table both times. The only difference is which of the two happens to sort first, because Rule 2 breaks a priority tie on index_name. So when the backing index wins, the duplicate goes today and nothing needs changing; when it loses, #913 stops the DISABLE and the duplicate stays.

What would finish it

1. Make the backing index win on purpose. A term in index_priority for "a foreign key is backed by this index" would settle the pair the right way round before any rule picks a loser, instead of leaving it to the alphabet. That also makes Never disable an index a foreign key is backed by (#902) #913's guard the belt rather than the braces: it would still catch anything a rule decides on a basis other than priority, which is most of them.
2. Merge includes into it where the loser covers more. If the duplicate carries includes the backing index does not, promoting the backing index has to absorb them or the coverage goes with the DISABLE, which is the failure shape your tests README calls the characteristic one.
3. Decide the case that has no answer. When both indexes of the pair back foreign keys there is no loser to pick at all. Keeping both, as Never disable an index a foreign key is backed by (#902) #913 does, is the only safe answer I can see, but it is worth saying so on purpose rather than by omission.

What I would want to measure first

Whether (1) alone is enough in practice, or whether a rule that does not read priority still picks the backing index as the loser. Rule 3 disables the narrower index of a subset pair and Rule 7.5 works on constraints, and neither consults index_priority the way Rule 2 does. If one of them can still do it, the priority term is a nicety and the guard is what actually protects the key, which is worth knowing before adding a magic number to the scoring.

Also whether a FK-backing index should outrank a clustered index's 1000, which it should not, so the term has to sit below that.

Happy to be told this is not worth the complexity. #913 already closes the data-integrity half, and this part is about getting the duplicate cleaned up as well.