Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2011
    Location
    Nasik, Maharastra
    Posts
    34

    Unanswered: Duplicate indexes found.

    I have find out almost 80 duplicate indexes in my database while tuning db with the use of following query:

    Can anybody suggest me how to delete all duplicate indexes in one go instead of deleting individual.

    Important suggestions will be appreciated.

    ================================================== ======
    -- Duplicate Index Scripts
    -- Original Author: Pinal Dave (C) 2011
    -- SQL Server Journey with SQL Authority
    WITH MyDuplicate AS (SELECT
    Sch.[name] AS SchemaName,
    Obj.[name] AS TableName,
    Idx.[name] AS IndexName,
    INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 1) AS Col1,
    INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 2) AS Col2,
    INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 3) AS Col3,
    INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 4) AS Col4,
    INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 5) AS Col5,
    INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 6) AS Col6,
    INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 7) AS Col7,
    INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 8) AS Col8,
    INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 9) AS Col9,
    INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 10) AS Col10,
    INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 11) AS Col11,
    INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 12) AS Col12,
    INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 13) AS Col13,
    INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 14) AS Col14,
    INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 15) AS Col15,
    INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 16) AS Col16
    FROM sys.indexes Idx
    INNER JOIN sys.objects Obj ON Idx.[object_id] = Obj.[object_id]
    INNER JOIN sys.schemas Sch ON Sch.[schema_id] = Obj.[schema_id]
    WHERE index_id > 0)
    SELECT MD1.SchemaName, MD1.TableName, MD1.IndexName,
    MD2.IndexName AS OverLappingIndex,
    MD1.Col1, MD1.Col2, MD1.Col3, MD1.Col4,
    MD1.Col5, MD1.Col6, MD1.Col7, MD1.Col8,
    MD1.Col9, MD1.Col10, MD1.Col11, MD1.Col12,
    MD1.Col13, MD1.Col14, MD1.Col15, MD1.Col16
    FROM MyDuplicate MD1
    INNER JOIN MyDuplicate MD2 ON MD1.tablename = MD2.tablename
    AND MD1.indexname <> MD2.indexname
    AND MD1.Col1 = MD2.Col1
    AND (MD1.Col2 IS NULL OR MD2.Col2 IS NULL OR MD1.Col2 = MD2.Col2)
    AND (MD1.Col3 IS NULL OR MD2.Col3 IS NULL OR MD1.Col3 = MD2.Col3)
    AND (MD1.Col4 IS NULL OR MD2.Col4 IS NULL OR MD1.Col4 = MD2.Col4)
    AND (MD1.Col5 IS NULL OR MD2.Col5 IS NULL OR MD1.Col5 = MD2.Col5)
    AND (MD1.Col6 IS NULL OR MD2.Col6 IS NULL OR MD1.Col6 = MD2.Col6)
    AND (MD1.Col7 IS NULL OR MD2.Col7 IS NULL OR MD1.Col7 = MD2.Col7)
    AND (MD1.Col8 IS NULL OR MD2.Col8 IS NULL OR MD1.Col8 = MD2.Col8)
    AND (MD1.Col9 IS NULL OR MD2.Col9 IS NULL OR MD1.Col9 = MD2.Col9)
    AND (MD1.Col10 IS NULL OR MD2.Col10 IS NULL OR MD1.Col10 = MD2.Col10)
    AND (MD1.Col11 IS NULL OR MD2.Col11 IS NULL OR MD1.Col11 = MD2.Col11)
    AND (MD1.Col12 IS NULL OR MD2.Col12 IS NULL OR MD1.Col12 = MD2.Col12)
    AND (MD1.Col13 IS NULL OR MD2.Col13 IS NULL OR MD1.Col13 = MD2.Col13)
    AND (MD1.Col14 IS NULL OR MD2.Col14 IS NULL OR MD1.Col14 = MD2.Col14)
    AND (MD1.Col15 IS NULL OR MD2.Col15 IS NULL OR MD1.Col15 = MD2.Col15)
    AND (MD1.Col16 IS NULL OR MD2.Col16 IS NULL OR MD1.Col16 = MD2.Col16)
    ORDER BY
    MD1.SchemaName,MD1.TableName,MD1.IndexName
    ================================================== ======

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    To start with, I wouldn't use that script for identifying duplicate indexes. On our database, it showed the following two indexes as overlapping:

    CREATE NONCLUSTERED INDEX [idx_BillHeaderStatus] ON [dbo].[tbl_BillHeader]
    ([Status] ASC)
    INCLUDE ([ID], [ClaimHeaderID], [DOI], [PrimaryICD9], [PtAcctNo], [TotalCharged])
    GO

    CREATE NONCLUSTERED INDEX [idx_BillHeaderStatusDate] ON [dbo].[tbl_BillHeader]
    ([StatusDate] ASC)
    GO

    Clearly they are not overlapping.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Oops. My bad.
    Turns out the developer had a similarly named index which was a duplicate.
    [IX_tbl_BillHeader_StatusStatusDate]
    Pinal's code may be fine.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...or maybe not. His code does not appear to assume all the indexes are use the same sort order.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •