SQL Server Performance: In Search of.... Duplicate indexes

2 new queries... the first works with SQL 2017 & later, the second for older versions.

Query for newer releases:

WITH IncludedColumns AS (

SELECT i.object_id AS TableID,

I.index_id AS IndexID,

STRING_AGG(c.name, ', ') WITHIN GROUP (ORDER BY c.name) AS Includes

FROM sys.index_columns ic

JOIN sys.indexes i

ON ic.object_id = i.object_id

AND ic.index_id = i.index_id

JOIN sys.columns c

ON c.object_id = ic.object_id

AND c.column_id = ic.column_id

WHERE ic.is_included_column = 1

GROUP BY i.object_id, i.index_id

MyDuplicate AS (

SELECT

Sch.[name] AS SchemaName,

Obj.[name] AS TableName,

Idx.[name] AS IndexName,

INDEX_COL(Sch.[name] + '.' + Obj.[name], Idx.index_id, 1) AS Col1,

INDEX_COL(Sch.[name] + '.' + Obj.[name], Idx.index_id, 2) AS Col2,

INDEX_COL(Sch.[name] + '.' + Obj.[name], Idx.index_id, 3) AS Col3,

INDEX_COL(Sch.[name] + '.' + Obj.[name], Idx.index_id, 4) AS Col4,

INDEX_COL(Sch.[name] + '.' + Obj.[name], Idx.index_id, 5) AS Col5,

INDEX_COL(Sch.[name] + '.' + Obj.[name], Idx.index_id, 6) AS Col6,

INDEX_COL(Sch.[name] + '.' + Obj.[name], Idx.index_id, 7) AS Col7,

INDEX_COL(Sch.[name] + '.' + Obj.[name], Idx.index_id, 8) AS Col8,

INDEX_COL(Sch.[name] + '.' + Obj.[name], Idx.index_id, 9) AS Col9,

Idx.filter_definition AS Filter_definition,

ic.Includes,

Idx.is_primary_key,

Idx.is_unique,

Idx.is_unique_constraint

-- Add more columns as needed

FROM

sys.indexes Idx

INNER JOIN

sys.objects Obj ON Idx.[object_id] = Obj.[object_id]

INNER JOIN

sys.schemas Sch ON Sch.[schema_id] = Obj.[schema_id]

LEFT OUTER JOIN IncludedColumns IC

ON Ic.TableID = Idx.object_id

AND Ic.IndexID = Idx.index_id

WHERE

index_id > 0

AND Sch.name <> 'sys'

)

SELECT

MD1.SchemaName,

MD1.TableName,

MD1.IndexName,

MD1.is_primary_key,

MD2.is_primary_key,

MD1.is_unique,

MD2.is_unique,

MD1.is_unique_constraint,

MD2.is_unique_constraint,

MD2.IndexName AS OverLappingIndex,

MD1.Col1,

MD1.Col2,

MD1.Col3,

MD1.Col4,

MD1.Col5,

MD1.Col6,

MD1.Col7,

MD1.Col8,

MD1.Col9,

MD1.Filter_definition AS F1,

MD2.Filter_definition AS F2,

MD1.Includes AS Includes1,

MD2.Includes AS Includes2,

'DROP INDEX ' +

MD2.IndexName +

' ON ' +

QUOTENAME(MD2.SchemaName) +

'.' +

QUOTENAME(MD2.TableName) + '

' AS DropCommand

-- Add more columns as needed

FROM

MyDuplicate MD1

INNER JOIN

MyDuplicate MD2 ON MD1.tablename = MD2.tablename

AND MD1.indexname <> MD2.indexname

where md1.COL1 = md2.COL1

AND ISNULL(md1.col2, '') = ISNULL(md2.col2, '')

AND ISNULL(md1.col3, '') = ISNULL(md2.col3, '')

AND ISNULL(md1.col4, '') = ISNULL(md2.col4, '')

AND ISNULL(md1.col5, '') = ISNULL(md2.col5, '')

AND ISNULL(md1.col6, '') = ISNULL(md2.col6, '')

AND ISNULL(md1.col7, '') = ISNULL(md2.col7, '')

AND ISNULL(md1.col8, '') = ISNULL(md2.col8, '')

AND ISNULL(md1.col9, '') = ISNULL(md2.col9, '')

AND (ISNULL(md1.Includes, '') LIKE ISNULL(md2.Includes, '') + '%'

--OR

--ISNULL(md2.Includes, '') LIKE ISNULL(md1.Includes, '') + '%'

)

AND ISNULL(md1.Filter_definition, '') = ISNULL(md2.Filter_definition, '')

AND MD2.is_primary_key = 0

AND MD2.is_unique_constraint = 0

-- AND Md1.is_unique = Md2.is_unique -- Optional condition

ORDER BY SchemaName, TableName, IndexName, OverLappingIndex

Query for older releases

WITH IncludedColumns AS (

SELECT i.object_id AS TableID,

I.index_id AS IndexID,

-- STRING_AGG(c.name, ', ') WITHIN GROUP (ORDER BY c.name) AS Includes

Stuff (( select ', '+ col2.name as [text()]

from sys.indexes ind2

inner join sys.index_columns ic2

on ind2.object_id = ic2.object_id and ind2.index_id = ic2.index_id

inner join sys.columns col2

on ic2.object_id = col2.object_id and ic2.column_id = col2.column_id

where ind2.index_id = i.index_id

and ind2.object_id = t.object_id

And ic2.key_ordinal =0

order by ic2.Key_Ordinal

for XML path ('')),1,1,'')

Includes

FROM sys.index_columns ic

JOIN sys.indexes i

ON ic.object_id = i.object_id

AND ic.index_id = i.index_id

JOIN sys.columns c

ON c.object_id = ic.object_id

AND c.column_id = ic.column_id

join sys.tables t on i.object_id = t.object_id

WHERE ic.is_included_column = 1

GROUP BY i.object_id, i.index_id,t.object_id