Blog Post

Find identical duplicate indexes - Revised

,

I realized yesterday I posted this quickly after testing only against SQL 2016 version. So I decided to also test against SQL 2014 and 2012 versions to make sure its compatible.  I made one tweak (removed the compression_delay field from the output) to make it possible. 

So please give it a try and let me know your results, perspectives and feedback!

As I had mentioned in my original post, I was looking to find identical duplicate non-clustered indexes i.e. indexes on same table with same columns in same order. The query below uses two variables to make it little bit more flexible.

If you have a need or just curious to find indexes having same columns but in any order, set value for the variable @disregard_column_order = 1 in the code.

The query excludes identical indexes where one is clustered and the other one is non-clustered index.  But you can again toggle that by setting value for variable @include_clustered_indexes = 1.


/*
whether to include identical indexes where one is clustered and 
the other one is non-clustered index
*/
DECLARE @include_clustered_indexes bit = 0
/*
whether to find duplicate indexes where although all columns are same, they may not be in same order
*/
DECLARE @disregard_column_order bit = 0
;WITH cte
AS
       (SELECT
                        o.schema_id,
                        o.type_desc,
                        o.object_id,
                        i.index_id,
                        i.name index_name,
                        index_columns =
                                                             COALESCE((STUFF((SELECT CAST(',' +
                                                             COL_NAME(object_id, column_id) AS varchar(max))
                               FROM sys.index_columns
                               WHERE
                          (
                                 object_id = i.object_id AND
                                         index_id = i.index_id
                          )
                               ORDER BY object_id, index_id,
                               CASE WHEN @disregard_column_order = 1
                                then column_id 
                                else key_ordinal end
                               FOR xml PATH ('')), 1, 1, '')), ''),
                        i.type_desc index_type,
                        i.is_unique,
                        i.data_space_id,
                        i.ignore_dup_key,
                        i.is_primary_key,
                        i.is_unique_constraint,
                       i.fill_factor,
                        i.is_padded,
                        i.is_disabled,
                        i.is_hypothetical,
                        i.allow_row_locks,
                        i.allow_page_locks,
                        i.has_filter,
                        i.filter_definition
       FROM sys.indexes i
       INNER JOIN sys.objects o ON o.object_id = i.object_id
       WHERE OBJECTPROPERTY(o.object_id, 'ismsshipped') = 0 AND index_id != 0
       AND i.index_id > CASE WHEN @include_clustered_indexes = 1 THEN 0 ELSE 1 END
)
SELECT
          SCHEMA_NAME(i1.schema_id) schema_name,
          i1.type_desc,
          OBJECT_NAME(i1.object_id) object_name,
          i1.index_name,
          i1.*
FROM cte i1
INNER JOIN (SELECT schema_id, type_desc, object_id, index_columns
            FROM cte
            GROUP BY schema_id, type_desc, object_id, index_columns
            HAVING COUNT(*) > 1) i2
                      ON i1.schema_id = i2.schema_id
           AND i1.type_desc = i2.type_desc
           AND i1.object_id = i2.object_id
           AND i1.index_columns = i2.index_columns
ORDER BY schema_name, i1.type_desc, object_name, i1.index_name

Now among other things, the query does not take into account the ASC or DESC clause of the index. So you may have two identical indexes but one is sorted ASC and the other one is DESC and you may have a very good reason for that.  Nor does this query consider if one or both indexes are filtered indexes. I would like to hear your feedbacks before putting more efforts to cover every other possible options.


Original post (opens in new tab)

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating