Overlapping Indexes With INCLUDE Columns: Merging Them Safely

Two indexes can share the same keys while each carries a different set of extra columns. Overlapping indexes are consolidation candidates, but their filters, uniqueness, and workload uses decide whether one can replace both.

Separate Search Keys From Coverage

Key columns determine the index's ordered access path. INCLUDE columns add leaf-level coverage without becoming search-order keys. Two definitions with the same column names can still serve different purposes when a column changes from an include to a key.

Order matters for composite keys. CustomerID followed by OrderedUtc differs from OrderedUtc followed by CustomerID. Direction also matters when satisfying a sort, particularly with mixed directions. Compare the complete ordered key list before comparing the extra payload.

I start with the definitions, not with index names. Names can describe old intentions rather than current behavior. Two cheerful names saying FastOrders do not certify two useful access paths. The scripts below read metadata from the current database and make those distinctions visible.

List the Keys and Includes Separately

The following query lists rowstore nonclustered indexes on user tables. It builds ordered key text and a separate included-column list. STRING_AGG uses large-value expressions so longer definitions do not hit an unnecessarily short concatenation limit.

The query shows uniqueness, filter details, and data-space identity beside the column lists. Those details affect whether consolidation is safe. It does not model every physical property, implicit clustered key, or partitioning rule. Treat the output as an inventory for review rather than as an automatic drop generator.

;WITH Keys AS
(
    SELECT ic.object_id, ic.index_id,
        STRING_AGG(CONVERT(nvarchar(max), QUOTENAME(c.name) +
            CASE WHEN ic.is_descending_key = 1 THEN N' DESC' ELSE N' ASC' END), N', ')
            WITHIN GROUP (ORDER BY ic.key_ordinal) AS KeyColumns
    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.key_ordinal > 0
    GROUP BY ic.object_id, ic.index_id
), Includes AS
(
    SELECT ic.object_id, ic.index_id,
        STRING_AGG(CONVERT(nvarchar(max), QUOTENAME(c.name)), N', ')
            WITHIN GROUP (ORDER BY ic.column_id) AS IncludedColumns
    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
)
SELECT OBJECT_SCHEMA_NAME(i.object_id) AS SchemaName,
    OBJECT_NAME(i.object_id) AS TableName, i.name AS IndexName,
    k.KeyColumns, inc.IncludedColumns, i.is_unique,
    i.is_primary_key, i.is_unique_constraint,
    i.has_filter, i.filter_definition, i.data_space_id
FROM sys.indexes AS i
JOIN sys.tables AS t ON t.object_id = i.object_id
JOIN Keys AS k ON k.object_id = i.object_id AND k.index_id = i.index_id
LEFT JOIN Includes AS inc ON inc.object_id = i.object_id AND inc.index_id = i.index_id
WHERE i.type = 2 AND i.is_hypothetical = 0 AND t.is_ms_shipped = 0
ORDER BY SchemaName, TableName, IndexName;

Find Overlapping Indexes With Identical Keys

Start with identical ordered keys on the same table. The next query compares a key signature built from column identifiers and direction. It excludes unique, filtered, disabled, and hypothetical indexes. Matching data spaces narrows the candidates further.

This deliberately handles a straightforward subset of overlapping indexes. A shorter leading-key prefix and a longer key can also overlap, but the longer definition changes ordering and size. Review those cases separately. A shared first column alone is not evidence that either index replaces the other.

;WITH KeySignatures AS
(
    SELECT object_id, index_id,
        STRING_AGG(CONVERT(varchar(max),
            CONVERT(varchar(11), column_id) + ':' + CONVERT(varchar(1), is_descending_key)), ',')
            WITHIN GROUP (ORDER BY key_ordinal) AS KeySignature
    FROM sys.index_columns
    WHERE key_ordinal > 0
    GROUP BY object_id, index_id
)
SELECT OBJECT_SCHEMA_NAME(a.object_id) AS SchemaName,
    OBJECT_NAME(a.object_id) AS TableName,
    a.name AS FirstIndex, b.name AS SecondIndex, ka.KeySignature
FROM sys.indexes AS a
JOIN sys.indexes AS b ON b.object_id = a.object_id AND b.index_id > a.index_id
JOIN KeySignatures AS ka ON ka.object_id = a.object_id AND ka.index_id = a.index_id
JOIN KeySignatures AS kb ON kb.object_id = b.object_id AND kb.index_id = b.index_id
JOIN sys.tables AS t ON t.object_id = a.object_id
WHERE a.type = 2 AND b.type = 2 AND t.is_ms_shipped = 0
  AND a.is_unique = 0 AND b.is_unique = 0
  AND a.has_filter = 0 AND b.has_filter = 0
  AND a.is_disabled = 0 AND b.is_disabled = 0
  AND a.is_hypothetical = 0 AND b.is_hypothetical = 0
  AND a.data_space_id = b.data_space_id
  AND ka.KeySignature = kb.KeySignature
ORDER BY SchemaName, TableName, FirstIndex, SecondIndex;

Merge Two Overlapping Indexes in a Scratch Example

The next example creates two indexes with identical keys and different INCLUDE columns. One covers Amount and one covers Status. A candidate merged index keeps the key sequence and includes both payload columns. The clustered primary key is also available implicitly in the nonclustered indexes.

Run this only in a scratch database. Creating the third index temporarily adds another structure to maintain. That is useful for testing the replacement, but leaving all three indefinitely defeats the consolidation. Do not remove the originals until representative reads and writes have been compared.

CREATE TABLE dbo.OverlapDemo
(
    OrderID int NOT NULL PRIMARY KEY CLUSTERED,
    CustomerID int NOT NULL,
    OrderedUtc datetime2(7) NOT NULL,
    Amount decimal(12,2) NOT NULL,
    Status varchar(12) NOT NULL
);
CREATE INDEX IX_Overlap_Amount
ON dbo.OverlapDemo (CustomerID, OrderedUtc) INCLUDE (Amount);
CREATE INDEX IX_Overlap_Status
ON dbo.OverlapDemo (CustomerID, OrderedUtc) INCLUDE (Status);
CREATE INDEX IX_Overlap_Merged
ON dbo.OverlapDemo (CustomerID, OrderedUtc) INCLUDE (Amount, Status);

Preserve Constraints and Filter Semantics

A unique index enforces a rule, whether it also supports a useful query or not. A nonunique merged index cannot replace that constraint. A primary key or unique constraint also has schema dependencies beyond a convenient access path. Keep those roles explicit.

Filtered indexes cover only selected rows and have statistics for that subset. Combining two payload lists does not preserve different filters. Replacing a selective filtered index with an unfiltered wide index also changes maintenance and plan choices. Compare the predicate and supported workload, not merely matching keys.

When building the include union, remove repeated column names and columns already present as keys. Do not promote an include column into the key just to reproduce a copied list. That changes the access path and can widen upper index levels. Include only the coverage each retained query needs. An index carrying every column becomes another copy of the table with its own maintenance bill.

Partitioning, compression, fill factor, and special storage properties need review too. A key-list comparison omits those details. When an index supplies a required operational feature, carry that requirement into the candidate definition or leave the index alone.

Read Usage With Its Time Window

Inspect seeks, scans, lookups, and update activity before any removal. The next query keeps indexes with no current usage row visible. An absent row means no retained observation in this DMV, not proof that no query ever needs the index.

Counters reset across events such as engine restarts, and database lifecycle changes also affect their history. They are operation counts rather than a complete measure of rows changed or maintenance cost. Keep the engine start time beside the results and collect enough business-cycle coverage.

SELECT sqlserver_start_time AS EngineStartTime FROM sys.dm_os_sys_info;
SELECT i.name AS IndexName,
    u.user_seeks, u.user_scans, u.user_lookups, u.user_updates,
    u.last_user_seek, u.last_user_scan, u.last_user_lookup, u.last_user_update
FROM sys.indexes AS i
LEFT JOIN sys.dm_db_index_usage_stats AS u
    ON u.database_id = DB_ID() AND u.object_id = i.object_id AND u.index_id = i.index_id
WHERE i.object_id = OBJECT_ID(N'dbo.OverlapDemo') AND i.index_id > 0
ORDER BY i.name;

Compare Representative Read and Write Work

A wider merged leaf can use more pages than either narrow leaf. A query that needs only Amount can lose some efficiency even though it remains covered. Conversely, maintaining one combined structure can cost less than maintaining two. Measure the tradeoff on representative data.

Check the actual plans for predicates and ordering. Also inspect procedure hints, plan guides, and forced plans referring to old definitions. An index referenced by name has an application dependency that a metadata signature does not reveal.

I keep the recreation definitions before approving index removal. That makes a measured regression reversible. Do your infrequent month-end queries appear in the observation window? If they do not, replay them safely on the test copy before interpreting quiet usage as redundancy.

SET STATISTICS IO ON;
SELECT OrderID, OrderedUtc, Amount
FROM dbo.OverlapDemo
WHERE CustomerID = 10
ORDER BY OrderedUtc;
SELECT OrderID, OrderedUtc, Status
FROM dbo.OverlapDemo
WHERE CustomerID = 10
ORDER BY OrderedUtc;
SET STATISTICS IO OFF;

On my scratch copy, both sample queries chose the merged index, so the two originals collected no usage at all. Usage counters gathered while a candidate exists describe the new competition, not the old need.

Drop Overlapping Indexes Only After the Replacement Is Proven

For a real consolidation, retain the same ordered keys, the required include union, and the necessary physical and semantic properties. Compare representative plans, reads, write overhead, and storage. Record the reviewed removal decision and a straightforward recreation script.

Remove the approved originals through the normal change process, then monitor the same workload again. Do not infer success from the replacement index existing. The workload must still receive the needed access paths without the redundant maintenance you intended to remove.

Overlapping indexes are a useful cleanup lead. Their similarity helps identify a candidate, while workload evidence establishes the replacement. Make that decision deliberately, and keep constraint semantics ahead of the desire for a shorter index list.

Related reading on this blog: Query Listing All the Indexes Key Column with Included Column and Finding Unused Indexes.

An overlapping index is not automatically redundant, it is a definition that needs a workload comparison.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.