Embedded Files
Applicability:
Applicability:
SQL Server 2000: Not Supported
SQL Server 2005: Tested
SQL Server 2008: Tested
SQL Server 2008R2: Tested
SQL Server 2012: Not Tested
Credits:
Credits:
Author: Unknown/ChillyDBA
Date: 4 Dec 2009/10 Jul 2012
Description
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
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
Page updated
Google Sites
Report abuse