Home » Intermediate »sqlserverpedia-syndication »SSC »TSQL » Currently Reading:

Find Duplicate (and covered) Indexes

October 11, 2012 Intermediate, sqlserverpedia-syndication, SSC, TSQL No Comments

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

Comment on this Article:







MidnightSQL Consulting

Need help? Got an emergency? Write us at Support@MidnightDBA.com!

We can schedule time to help with your backup/restore issues, high availability and disaster recovery setup, performance problems, and a great deal more. Very often, we're even available on the moment for downtime issues and emergencies.

For more information about MidnightSQL consulting, email us or check out www.MidnightSQL.com. Happy days!

Where are We?

Blog Posts by Category

DBAs@Midnight

How to Eat Pop-tarts
Watch DBAs@Midnight live on Fridays,m 11pm Central time

The best database career advice you’ve never heard!

DBARoadmap.com

The DBA Roadmap Seminar is 7 MP3 tracks (over 5 hours!) of insider guidance on your database career. We'll teach you how and what to study as a DBA, weigh in on controversial resume debates, teach you to recognize a worthy recruiter, and discuss the new professionalism of interviews. Also some bonus materials, PDF companion guides, and really spiffy intro music!

Once your $99 PayPal payment is submitted, you'll get the download link in e-mail! (Download is a 370Mb ZIP file.)

Become a DBA. Become a BETTER DBA. Use the Roadmap.

Visit www.DBARoadmap.com for info, forums, and more!

Add to Cart View Cart

Cunningham’s Law

"The best way to get the right answer on the Internet is not to ask a question, it's to post the wrong answer."
Relevant: http://xkcd.com/386/