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