SQL Server Exact Duplicate Index Finder

Purpose

Identifies indexes on the same table that have identical key column lists and identical INCLUDE column lists — exact duplicates that provide no additional query coverage over one another. Retaining both imposes extra write overhead on every INSERT, UPDATE, and DELETE against the table without providing any benefit that the surviving index does not already cover.

The script builds a normalized signature string for each index — ordered key columns, then ordered INCLUDE columns — and self-joins on that signature to surface pairs. Each row shows both indexes, their types, uniqueness, and whether either is a primary key or unique constraint, which determines which one should be retained when the other is dropped.

Uses sys.index_columns (the current catalog view) rather than the legacy sys.sysindexkeys compatibility view.

Supported Platforms and Versions

SQL Server 2017, 2019, and 2022 (on-premises and SQL Server on Azure VMs). Azure SQL Database is supported.

STRING_AGG (used to build the column signature) requires SQL Server 2017 or later. The script will not run on SQL Server 2016 — use STUFF(...FOR XML PATH('')) instead if earlier version support is needed.

Required Permissions

SELECT on sys.indexes, sys.index_columns, and sys.columns. These are available to any user with VIEW DATABASE STATE or the db_datareader role on system catalog views.

Preconditions

Run in the context of the database you want to analyse (USE [YourDatabase]). The script scans all user tables in the current database.

Risk Level

Low. Read-only. No indexes are dropped. Identifies candidates only.

Script

-- ============================================================
-- SQL Server Exact Duplicate Index Finder
-- Permissions : VIEW DATABASE STATE (or db_datareader on sys views)
-- Risk        : Read-only — identifies candidates, drops nothing
-- Platform    : SQL Server 2017+ / Azure SQL Database
-- ============================================================

WITH index_key_cols AS
(
    SELECT
        ic.object_id,
        ic.index_id,
        STRING_AGG(c.name, ',')
            WITHIN GROUP (ORDER BY ic.key_ordinal)          AS key_cols
    FROM sys.index_columns AS ic
    JOIN sys.columns        AS c
        ON c.object_id = ic.object_id
       AND c.column_id = ic.column_id
    WHERE ic.is_included_column = 0
      AND ic.key_ordinal        > 0      -- skip the row-locator pseudo-column (ordinal 0)
    GROUP BY ic.object_id, ic.index_id
),
index_inc_cols AS
(
    SELECT
        ic.object_id,
        ic.index_id,
        STRING_AGG(c.name, ',')
            WITHIN GROUP (ORDER BY c.name)                  AS inc_cols
    FROM sys.index_columns AS ic
    JOIN sys.columns        AS c
        ON c.object_id = ic.object_id
       AND c.column_id = ic.column_id
    WHERE ic.is_included_column = 1
    GROUP BY ic.object_id, ic.index_id
),
index_signatures AS
(
    SELECT
        i.object_id,
        i.index_id,
        i.name                              AS index_name,
        i.type_desc                         AS index_type,
        i.is_unique,
        i.is_primary_key,
        i.is_unique_constraint,
        i.fill_factor,
        k.key_cols,
        ISNULL(n.inc_cols, '')              AS inc_cols
    FROM sys.indexes         AS i
    JOIN index_key_cols      AS k
        ON k.object_id = i.object_id
       AND k.index_id  = i.index_id
    LEFT JOIN index_inc_cols AS n
        ON n.object_id = i.object_id
       AND n.index_id  = i.index_id
    WHERE i.index_id > 0                   -- exclude heaps
)
SELECT
    OBJECT_SCHEMA_NAME(s1.object_id) + '.' + OBJECT_NAME(s1.object_id)
                                                    AS [table],
    s1.index_name                                   AS [index],
    s1.index_type,
    s1.is_unique,
    s1.is_primary_key,
    s1.is_unique_constraint,
    s1.fill_factor,
    s1.key_cols,
    s1.inc_cols,
    s2.index_name                                   AS duplicate_index,
    s2.index_type                                   AS duplicate_index_type,
    s2.is_unique                                    AS duplicate_is_unique,
    s2.is_primary_key                               AS duplicate_is_pk,
    s2.is_unique_constraint                         AS duplicate_is_uc,
    s2.fill_factor                                  AS duplicate_fill_factor
FROM index_signatures AS s1
JOIN index_signatures AS s2
    ON  s1.object_id  = s2.object_id
    AND s1.index_id   < s2.index_id
    AND s1.key_cols   = s2.key_cols
    AND s1.inc_cols   = s2.inc_cols
ORDER BY [table], s1.key_cols;

Safe Execution Guidance

1. Run in the context of the database to analyse (USE [YourDatabase]).
2. Each output row represents one duplicate pair. Both indexes in the pair cover the same queries — one can be dropped.
3. Before dropping, check which index is referenced by foreign keys or query hints:

   -- Check if the index is referenced in query hints (search plan cache)
   SELECT TOP 10 qs.execution_count, qp.query_plan
   FROM sys.dm_exec_query_stats AS qs
   CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
   WHERE CAST(qp.query_plan AS nvarchar(max)) LIKE '%<index name>%';

4. Prefer retaining the index with the more restrictive uniqueness (is_unique = 1 or is_primary_key = 1) and dropping the non-unique duplicate, unless usage metadata from sys.dm_db_index_usage_stats indicates otherwise.
5. Generate the DROP INDEX statement only after confirming the impact:

   -- Generate candidate DROP statements — review before executing
   SELECT
       'DROP INDEX ' + QUOTENAME(s2.index_name)
       + ' ON ' + QUOTENAME(OBJECT_SCHEMA_NAME(s1.object_id))
       + '.' + QUOTENAME(OBJECT_NAME(s1.object_id)) + ';' AS drop_statement,
       s2.is_primary_key,
       s2.is_unique_constraint
   FROM index_signatures AS s1
   JOIN index_signatures AS s2
       ON s1.object_id = s2.object_id AND s1.index_id < s2.index_id
       AND s1.key_cols = s2.key_cols AND s1.inc_cols = s2.inc_cols;
   -- Note: primary keys and unique constraints cannot be dropped with DROP INDEX.
   -- Use ALTER TABLE DROP CONSTRAINT for those.

Expected Output

One row per duplicate pair. If no rows are returned, no exact duplicates exist in the current database.

Interpreting Results

Both indexes non-unique: Either can be dropped. Check sys.dm_db_index_usage_stats to see which has fewer seeks/scans since the last service restart — that one is a better candidate for removal.

One index is a primary key or unique constraint: The constraint-backed index cannot be dropped with DROP INDEX. The non-constraint index is the candidate for removal. Use ALTER TABLE DROP CONSTRAINT only if the constraint itself is redundant.

Different fill factors: A different fill factor does not affect query coverage — only insert/update fragmentation behaviour. This is not a reason to keep both.

Clustered index appears as a duplicate: The clustered index key is automatically appended to every nonclustered index entry. A nonclustered index whose key columns exactly match the clustered index key is redundant — the clustered index itself is used for lookups on those columns. The nonclustered duplicate is the candidate to drop.

Supplement: Index Usage Statistics

Cross-reference with usage statistics to confirm the duplicate has low usage before dropping:

SELECT
    OBJECT_SCHEMA_NAME(i.object_id) + '.' + OBJECT_NAME(i.object_id) AS [table],
    i.name AS index_name,
    u.user_seeks,
    u.user_scans,
    u.user_lookups,
    u.user_updates,
    u.last_user_seek,
    u.last_user_scan
FROM sys.indexes AS i
LEFT JOIN sys.dm_db_index_usage_stats AS u
    ON u.object_id   = i.object_id
    AND u.index_id   = i.index_id
    AND u.database_id = DB_ID()
WHERE i.object_id IN (
    -- Paste the object IDs returned by the duplicate finder
    SELECT object_id FROM sys.indexes WHERE name IN ('IndexA', 'IndexB')
)
ORDER BY [table], i.name;

Note: sys.dm_db_index_usage_stats resets on service restart. Confirm the observation window is representative of a full workload cycle.

Rollback Steps

Not applicable — read-only script.

Official References

• sys.index_columns
• sys.indexes
• sys.dm_db_index_usage_stats
• Reorganize and Rebuild Indexes