Find Duplicate (and covered) Indexes

A while back, I wrote a simple little query that lets you Get Index Included Column Info (because sp_help and sp_helpindex only show non-included index columns).  More recently, I’ve been wanting not only a better way to look at index information, but a way to compare indexes…for example, to find duplicate or covered indexes.

Here, friends and neighbors, is that new and better way:
-- Get indx information comparing indexes to find duplicate/covered indexes.
SELECT OBJECT_NAME(i.OBJECT_ID) AS TableName, i.name AS IndexName, i.type_desc, i.is_unique, i.is_primary_key,
cols = STUFF((
SELECT CASE
WHEN ic.is_descending_key = 1 THEN ', ' + c.name + '(-)'
ELSE ', ' + c.name
END
FROM sys.index_columns ic
INNER JOIN sys.columns c ON c.OBJECT_ID = ic.OBJECT_ID
AND c.column_id = ic.column_id
WHERE i.index_id = ic.index_id
AND i.OBJECT_ID = ic.OBJECT_ID
AND ic.is_included_column = 0
ORDER BY i.OBJECT_ID,i.index_id, ic.is_included_column ASC, ic.key_ordinal
FOR XML PATH ('')),1,2,''),
IncludedCols = STUFF((
SELECT CASE
WHEN ic.is_descending_key = 1 THEN ', ' + c.name + '(-)'
ELSE ', ' + c.name END
FROM sys.index_columns ic
INNER JOIN sys.columns c ON c.OBJECT_ID = ic.OBJECT_ID
AND c.column_id = ic.column_id
WHERE i.index_id = ic.index_id
AND i.OBJECT_ID = ic.OBJECT_ID
AND ic.is_included_column = 1
ORDER BY i.OBJECT_ID,i.index_id, ic.is_included_column ASC, ic.key_ordinal
FOR XML PATH ('')),1,2,'')
INTO #TMP
FROM sys.indexes i
WHERE
--i.object_id = object_id('Calls') /* search by table name */ -- i.name = 'ix_calls2' /* search by index name */
is_disabled = 0
AND is_hypothetical = 0
ORDER BY TableName;

(Note: I have tried my damndest to get this code to display prettily, but I can’t, no matter what. Feel free to copy paste into The Simple-Talk Code Prettifier to make sense of it.)

This little baby will load one row per index into a temp table; each row has a comma delimited list of that index’s columns, and another of included columns, in their key_ordinal order.  That means the first in the list is the first in the index. Here’s an example of some output:

We do that little comma delimited list trick with the FOR XML hack I’ve demonstrated in my T-SQL Brush-up session (see the Sessions and Pubs page for recordings of that session).

So what does this mean? It means that you can more easily see which indexes are like which other indexes, or even identical. Here’s a query that shows you which indexes have identical column lists (this query doesn’t compare included columns, but it does display them):

SELECT t1.TableName
, t1.indexname AS [Index1]
, t1.type_desc AS [Index1 type]
, t2.indexname AS [Index2]
, t1.cols + ISNULL(' (' + t1.IncludedCols + ')','') AS Index1Def
, t2.cols + ISNULL(' (' + t2.IncludedCols + ')','') AS Index2Def
FROM #tmp t1
INNER JOIN #TMP t2 ON t1.TableName = t2.TableName AND t1.indexname <> t2.indexname AND t1.cols = t2.cols
WHERE t2.type_desc = 'nonclustered'

Note that you could also choose to eliminate primary keys or unique indexes from consideration, as you wouldn’t want to delete those.

So there you go, a handy dandy solution for digging into your indexes. Use it how you like, and use it well!

Happy days,
Jen McCown
MidnightDBA.com/Jen