SQLTreeo Compare · How-to

Compare indexes between two SQL Server databases

Find indexes that exist in one SQL Server database but not the other, or have other columns, with SQLTreeo Compare across servers, or with a T-SQL query.

SQLTreeo Compare shows which indexes exist in one SQL Server database and not in the other, or have other columns, as part of the schema comparison, also when the databases are on different servers. That is often the answer when a query is fast in production and slow in test, and the plans differ: somebody created an index by hand to solve an incident, and it never made it into the deployment scripts.

The steps come first. Further down is a query that does the comparison when both databases are on the same server.

With SQLTreeo Compare

SQLTreeo Compare compares the indexes as part of the tables, together with everything else in the schema, and the two databases can be on different servers without a linked server.

  1. Start SQLTreeo Compare and choose Compare database schemas.
  2. Pick the Source database and the Target database, and press Compare.
  3. A table with a missing or changed index is listed under Different. Select the table to see which parts differ, the index among them, and a side-by-side diff of the two scripts.
  4. Tick what you want to bring over and press Deploy…. The script that makes the target match the source is shown first, with the number of changes and the DROP statements, before anything runs. Save script… hands it to your own change process instead.

These options decide what counts as a difference, all under Comparison options on the project page:

  • Ignore fill factor and index padding and Ignore data compression are off by default, so an index that differs only in those is reported. Switch them on when the environments differ on purpose.
  • Ignore filegroups and data spaces and Ignore names of system-named constraints are on by default, so another filegroup or the generated name of a primary key does not show up as a difference.

Under Object types to compare you can leave out the types you do not need, and Include objects and Exclude objects take names such as sales.* to limit the comparison to some tables.

Comparing and reading the differences and the script are free. Copying, saving and deploying work during the 30-day trial or with a license, see Trial and licensing.

What the query compares

An index is more than its name. The query below compares, per table and index name:

  • whether the index exists on both sides,
  • its type (clustered, nonclustered, columnstore) and whether it is unique,
  • the key columns in order, with DESC where the column is sorted descending,
  • the included columns,
  • the filter of a filtered index.

It leaves out fill factor, compression, partitioning, the filegroup and whether the index is disabled. Add them if they matter for your comparison.

A query for two databases on one server

Replace SourceDb and TargetDb with the names of the two databases, in all places. The query reads the system views of both with three-part names, so it needs no linked server. It uses STRING_AGG, so it needs SQL Server 2017 or later.

WITH src AS (
    SELECT s.name AS schema_name, t.name AS table_name, i.name AS index_name,
           i.type_desc, i.is_unique, i.filter_definition,
           (SELECT STRING_AGG(CONCAT(c.name, CASE WHEN ic.is_descending_key = 1 THEN ' DESC' END), ', ')
                   WITHIN GROUP (ORDER BY ic.key_ordinal)
            FROM SourceDb.sys.index_columns AS ic
            JOIN SourceDb.sys.columns AS c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
            WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id
              AND ic.is_included_column = 0 AND ic.key_ordinal > 0) AS key_columns,
           (SELECT STRING_AGG(c.name, ', ') WITHIN GROUP (ORDER BY c.name)
            FROM SourceDb.sys.index_columns AS ic
            JOIN SourceDb.sys.columns AS c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
            WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id
              AND ic.is_included_column = 1) AS included_columns
    FROM SourceDb.sys.indexes AS i
    JOIN SourceDb.sys.tables AS t ON t.object_id = i.object_id
    JOIN SourceDb.sys.schemas AS s ON s.schema_id = t.schema_id
    WHERE i.type > 0 AND t.is_ms_shipped = 0
),
tgt AS (
    SELECT s.name AS schema_name, t.name AS table_name, i.name AS index_name,
           i.type_desc, i.is_unique, i.filter_definition,
           (SELECT STRING_AGG(CONCAT(c.name, CASE WHEN ic.is_descending_key = 1 THEN ' DESC' END), ', ')
                   WITHIN GROUP (ORDER BY ic.key_ordinal)
            FROM TargetDb.sys.index_columns AS ic
            JOIN TargetDb.sys.columns AS c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
            WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id
              AND ic.is_included_column = 0 AND ic.key_ordinal > 0) AS key_columns,
           (SELECT STRING_AGG(c.name, ', ') WITHIN GROUP (ORDER BY c.name)
            FROM TargetDb.sys.index_columns AS ic
            JOIN TargetDb.sys.columns AS c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
            WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id
              AND ic.is_included_column = 1) AS included_columns
    FROM TargetDb.sys.indexes AS i
    JOIN TargetDb.sys.tables AS t ON t.object_id = i.object_id
    JOIN TargetDb.sys.schemas AS s ON s.schema_id = t.schema_id
    WHERE i.type > 0 AND t.is_ms_shipped = 0
)
SELECT COALESCE(src.schema_name, tgt.schema_name) AS schema_name,
       COALESCE(src.table_name, tgt.table_name) AS table_name,
       COALESCE(src.index_name, tgt.index_name) AS index_name,
       CASE WHEN tgt.index_name IS NULL THEN 'only in source'
            WHEN src.index_name IS NULL THEN 'only in target'
            ELSE 'different' END AS difference,
       src.key_columns AS source_keys, tgt.key_columns AS target_keys,
       src.included_columns AS source_included, tgt.included_columns AS target_included
FROM src
FULL JOIN tgt ON tgt.schema_name = src.schema_name
             AND tgt.table_name = src.table_name
             AND tgt.index_name = src.index_name
WHERE src.index_name IS NULL OR tgt.index_name IS NULL
   OR src.type_desc <> tgt.type_desc
   OR src.is_unique <> tgt.is_unique
   OR ISNULL(src.filter_definition, '') <> ISNULL(tgt.filter_definition, '')
   OR ISNULL(src.key_columns, '') <> ISNULL(tgt.key_columns, '')
   OR ISNULL(src.included_columns, '') <> ISNULL(tgt.included_columns, '')
ORDER BY schema_name, table_name, index_name;

Heaps (i.type = 0) are left out. A result looks like this:

schema_name table_name index_name difference source_keys target_keys source_included target_included
dbo Orders IX_Orders_Customer different CustomerId, OrderDate DESC CustomerId, OrderDate Total NULL
dbo Orders IX_Orders_Old only in target NULL Total NULL NULL
dbo Orders IX_Orders_Status only in source Status NULL NULL NULL

Where this query falls short

  • The names must match. An index that has the same columns but another name is shown as one index only in the source and one only in the target. Primary key and unique constraints that SQL Server named itself (PK__Orders__A1B2C3) differ in every database, so you have to look at those by hand.
  • Both databases must be on one server, or reachable through a linked server, and you have to run it with rights to read both.
  • It does not write the script that creates or drops the indexes. For that you script the index in SSMS, and review every DROP yourself.

Next steps