Identify Full or Partial Duplicate Indexes

Applicability:

SQL Server 2000: Not Supported

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Not Tested

Credits:

Author: Unknown/ChillyDBA

Date: 4 Dec 2009/10 Jul 2012

Description

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.

Code

Identify Identical Indexes:

WITH indexcols AS

(

SELECT

OBJECT_ID AS id,

index_id AS indid,

name,

(

SELECT CASE key_ordinal

WHEN 0 THEN NULL

ELSE column_id

END AS [data()]

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, column_id

FOR XML path('')

) AS cols,

(

SELECT CASE key_ordinal

WHEN 0 THEN column_id

ELSE NULL

END AS [data()]

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('')

) AS inc

FROM sys.indexes AS i

)

SELECT

'Exact Duplicate (key and included)',

object_schema_name(c1.id) + '.' + OBJECT_NAME(c1.id) AS 'table',

c1.name AS 'index',

c2.name AS 'exactduplicate'

FROM indexcols AS c1

INNER JOIN indexcols AS c2

ON c1.id = c2.id

AND c1.indid < c2.indid

AND c1.cols = c2.cols

AND c1.inc = c2.inc;

Identify Near-Identical Indexes:

WITH indexcols AS

(

SELECT

OBJECT_ID AS id,

index_id AS indid,

name,

(

SELECT CASE key_ordinal

WHEN 0 THEN NULL

-- we need to artificially pad/convert the index ordinals so that '2' does not give a false positive match against '23'

ELSE 'x' + (RIGHT('00000' + CAST(column_id AS VARCHAR(5)),3))

END AS [data()]

FROM sys.index_columns AS k

WHERE k.OBJECT_ID = i.OBJECT_ID

AND k.index_id = i.index_id

ORDER BY key_ordinal, column_id

FOR XML path('')

) AS cols

FROM sys.indexes AS i

)

SELECT

'Partial Duplicate (key only)',

object_schema_name(c1.id) + '.' + OBJECT_NAME(c1.id) AS 'table',

c1.name AS 'index',

c2.name AS 'partialduplicate',

c1.cols, c2.cols

FROM indexcols AS c1

INNER JOIN indexcols AS c2

ON c1.id = c2.id

AND c1.indid < c2.indid

AND (

(c1.cols LIKE c2.cols + '%')

OR

(c2.cols LIKE c1.cols + '%')

) ;

Test Table Creation Code:

USE [AdventureWorksDW]

GO

IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[IndexTestTable]') AND TYPE IN (N'U'))

DROP TABLE [dbo].[IndexTestTable]

GO

CREATE TABLE [dbo].[IndexTestTable](

[Col1] [nchar](10) NULL,

[Col2] [nchar](10) NULL,

[Col3] [nchar](10) NULL,

[Col4] [nchar](10) NULL,

[Col5] [nchar](10) NULL,

[Col6] [nchar](10) NULL,

[Col7] [nchar](10) NULL,

[Col8] [nchar](10) NULL

) ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX [IX1] ON [dbo].[IndexTestTable]

(

[Col1] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX [IX2] ON [dbo].[IndexTestTable]

(

[Col2] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX [IX3] ON [dbo].[IndexTestTable]

(

[Col1] ASC,

[Col2] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX [IX4] ON [dbo].[IndexTestTable]

(

[Col2] ASC,

[Col1] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX [IX5] ON [dbo].[IndexTestTable]

(

[Col1] ASC,

[Col2] ASC,

[Col3] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX [IX6] ON [dbo].[IndexTestTable]

(

[Col2] ASC,

[Col1] ASC,

[Col3] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX [IX7] ON [dbo].[IndexTestTable]

(

[Col1] ASC,

[Col2] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO