Welcome to our blog post series on SQL Server! In this article, we will continue our discussion on redundant indexes and explore a special case where a redundant index can actually be useful for performance optimization.

Before we dive into the details, let’s quickly recap what we learned in the previous article. Redundant indexes are indexes that provide the same set of columns as another index. In most cases, it is considered best practice to drop redundant indexes to avoid unnecessary overhead and maintenance.

However, there are scenarios where even though an index is redundant, it should not be removed. In this article, we will explore one such scenario with a demo.

Let’s imagine a conversation between two individuals, Jon and Mike, who are eager to learn and explore SQL Server. Jon promises to explain with a demo where a redundant index is useful and should not be dropped.

Jon starts by creating two tables, SampleTable1 and SampleTable2. Both tables have the same columns, but SampleTable2 has a wider column of type CHAR(800). Jon then creates two indexes on each table, one on Col1 and Col2, and another on Col1, Col2, and Col3.

Next, Jon populates both tables with the same data. Now comes the interesting part – the performance comparison.

Jon runs a test where he selects a smaller set of data from both tables using the respective indexes. He compares the execution plans and observes that in the case of SampleTable1, using either of the indexes yields the same performance. This is because Col3 is an integer, and the width of the column does not significantly impact performance.

However, in the case of SampleTable2, where Col3 is a CHAR(800) datatype, the choice of index makes a huge difference in performance. Jon explains that in this scenario, the index on Col1 and Col2 is the most optimal index for queries that only require those columns. If the SELECT statement includes Col3, then the index on Col1, Col2, and Col3 becomes the optimal choice.

Jon emphasizes that even though the index on Col1, Col2, and Col3 includes all the columns, when SQL Server only needs Col1 and Col2, it finds the index on Col1 and Col2 more suitable for performance.

From this demo, we can conclude that redundant indexes can be useful in certain cases, especially when the data type of a column is much wider than other columns. It is important to properly validate the usage patterns of indexes and query workloads before considering dropping redundant indexes.

Jon and Mike summarize their findings – before taking any actions, it is crucial to test everything and understand the specific needs of your queries and data. They also mention that this is not the only case when redundant indexes can be useful, and there may be other scenarios as well.

Stay tuned for our next blog post, where we will explore Test 2 with a larger dataset and further validate our earlier findings. In the meantime, you can clean up your database by dropping the test tables.

Thank you for reading! We hope you found this article helpful in understanding redundant indexes in SQL Server. If you have any questions or feedback, please let us know in the comments below.