What's a comparison key?
Published 21 August 2019
When comparing data sources, SQL Data Compare looks for a matching primary key or other unique identifier in each data source to use as the comparison key. This enables matching rows to be identified, and their differences to be compared.
SQL Data Compare automatically selects a comparison key when:
- tables contain a matching primary key, unique index, or unique constraint
- views contain a matching unique clustered index
- A comparison key cannot include columns whose data type is image, ntext, nvarchar(max), sql_variant, text, varbinary(max), varchar(max), or xml.
- Columns with the data type uniqueidentifier and no other unique constraints are not guaranteed to contain unique values, and so will not be automatically selected as a comparison key.
- You cannot specify custom comparison keys if you are using a backup as a data source; however, you can select an alternative unique index or unique constraint.
Example
For example, the databases WidgetSales and WidgetDeploy each contain the table [dbo].[WidgetPrices]:
Since rows can be inserted and deleted, we can't be certain that the third row in WidgetSales is the same as the third row in WidgetDeploy. Simply comparing rows in the order in which they appear in the table might result in a meaningless comparison. Similarly, rows can't be matched based on their Price values, as more than one widget can have the same price.
In [dbo].[WidgetPrices], RecordID is a primary key. No two widgets will have the same RecordID, and the rows are uniquely identified. Two matching RecordID values in WidgetSales and WidgetDeploy therefore represent the same piece of real world data.
Where there's no matching primary key, or other unique identifier you must set an appropriate comparison key when selecting tables and views for comparison.
For example, if you know that two widgets with the same name are never added to the database on the same day, you can select the two columns WidgetName and DateValidFrom to form the comparison key. The rows in the tables can now be matched.
