Is an index on A, B redundant if there is an index on A, B, C?

Question

Having years of experience as a DBA, I do believe I know the answer to the question, but I figured it never hurts to check my bases.

Using SQL Server, assuming I have a table which has an index on column A and column B, and a second index on columns A, B, and C, would it be safe to drop the first index, as the second index basically would satisfy queries that would benefit from the first index?

Answer 1

It depends, but the answer is often 'Yes, you could drop the index on (A,B)'.

The counter-case (where you would not drop the index on (A,B)) is when the index on (A,B) is a unique index that is enforcing a constraint; then you do not want to drop the index on (A,B). The index on (A,B,C) could also be unique, but the uniqueness is redundant because the (A,B) combination is unique because of the other index.

But in the absence of such unusual cases (for example, if both (A,B) and (A,B,C) allow duplicate entries), then the (A,B) index is logically redundant. However, if the column C is 'wide' (a CHAR(100) column perhaps), whereas A and B are small (say INTEGER), then the (A,B) index is more efficient than the (A,B,C) index because you can get more information read per page of the (A,B) index. So, even though (A,B) is redundant, it may be worth keeping. You also need to consider the volatility of the table; if the table seldom changes, the extra indexes don't matter much; if the table changes a lot, extra indexes slow up modifications to the table. Whether that's significant is difficult to guess; you probably need to do the performance measurements.