Cool Tips‎ > ‎Indexes‎ > ‎

Indexes - 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

ċ
Find full and partial index duplicates.sql
(5k)
Andy Hughes,
Jul 11, 2012, 12:00 PM
Comments