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.
- Start SQLTreeo Compare and choose Compare database schemas.
- Pick the Source database and the Target database, and press Compare.
- 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.
- 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
DROPstatements, 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
DESCwhere 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
DROPyourself.
Next steps
- How to compare two SQL Server databases covers the whole schema, with the options that prevent false differences.
- Compare stored procedures, views and functions between databases has a similar query for code.
- Download SQLTreeo Compare and start the 30-day trial.