sqlsql-serverdatabaseindexingnon-clustered-index

Multiple Index Personalities: Borderline duplicate keys

I am running sp_BlitzIndex from Brent Ozar and get a number of these items.

Multiple Index Personalities: Borderline duplicate keys

I'm not 100% on what to do but here is an example below.

CREATE INDEX [IX_Test] ON [Test] ( [SportId] ) WITH (FILLFACTOR=100, ONLINE=?, SORT_IN_TEMPDB=?, DATA_COMPRESSION=?);

CREATE UNIQUE INDEX [IX_Test_2] ON [Test] ( [SportId], [AnotherId] ) WITH (FILLFACTOR=100, ONLINE=?, SORT_IN_TEMPDB=?, DATA_COMPRESSION=?);

As you can see they seem similar. My question is does it hurt to remove the first index SportID but keep the dual index (SportId, TextId).

What is my best approach here?

Solution

  • The first index, IX_Test, is redundant since its first, most selective column, is replicated by index IX_Test_2.

    Both these indexes can satisfy a seek to specific rows by key or range for SportId, the second index also includes an additional column so is covering for queries that require both or to order by AnotherId.

    In the absence of the first index, the optimizer can utilize the second index equally well and the addition of a single int column, while making the index slightly wider, will be negligible and more than offset by the reduced overhead of having to maintain both.