THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Paul Nielsen

www.SQLServerBible.com

Find Duplicate Indexes

These queries mix a few basic DMVs with a little relational division to indentify every set of duplicate indexes.
 
This is the result of a collaborative effort. I started with a group by version of this query about a week ago and posted it on the private MVP Newsgroups. Itzik Ben-Gan replied with a pretty cool variation, and then I made some minor edits to handle partial dups and include column logic.
 
The first query finds exact matches. The indexes must have the same key columns in the same order, and the same included columns but in any order. These indexes are sure targets for elimination. The only caution would be to check for index hints. 
 
-- exact duplicates
with indexcols as
(
select object_id as id, index_id as indid, name,
(select case keyno when 0 then NULL else colid end as [data()]
from sys.sysindexkeys as k
where k.id = i.object_id
and k.indid = i.index_id
order by keyno, colid
for xml path('')) as cols,
(select case keyno when 0 then colid else NULL end as [data()]
from sys.sysindexkeys as k
where k.id = i.object_id
and k.indid = i.index_id
order by colid
for xml path('')) as inc
from sys.indexes as i
)
select
object_schema_name(c1.id) + '.' + object_name(c1.id) as 'table',
c1.name as 'index',
c2.name as 'exactduplicate'
from indexcols as c1
join indexcols as c2
on c1.id = c2.id
and c1.indid < c2.indid
and c1.cols = c2.cols
and c1.inc = c2.inc;
 
The second variation of this query finds partial, or duplicate, indexes that share leading key columns, e.g. Ix1(col1, col2, col3) and Ix2(col1, col2) would be considered duplicate indexes. This query only examines key columns and does not consider included columns.
These types of indexes are probable dead indexes walking.

-- Overlapping indxes
with indexcols as
(
select object_id as id, index_id as indid, name,
(select case keyno when 0 then NULL else colid end as [data()]
from sys.sysindexkeys as k
where k.id = i.object_id
and k.indid = i.index_id
order by keyno, colid
for xml path('')) as cols
from sys.indexes as i
)
select
object_schema_name(c1.id) + '.' + object_name(c1.id) as 'table',
c1.name as 'index',
c2.name as 'partialduplicate'
from indexcols as c1
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 + '%') ;

Be careful when dropping a partial duplicate index if the two indexes differ greatly in width. For example, if Ix1 is a very wide index with 12 columns, and Ix2 is a narrow two-column index that shares the first two columns, you may want to leave Ix2 as a faster, tighter, narrower index.
 
Subscribe to my free e-Newsletter.
Published Wednesday, June 25, 2008 12:47 AM by Paul Nielsen
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

albTotxo said:

Hi, Paul

Very useful scripts, but I've noticed there is a problem in the second one. If you have indexes with an index_id over 9 it can return false positives. For instance, if you have an index on column 1 and another one on columns 15 16 it will return them as overlapping, but they're not.

I think changing the last two lines for these solves the problem:

and ((c1.cols like c2.cols + '%' and SUBSTRING(c1.cols,LEN(c2.cols)+1,1) = ' ')

or (c2.cols like c1.cols + '%' and SUBSTRING(c2.cols,LEN(c1.cols)+1,1) = ' ')) ;

I added a check to verify if the char next to the last in the column set is an space, which means it is not a false positive.

June 25, 2008 4:28 AM
 

albTotxo said:

One more thing:

I think it woulk make sense to add one more condition to delete from the resultset the indexes that are exact duplicates, because they are covered by the first script, so the last part of the script would be something like this:

and c1.cols <> c2.cols -- remove exact duplicates

and ((c1.cols like c2.cols + '%' and SUBSTRING(c1.cols,LEN(c2.cols)+1,1) = ' ')

or (c2.cols like c1.cols + '%' and SUBSTRING(c2.cols,LEN(c1.cols)+1,1) = ' ')) ;

June 25, 2008 6:04 AM
 

csm said:

Very, very useful script, really. But it could be more if it includes the use of the indexes (user_seeks, user_scans, user_lookups and user_updates) from sys.dm_db_index_usage_stats's dmv.

The second script shows those indexes that could be dropped. But, in case you're not sure about drop it or not, the use of that index could answer any doubt.

June 25, 2008 9:12 AM
 

Paul Nielsen said:

albTotxo

- Thanks for the over 9 check, good call.

- I considered filtering out exact matches from the partial matches, but decided against becasue a shop might employ only the partial dups query and a DBA later on might miss eact matches.

csm

- I considered it, maybe in the next version. Even if the partial is being used sometimes, it's still probably a good candidate to drop.

Originally I set out to creaste a stored procedure that would automatically drop exact dups and close partial dups. Is this something that would be useful?

-Paul

June 25, 2008 11:54 AM
 

Phillip Senn said:

Happy birthday, Paul!

How many duplicates is this?

June 25, 2008 2:15 PM
 

Merrill Aldrich said:

June 25, 2008 6:53 PM
 

Merl said:

June 25, 2008 6:57 PM
 

Steve Dassin said:

If only developers had to be concerned with keys instead of indexes. Would you write a blog about duplicate keys? Those damn relational ideas :-)

June 26, 2008 1:04 AM
 

Carl Federl said:

From some testing, noticed that there were some statistics that matched some indexes but not sure of the impact.

Tried to use the provided SQL and had a number of problems:

1.  The SQL does not work with SQL Server version 7 - as I am supporting 40 SQL Server 7 and only 7 with 2005, this lack of functionallity is critical to my environment.

2.  Performance of the SQL when there are a lot of indexes is not optimal.

Here is a different SQL Solution that uses a traditional divide solution of "the count of the index columns" is equal to "the count of the matching index columns"  As you can see, the traditional DIVIDE uses about half the resources of the XML solution:

Match    Solution CPU Logical IO Duration

Full XML 1362 125909 1533

Full DIVIDE 470 60965 712

Partial  XML 1161 93465 1953

Partial Divide 360 48237 524

-- Create test data:

-- For the TALLY table, see http://www.sqlservercentral.com/articles/TSQL/62867/

declare @CreateTableSQL varchar(max)

set @CreateTableSQL =

'CREATE TABLE dbo.Foo_!seq

(c1 int NOT NULL,c2 int NOT NULL,c3 int NOT NULL,c4 int NOT NULL

, CONSTRAINT foo_!seq_p PRIMARY KEY CLUSTERED (c1,c2)

, CONSTRAINT foo_!seq_u1 UNIQUE NONCLUSTERED (c1,c2,c3))

CREATE NONCLUSTERED INDEX foo_!seq_x1 ON dbo.foo_!seq (c1 ,c2 )

CREATE NONCLUSTERED INDEX foo_!seq_x2 ON dbo.foo_!seq (c1 ,c2 ) INCLUDE ( c3 ,c4)

CREATE STATISTICS foo_!seq_s1 ON dbo.foo_!seq (c1 ,c2 )'

--select @CreateTableSQL

IF OBJECT_ID('tempdb..#DDL') is not null drop table #DDL

select  REPLACE(@CreateTableSQL  ,'!seq',cast(tally.n as varchar(8))) as SQLStmt

into #DDL

from master.dbo.tally as tally

where tally.n between 1 and 1000

exec dbo.sp_execresultset @cmd = 'select SQLStmt from #DDL'

select count(*) from information_schema.tables

-- Match on all index columns

select sysusers.name as SchemaName

, sysobjects.name as TableName

, sysindexes.name as IndexName

, DupIndex.name As DuplicateIndexName

, sysindexes.indid as IndexInd

, DupIndex.indid as DuplicateIndid

FROM sysusers

join sysobjects

on sysobjects.uid = sysusers.uid

join sysindexes

on sysindexes.id = sysobjects.id

join (

select sysindexkeys.id

, sysindexkeys.indid

--, object_name(sysindexkeys.id) -- For Debugging

, count(*) as BaseKeyCnt

from dbo.sysindexkeys

where sysindexkeys.indid not in (0,255)

group by sysindexkeys.id

, sysindexkeys.indid

-- order by sysindexkeys.id , sysindexkeys.indid -- For Debugging

) as IndexKeys

on IndexKeys.id = sysindexes.id

and IndexKeys.indid = sysindexes.indid

join (

select sysindexkeys.id

, sysindexkeys.indid

, DupIndexkeys.indid as Dupindid

, count(*) as DupKeyCnt

--, object_name(sysindexkeys.id) -- For Debugging

from dbo.sysindexkeys

join dbo.sysindexkeys as DupIndexkeys

on  DupIndexkeys.id = sysindexkeys.id

and DupIndexkeys.indid > sysindexkeys.indid

and DupIndexkeys.colid = sysindexkeys.colid

and DupIndexkeys.keyno = sysindexkeys.keyno

where sysindexkeys.indid not in (0,255)

and DupIndexkeys.indid not in (0,255)

group by sysindexkeys.id

, sysindexkeys.indid

, DupIndexkeys.indid

-- order by sysindexkeys.id , sysindexkeys.indid , DupIndexkeys.indid -- For Debugging

) as DupKeys

on  DupKeys.id = IndexKeys.id

and DupKeys.indid = IndexKeys.indid

and DupKeys.DupKeyCnt = IndexKeys.BaseKeyCnt

join sysindexes as DupIndex

on DupIndex.id = DupKeys.id

and DupIndex.indid = DupKeys.Dupindid

-- Match on first two index columns:

select sysusers.name as SchemaName

, sysobjects.name as TableName

, sysindexes.name as IndexName

, DupIndex.name As DuplicateIndexName

, DupKeys.DupKeyCnt As DuplicateLeadingColumnCnt

, sysindexes.indid as IndexInd

, DupIndex.indid as DuplicateIndid

FROM sysusers

join sysobjects

on sysobjects.uid = sysusers.uid

join sysindexes

on sysindexes.id = sysobjects.id

join (

select sysindexkeys.id

, sysindexkeys.indid

, DupIndexkeys.indid as Dupindid

, count(*) as DupKeyCnt

--, object_name(sysindexkeys.id) -- For Debugging

from dbo.sysindexkeys

join dbo.sysindexkeys as DupIndexkeys

on  DupIndexkeys.id = sysindexkeys.id

and DupIndexkeys.indid > sysindexkeys.indid

and DupIndexkeys.colid = sysindexkeys.colid

and DupIndexkeys.keyno = sysindexkeys.keyno

where sysindexkeys.indid not in (0,255)

and DupIndexkeys.indid not in (0,255)

and sysindexkeys.keyno > 0

and DupIndexkeys.keyno > 0

group by sysindexkeys.id

, sysindexkeys.indid

, DupIndexkeys.indid

HAVING COUNT(*) >= 2

-- order by sysindexkeys.id , sysindexkeys.indid , DupIndexkeys.indid -- For Debugging

) as DupKeys

on  DupKeys.id = sysindexes.id

and DupKeys.indid = sysindexes.indid

join sysindexes as DupIndex

on DupIndex.id = DupKeys.id

and DupIndex.indid = DupKeys.Dupindid

June 30, 2008 9:19 AM
 

Paul Nielsen said:

Hi Carl, Glad to see you here - long time !

My first pass at this query was the relational division (is count = count) method. Itzik suggested the XML path() method and what I like about it is that it's very elegent in testing for partial matches in the order of the key columns. But I'll take a hard look at what you've written as well - always looking for a better way. But as you point out the most elegent solution sometimes doesn't perform.

June 30, 2008 11:56 PM
 

Rob Vallee said:

Very nice scripts! Thanks Paul

October 28, 2008 9:35 AM
 

JJ said:

I just found this query from a link in a blog post from Brad McGehee.  I didn't think I would have any duplicates in any of my databases, but I was curious to see if I missed something.  So, I ran the first query on a database with almost 300 tables.  I found 24 duplicates!  Yikes.  What was going on?

I haven't done a lot of research yet, but I found one cause that I can now work to remedy.  I use ERwin to generate the schema for my databases.  I have it set up so that by default, ERwin generates indexes for foreign keys.  Also by default, the primary keys are created as clustered indexes.  Guess what happens when a table's primary key is also a foreign key.  Yep, both get created.

I looked further into ERwin and see that I can keep the defaults, but have control at an index level to choose not to generate certain indexes.  So, I can go back in and fine-tune my database so that when I re-create it in the future, it will create a more efficient database.  Also, I now know about something else I must think about when I generate new tables and databases.  

Thanks for sharing this issue and query to help fix it!!!

April 20, 2009 1:02 PM
 

Halcyon said:

Great script - neat and very usefull especially when you inherit a database

Thank you

June 3, 2010 1:12 PM
 

john winds said:

Any chance of combining this with "dm_db_missing_index_group_stats"?

October 4, 2010 6:17 PM
 

Regan said:

Nice query Paul.

I've taken the liberty of extending it a bit to give the kind of detail needed when reporting - space used by the duplicate, number of rows, server name etc.

Based on my understanding, there is no method used to "choose2 which should be considered the duplicate - I've not looked into that, but I think that would be an interesting extension - something like "clustered indexes will always be considered the non-duplicate", "Primary Key indexes should be considered non0duplicate". Anything other rationale's?

The modified version reports the following (database names removed to protect the innocent :) )

Apologies for format of table in trext.

ServerChecked DataBaseChecked DateChecked_UTC TableName IndexName ExactDuplicateIndexName SpaceUsed_MB_ByDuplicate RowsIn_Duplicate DuplicateIndex_Usage DuplicateIndex_Updates

SQLServer DB 03/11/2010 11:47:03.930 TB1 PK_TB1 IX_TB1 0.4375 13534 31 23623

SQLServer DB 03/11/2010 11:47:03.930 TB2 PK__TB2 idx4 23.585937 3370357 28 6933

SQLServer DB 03/11/2010 11:47:03.930 TB3 PK__TB3 idx2 1.195312 114235 36 511

SQLServer DB 03/11/2010 11:47:03.930 TB4 PK__TB4 idx2 2.132812 243757 70 744

SQLServer DB 03/11/2010 11:47:03.930 TB5 PK__TB5 idx2 1.132812 84550 766 698

Modified query (apologies for the alias renaming - it  made more sense to me, and apologies in advance, as I expect that my tabbing will get stripped by the page):

-- exact duplicates

with indexcols as

(

select TableID = IX.[object_id],

index_id ,

name,

( select case keyno when 0 then NULL else colid end as [data()]

from sys.sysindexkeys as IXKeys1

where IXKeys1.id = IX.[object_id]

and IXKeys1.indid = IX.index_id

order by keyno, colid

for xml path('')

) as cols,

( select case keyno when 0 then colid else NULL end as [data()]

from sys.sysindexkeys as IXKeys2

where IXKeys2.id = IX.[object_id]

and IXKeys2.indid = IX.index_id

order by colid

for xml path('')

) as inc

from sys.indexes as IX

)

select

'ServerChecked' = @@Servername,

'DataBaseChecked' = DB_Name(),

'DateChecked_UTC' = GETUTCDATE(),

'TableName' = object_schema_name(IndexSet1.TableID)

+ '.' + object_name(IndexSet1.TableID),

'IndexName' = IndexSet1.name,

'ExactDuplicateIndexName' = IndexSet2.name,

'SpaceUsed_MB_ByDuplicate' = (SIx.Reserved)/128.0, --*8 to KB, / 1024 to MB = nett / 128.

'RowsIn_Duplicate' = SIx.[rows],

'DuplicateIndex_Usage' = (DupIXUsage.user_seeks + DupIXUsage.user_scans + DupIXUsage.user_lookups),

'DuplicateIndex_Updates' = (DupIXUsage.user_updates)

from indexcols as IndexSet1

join indexcols as IndexSet2

on IndexSet1.TableID = IndexSet2.TableID

and IndexSet1.index_id < IndexSet2.index_id

and IndexSet1.cols = IndexSet2.cols

and IndexSet1.inc = IndexSet2.inc

left join sys.sysindexes SIx

on IndexSet2.name = SIx.name

and IndexSet2.TableID = SIx.[id]

left join sys.dm_db_index_usage_stats DupIXUsage

on IndexSet2.TableID = DupIXUsage.[object_id]

and IndexSet2.index_id = DupIXUsage.index_id

and DupIXUsage.Database_id = db_id() --current database

Order by

TableName, IndexName, ExactDuplicateIndexName

November 3, 2010 7:55 AM
 

Mitchell said:

Paul,

Thanks! Your query is very helpful! Do have a way to run the code for  a multiple database view on the same server?

Thanks,

Mitchell

December 28, 2010 2:50 PM
 

Tim said:

Very nice query, I have a question though.  I ran the query and started poking around to check the indexes returned, I found quite a few indexes reported as duplicates to not exist anymore.  

I verified statistics updated on the tables in question.

Any idea why the query would be returning indexes that no longer exist?

Thanks

September 1, 2011 5:19 PM
 

Jacob Buter said:

This would be my script:

SET NOCOUNT ON

DECLARE @objname     nvarchar(776)

     , @objid       int            -- the object id of the table

     , @indid       smallint       -- the index id of an index

     , @groupid     smallint       -- the filegroup id of an index

     , @indname     sysname

     , @groupname   sysname

     , @status      int

     , @keys        nvarchar(2126) --Length (16*max_identifierLength)+(15*2)+(16*3)

     , @dbname     sysname

     , @usrname     sysname

     , @i           int

     , @thiskey     nvarchar(131)  -- 128+3

-- Check to see that the object names are local to the current database.

SELECT @dbname = parsename(@objname,3)

IF  @dbname IS NOT NULL

AND @dbname <> db_name()

BEGIN

 raiserror(15250,-1,-1)

END

-- create temp table

create table #indextable

(usr_name          sysname

,table_name        sysname

,index_name        sysname        collate database_default

,stats             int

,groupname         sysname        collate database_default

,index_keys        nvarchar(2126) collate database_default  -- see @keys above for length descr

)

-- OPEN CURSOR OVER TABLES

DECLARE cur_tables CURSOR LOCAL STATIC

FOR

 SELECT t1.id

      , t1.name

      , t2.name

   FROM sysobjects t1

  INNER JOIN sysusers t2 on t1.uid = t2.uid

  WHERE type = 'U'

OPEN cur_tables

FETCH cur_tables

INTO @objid

   , @objname

   , @usrname

WHILE @@fetch_status >= 0

BEGIN

-- OPEN CURSOR OVER INDEXES

 DECLARE cur_indexes CURSOR LOCAL STATIC

 FOR

   SELECT indid

        , groupid

        , name

        , status

     FROM sysindexes

    WHERE id = @objid

      AND indid > 0

      AND indid < 255

      AND (status & 64) = 0

    ORDER BY indid

 OPEN cur_indexes

 FETCH cur_indexes

  INTO @indid

     , @groupid

     , @indname

     , @status

 WHILE @@fetch_status >= 0

 BEGIN

   -- First we'll figure out what the keys are.

   SELECT @keys = index_col(@usrname + '.' + @objname, @indid, 1)

        , @i = 2

   IF (indexkey_property(@objid, @indid, 1, 'isdescending') = 1)

     SELECT @keys = @keys

                  + '(-)'

   SELECT @thiskey = index_col(@usrname + '.' + @objname, @indid, @i)

   IF ((@thiskey IS NOT NULL)

   AND (indexkey_property(@objid, @indid, @i, 'isdescending') = 1))

     SELECT @thiskey = @thiskey

                     + '(-)'

   WHILE (@thiskey IS NOT NULL)

   BEGIN

     SELECT @keys = @keys

                  + ', '

                  + @thiskey

          , @i = @i + 1

     SELECT @thiskey = index_col(@usrname + '.' + @objname, @indid, @i)

     IF ((@thiskey IS NOT NULL)

     AND (indexkey_property(@objid, @indid, @i, 'isdescending') = 1))

       SELECT @thiskey = @thiskey + '(-)'

   END

   SELECT @groupname = groupname

     FROM sysfilegroups

    WHERE groupid = @groupid

   -- INSERT ROW FOR INDEX

   INSERT INTO #indextable

          VALUES (@usrname, @objname, @indname, @status, @groupname, @keys)

   -- Next index

   FETCH cur_indexes

    INTO @indid

       , @groupid

       , @indname

       , @status

 END

 CLOSE cur_indexes

 DEALLOCATE cur_indexes

 FETCH cur_tables

  INTO @objid

     , @objname

     , @usrname

END

DEALLOCATE cur_tables

-- DISPLAY THE RESULTS DUPLICATED

SELECT @@SERVERNAME as Instance

    , DB_NAME() as DatabaseName

    , t1.usr_name as usr_name

    , t1.table_name as table_name

    , t1.index_name as index_name

    , convert(varchar(210), --bits 16 off, 1, 2, 16777216 on, located on group

              case

                when (t1.stats & 16)<>0

                  then 'clustered'

                  else 'nonclustered'

              end

            + case

                when (t1.stats & 1)<>0

                  then ', ignore duplicate keys'

                  else ''

              end

            + case

                when (t1.stats & 2)<>0

                  then ', unique'

                  else ''

              end

            + case

                when (t1.stats & 4)<>0

                  then ', ignore duplicate rows'

                  else ''

              end

            + case

                when (t1.stats & 64)<>0

                  then ', statistics'

                  else

                    case

                      when (t1.stats & 32)<>0

                        then ', hypothetical'

                        else ''

                    end

              end

            + case

                when (t1.stats & 2048)<>0

                  then ', primary_key'

                  else ''

              end

            + case

                when (t1.stats & 4096)<>0

                  then ', unique_key'

                  else ''

              end

           + case

               when (t1.stats & 8388608)<>0

                 then ', auto_create'

                 else ''

             end

           + case

               when (t1.stats & 16777216)<>0

                 then ', stats_no_recompute'

                 else ''

               end

           + ' located on '

           + t1.groupname) as index_description

    , t2.index_keys as index_keys

    , t2.index_name as index_name

    , convert(varchar(210), --bits 16 off, 1, 2, 16777216 on, located on group

              case

                when (t2.stats & 16)<>0

                  then 'clustered'

                  else 'nonclustered'

              end

            + case

                when (t2.stats & 1)<>0

                  then ', ignore duplicate keys'

                  else ''

              end

            + case

                when (t2.stats & 2)<>0

                  then ', unique'

                  else ''

              end

            + case

                when (t2.stats & 4)<>0

                  then ', ignore duplicate rows'

                  else ''

              end

            + case

                when (t2.stats & 64)<>0

                  then ', statistics'

                  else

                    case

                      when (t2.stats & 32)<>0

                        then ', hypothetical'

                        else ''

                    end

              end

            + case

                when (t2.stats & 2048)<>0

                  then ', primary_key'

                  else ''

              end

            + case

                when (t2.stats & 4096)<>0

                  then ', unique key'

                  else ''

              end

            + case

                when (t2.stats & 8388608)<>0

                  then ', auto create'

                  else ''

                end

            + case

                when (t2.stats & 16777216)<>0

                  then ', stats no recompute'

                  else ''

              end

            + ' located on '

            + t2.groupname) as index_description

    , t2.index_keys as index_keys

 FROM #indextable t1

INNER JOIN #indextable t2 ON t1.table_name = t2.table_name

                         AND t1.index_name != t2.index_name

                         AND t1.index_keys = t2.index_keys

ORDER BY t1.table_name

       , t1.index_name

-- DISPLAY THE RESULTS OVERLAPPING

SELECT @@SERVERNAME as Instance

    , DB_NAME() as DatabaseName

    , t1.usr_name as usr_name

    , t1.table_name as table_name

    , t1.index_name as index_name

    , convert(varchar(210), --bits 16 off, 1, 2, 16777216 on, located on group

              case

                when (t1.stats & 16)<>0

                  then 'clustered'

                  else 'nonclustered'

              end

            + case

                when (t1.stats & 1)<>0

                  then ', ignore duplicate keys'

                  else ''

              end

            + case

                when (t1.stats & 2)<>0

                  then ', unique'

                  else ''

              end

            + case

                when (t1.stats & 4)<>0

                  then ', ignore duplicate rows'

                  else ''

              end

            + case

                when (t1.stats & 64)<>0

                  then ', statistics'

                  else

                    case

                      when (t1.stats & 32)<>0

                        then ', hypothetical'

                        else ''

                    end

              end

            + case

                when (t1.stats & 2048)<>0

                  then ', primary_key'

                  else ''

              end

            + case

                when (t1.stats & 4096)<>0

                  then ', unique key'

                  else ''

              end

           + case

               when (t1.stats & 8388608)<>0

                 then ', auto create'

                 else ''

             end

           + case

               when (t1.stats & 16777216)<>0

                 then 'stats no recompute'

                 else ''

               end

           + ' located on '

           + t1.groupname) as index_description

    , t2.index_keys as index_keys

    , t2.index_name as index_name

    , convert(varchar(210), --bits 16 off, 1, 2, 16777216 on, located on group

              case

                when (t2.stats & 16)<>0

                  then 'clustered'

                  else 'nonclustered'

              end

            + case

                when (t2.stats & 1)<>0

                  then ', ignore duplicate keys'

                  else ''

              end

            + case

                when (t2.stats & 2)<>0

                  then ', unique'

                  else ''

              end

            + case

                when (t2.stats & 4)<>0

                  then ', ignore duplicate rows'

                  else ''

              end

            + case

                when (t2.stats & 64)<>0

                  then ', statistics'

                  else

                    case

                      when (t2.stats & 32)<>0

                        then ', hypothetical'

                        else ''

                    end

              end

            + case

                when (t2.stats & 2048)<>0

                  then ', primary_key'

                  else ''

              end

            + case

                when (t2.stats & 4096)<>0

                  then ', unique key'

                  else ''

              end

            + case

                when (t2.stats & 8388608)<>0

                  then ', auto create'

                  else ''

                end

            + case

                when (t2.stats & 16777216)<>0

                  then ', stats no recompute'

                  else ''

              end

            + ' located on '

            + t2.groupname) as index_description

    , t2.index_keys as index_keys

 FROM #indextable t1

INNER JOIN #indextable t2 ON t1.table_name = t2.table_name

                         AND t1.index_name != t2.index_name

                         AND t1.index_keys like t2.index_keys + ',' + '%'

                         AND LTRIM(RTRIM(t1.index_keys)) != LTRIM(RTRIM(t2.index_keys))

ORDER BY t1.table_name

       , t1.index_name

DROP TABLE #indextable

October 31, 2011 2:58 PM
 

Paul Nielsen said:

nice, Jacob.

October 31, 2011 3:22 PM
 

mssqldude said:

Very nice, i've used your scripts. Thanks! Mark

November 3, 2011 3:38 PM
 

Jacob Buter said:

Take a look at my blog site

December 29, 2011 4:14 PM
 

George Mastros said:

Nice blog.  I do have one minor issue with the exact duplicate query.  If you have a clustered index and a non-clustered index that has the same key columns, it is returned by the query.  However, if you have a query that only uses the key column(s), the non-clustered index will give you better performance.  So, even if there is a duplicate based on the key columns, there are occasionally valid reasons for have a duplicate.

April 11, 2012 1:17 PM
 

Jacob Buter said:

November 20, 2012 4:08 PM

Leave a Comment

(required) 
(required) 
Submit

About Paul Nielsen

Paul Nielsen believes SQL is the romance language of data. As such he’s a hands-on database developer, Microsoft SQL Server MVP, trainer, and author of SQL Server Bible series (Wiley). As a data architect, he developed the concepts of Smart Database Design and Nordic – an open source O/R dbms for SQL Server. He lives in Colorado Springs.

This Blog

Syndication

News

news item test
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement