What is a comparison key?

Last modified: August 8, 2024

Comparison keys uniquely identify every single row in a table or a view for both the Source and Target.

When comparing data sources, Data Compare for SQL Server uses a matching primary key or other unique identifiers in each data source as the comparison key. This enables rows to be identified as matching and then compared.

Consider the following example. The databases Production and Orders each contain the table [dbo].[UnitPrice].

As rows can be inserted and deleted, you can’t be sure that the fifth row in the Production database is the same as the fifth row in the Orders database. So if you simply compare rows in the order in which they appear in the table, it can result in a meaningless comparison. In a similar way, rows can’t be matched based on their Price values, as more than one unit can have the same price.

In [dbo].[UnitPrices], Record_id is a primary key. No two units can have the same Record_id, and thus the rows are uniquely identified. Two matching Record_id values in Production and Orders, therefore, represent the same piece of real-world data.

Note

If there’s no matching primary key or another unique identifier you need to set an appropriate comparison key when selecting tables and views for comparison. For example, if you know that two units with the same name are never added to the database on the same day, you can select the two columns UnitName and Date to form the comparison key.