Table of contents
Comparing indexes between two MSSQL databases is a crucial task for database administrators and developers to ensure consistency and performance across environments.
Whether you're upgrading, migrating, or simply auditing your databases, having an efficient method to compare indexes can save time and avoid potential issues.
This article provides a straightforward T-SQL script to compare indexes between two MSSQL databases, helping you identify missing indexes and differences that could impact performance.
Prerequisites
- SQL Server Management Studio (SSMS) installed.
- Access to a SQL Server instance.
Step 1: Create Source and Target Databases
First, let's create two new databases named SourceDatabase and TargetDatabase.
-- Create Source Database
CREATE DATABASE SourceDatabase;
GO
-- Create Target Database
CREATE DATABASE TargetDatabase;
GO
Step 2: Create Tables and Indexes in Both Databases
Next, we'll create identical tables and indexes in both databases, then modify one of the databases to simulate differences.
-- Create a table and indexes in SourceDatabase
USE SourceDatabase;
GO
CREATE TABLE dbo.Customers (
CustomerID INT PRIMARY KEY,
CustomerName NVARCHAR(100),
Email NVARCHAR(100),
Phone NVARCHAR(15)
);
GO
CREATE INDEX IX_Customers_Email ON dbo.Customers(Email);
CREATE INDEX IX_Customers_Phone ON dbo.Customers(Phone);
GO
-- Create a table and indexes in TargetDatabase
USE TargetDatabase;
GO
CREATE TABLE dbo.Customers (
CustomerID INT PRIMARY KEY,
CustomerName NVARCHAR(100),
Email NVARCHAR(100),
Phone NVARCHAR(15)
);
GO
CREATE INDEX IX_Customers_Email ON dbo.Customers(Email);
-- Intentionally omitting IX_Customers_Phone to simulate a difference
CREATE INDEX IX_Customers_CustomerName ON dbo.Customers(CustomerName); -- New index not in SourceDatabase
GO
Step 3: Compare Indexes Between Source and Target Databases
Now, we will compare the indexes between the two databases using the script provided earlier.
-- Declare variables for database names
DECLARE @SourceDatabaseName NVARCHAR(128) = 'SourceDatabase';
DECLARE @TargetDatabaseName NVARCHAR(128) = 'TargetDatabase';
-- Drop temporary tables if they exist
IF OBJECT_ID('tempdb..#SourceDatabaseIndexes') IS NOT NULL
DROP TABLE #SourceDatabaseIndexes;
IF OBJECT_ID('tempdb..#TargetDatabaseIndexes') IS NOT NULL
DROP TABLE #TargetDatabaseIndexes;
-- Create temporary tables in the main session
CREATE TABLE #SourceDatabaseIndexes (
SchemaName NVARCHAR(128),
TableName NVARCHAR(128),
IndexName NVARCHAR(128),
IndexID INT,
IndexType NVARCHAR(60),
ColumnName NVARCHAR(128),
KeyOrdinal INT,
IsIncludedColumn BIT
);
CREATE TABLE #TargetDatabaseIndexes (
SchemaName NVARCHAR(128),
TableName NVARCHAR(128),
IndexName NVARCHAR(128),
IndexID INT,
IndexType NVARCHAR(60),
ColumnName NVARCHAR(128),
KeyOrdinal INT,
IsIncludedColumn BIT
);
-- Capture index information from the source database
PRINT 'Capturing index information from the source database...';
DECLARE @sql NVARCHAR(MAX);
SET @sql = '
USE [' + @SourceDatabaseName + '];
INSERT INTO #SourceDatabaseIndexes
SELECT
schema_name(t.schema_id) AS SchemaName,
t.name AS TableName,
i.name AS IndexName,
i.index_id AS IndexID,
i.type_desc AS IndexType,
c.name AS ColumnName,
ic.key_ordinal AS KeyOrdinal,
ic.is_included_column AS IsIncludedColumn
FROM sys.indexes i
INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
INNER JOIN sys.tables t ON i.object_id = t.object_id
WHERE i.is_hypothetical = 0
ORDER BY SchemaName, TableName, IndexName, KeyOrdinal;
';
EXEC sp_executesql @sql;
-- Capture index information from the target database
PRINT 'Capturing index information from the target database...';
SET @sql = '
USE [' + @TargetDatabaseName + '];
INSERT INTO #TargetDatabaseIndexes
SELECT
schema_name(t.schema_id) AS SchemaName,
t.name AS TableName,
i.name AS IndexName,
i.index_id AS IndexID,
i.type_desc AS IndexType,
c.name AS ColumnName,
ic.key_ordinal AS KeyOrdinal,
ic.is_included_column AS IsIncludedColumn
FROM sys.indexes i
INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
INNER JOIN sys.tables t ON i.object_id = t.object_id
WHERE i.is_hypothetical = 0
ORDER BY SchemaName, TableName, IndexName, KeyOrdinal;
';
EXEC sp_executesql @sql;
-- Compare indexes between the source and target databases
PRINT 'Comparing indexes between the source and target databases...';
PRINT 'Comparison of Indexes:';
SELECT
COALESCE(s.SchemaName, t.SchemaName) AS SchemaName,
COALESCE(s.TableName, t.TableName) AS TableName,
COALESCE(s.IndexName, t.IndexName) AS IndexName,
COALESCE(s.IndexID, t.IndexID) AS IndexID,
s.IndexType AS SourceIndexType,
t.IndexType AS TargetIndexType,
s.ColumnName AS SourceColumnName,
t.ColumnName AS TargetColumnName,
s.KeyOrdinal AS SourceKeyOrdinal,
t.KeyOrdinal AS TargetKeyOrdinal,
s.IsIncludedColumn AS SourceIsIncludedColumn,
t.IsIncludedColumn AS TargetIsIncludedColumn
FROM #SourceDatabaseIndexes s
FULL OUTER JOIN #TargetDatabaseIndexes t
ON s.SchemaName = t.SchemaName
AND s.TableName = t.TableName
AND s.IndexName = t.IndexName
AND s.IndexID = t.IndexID
AND s.ColumnName = t.ColumnName
ORDER BY SchemaName, TableName, IndexName;
-- Identify missing indexes in the source database
PRINT 'Identifying missing indexes in the source database...';
PRINT 'Indexes present in the target database but missing in the source database:';
SELECT
t.SchemaName,
t.TableName,
t.IndexName,
t.IndexID,
t.IndexType,
t.ColumnName,
t.KeyOrdinal,
t.IsIncludedColumn
FROM #TargetDatabaseIndexes t
LEFT JOIN #SourceDatabaseIndexes s
ON t.SchemaName = s.SchemaName
AND t.TableName = s.TableName
AND t.IndexName = s.IndexName
AND t.IndexID = s.IndexID
AND t.ColumnName = s.ColumnName
WHERE s.IndexName IS NULL
ORDER BY t.SchemaName, t.TableName, t.IndexName;
-- Identify missing indexes in the target database
PRINT 'Identifying missing indexes in the target database...';
PRINT 'Indexes present in the source database but missing in the target database:';
SELECT
s.SchemaName,
s.TableName,
s.IndexName,
s.IndexID,
s.IndexType,
s.ColumnName,
s.KeyOrdinal,
s.IsIncludedColumn
FROM #SourceDatabaseIndexes s
LEFT JOIN #TargetDatabaseIndexes t
ON s.SchemaName = t.SchemaName
AND s.TableName = t.TableName
AND s.IndexName = t.IndexName
AND s.IndexID = t.IndexID
AND s.ColumnName = t.ColumnName
WHERE t.IndexName IS NULL
ORDER BY s.SchemaName, s.TableName, s.IndexName;
Output Explanation and Analysis
After running the provided T-SQL script, you will see several result sets in SQL Server Management Studio (SSMS).
Let's walk through each result set and understand what it means.
1. Capturing Index Information from Source and Target Databases
When you run the script, the first two parts capture index information from the SourceDatabase and TargetDatabase.
The PRINT statements indicate these actions:
- "Capturing index information from the source database..."
- "Capturing index information from the target database..."
2. Comparison of Indexes
The script performs a full outer join on the indexes from both databases, showing you a detailed comparison.
Analysis:
- The IX_Customers_Email index exists in both databases with identical definitions.
- The IX_Customers_Phone index exists in the SourceDatabase but is missing in the TargetDatabase.
- The IX_Customers_Name index exists in the TargetDatabase but is missing in the SourceDatabase.
3. Identifying Missing Indexes in the Source Database
This result set identifies indexes that are present in the TargetDatabase but missing in the SourceDatabase.
The IX_Customers_Name index is missing in the SourceDatabase.
4. Identifying Missing Indexes in the Target Database
This result set identifies indexes that are present in the SourceDatabase but missing in the TargetDatabase.
The IX_Customers_Phone index is missing in the TargetDatabase.
Step 4: Drop the Databases (only for tutorial)
Finally, clean up by dropping the SourceDatabase and TargetDatabase.
-- Drop Source Database
DROP DATABASE SourceDatabase;
GO
-- Drop Target Database
DROP DATABASE TargetDatabase;
GO
Dropping databases in a production environment can result in data loss and service interruption.
The steps to drop the databases are included here for tutorial purposes only.
Ensure you have proper backups and authorization before performing such operations in a live environment.
Keep Your Databases Running Smoothly
Keeping your MSSQL databases in sync is essential for maintaining their performance and reliability.
This simple T-SQL script makes comparing indexes between two databases easy.
It helps you spot discrepancies and address them quickly.
Hopefully, this automated approach saves time and ensures that your databases are always in top shape.
👉You can find the detailed code for the script on GitHub here.
What's next?
🌐 Explore More: Interested in learning about MSSQL, .NET, and other web development insights? Explore our blog for a wealth of information and expert advice.