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

(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.

Find identical duplicate indexes - Revised

Rate

Share

Share

Rate