Identify Full or Partial Duplicate Indexes
SQL Server 2000: Not Supported
SQL Server 2005: Tested
SQL Server 2008: Tested
SQL Server 2008R2: Tested
SQL Server 2012: Not Tested
Date: 4 Dec 2009/10 Jul 2012
This is some code I stumbled across that still needed a bit of polishing/testing. It was originally coded for SQL 2000 and someone had started conversion to SQL 2005 (with included columns) but didn't appear to have completed the job.
The basic premise of the code was sound, so I invested a little time and finished the conversion and testing.
There are 2 queries:
Identify identical indexes - where the keys and included columns are identical and in the same order
Identify near-identical indexes - where only the keys are considered (in order) and the matching is done from first to last key using a wildcard. A partial match would be where a two-key index has the same first two keys as a three-key index. The test data below should illustrate the results better than i can explain them.
No attempt is made to auto-generate any code to drop the duplicated indexes- in my opinion this should only be a manual task as all of the recommendations should be weighed carefully against business or application needs.
Identify Identical Indexes:
Identify Near-Identical Indexes:
Test Table Creation Code: