This blog is not meant to be a comprehensive explanation of indexes. It is meant to help you determine if there are duplicate indexes within your database. There appears to be some debate regarding what is a duplicate index. This article defines an “Exact Duplicate Index” where there are multiple non-clustered indexes with exactly the same keys and include columns.
This query will ignore clustered indexes.
It is possible to create a non-clustered index with keys that match a clustered index. Sometimes, this could be considered a duplicate index, and sometimes it should not be considered a duplicate. For example, suppose you have a wide table so that there is only one row per data page. Also suppose you have a query that only needs to use the key columns. It will be faster to use a non-clustered index for the query instead of the clustered index because the non-clustered index would only include the key columns, and you will get more columns per data page. The non-clustered index would require less file I/O, and therefore increased performance.
If you have a wide table with many rows, a non-clustered index that matches the key columns of the clustered index will improve performance.
This query will ignore partial duplicates with similar keys.
It is possible to create indexes with similar keys, and sometimes it is beneficial to do so. For example, suppose you had an index with key columns (Col1, Col2) and another with (Col1, Col2, Col3). You probably don’t need the first index because the second index will suffice. However, there are cases where Col3 could be wide and using it would cause extra I/O compared to the first index. It is probably rare that you would see any difference in performance between these indexes. I will be writing another article that explains this in depth and provides a query that will return these possible duplicates.
This query will ignore partial duplicates with the same keys and similar includes.
Column ordering for include columns does not matter, but which columns are included can make a big difference in the performance of a query. There could be similar indexes that have the same key columns but different include columns. This can affect the size of the index and cause more I/O. There will be another blog that explains this in depth and provides a query to identify duplicate indexes with similar includes.
The query to identify exact duplicates:
; With IndexColumns As ( select I.object_id, I.index_id, SubString( ( select ',' + Convert(VarChar(10), Column_id) from sys.index_columns as k where k.object_id = i.object_id and k.index_id = i.index_id and is_included_column = 0 Order By key_ordinal for xml path('') ), 2, 1000) As KeyColumns, SubString(Coalesce( ( select ',' + Convert(VarChar(10), Column_id) from sys.index_columns as k where k.object_id = i.object_id and k.index_id = i.index_id and is_included_column = 1 Order By column_id for xml path('') ), ''), 2, 1000) As IncludeColumns From sys.indexes As I Inner Join sys.Tables As T On I.object_id = T.object_id Where I.type_desc <> 'Clustered' And T.is_ms_shipped = 0 ) Select Object_Name(AIndex.object_id) As TableName, AIndex.name As Index1, bIndex.Name As Index2, 'Drop Index [' + bIndex.Name + '] On [' + Object_Name(AIndex.object_id) + ']' As DropCode From IndexColumns As A Inner Join IndexColumns As B On A.object_id = B.object_id And A.index_id < B.index_id And A.KeyColumns = B.KeyColumns And A.IncludeColumns = B.IncludeColumns Inner Join sys.indexes As AIndex On A.object_id = AIndex.object_id And A.index_id = AIndex.index_id Inner Join sys.indexes As BIndex On B.object_id = BIndex.object_id And B.index_id = BIndex.index_id
When you run this query, you will see two columns in the output showing you the duplicates. Since these are exact duplicates, you can drop one of them. Which one you drop is up to you.