Applies to
ApexSQL Data Diff
Summary
This article explains how to use the Custom comparison keys in ApexSQL Data Diff in order to uniquely identify and compare table rows without a primary key or unique constraint/index.
Description
During the comparison process, ApexSQL Data Diff uses Comparison keys, which uniquely identify each single row in a table or a view for both the source and destination data sources. Tables that have a primary key constraint or a unique constraint (or index) will have automatically defined Comparison key list.
ApexSQL Data Diff is also able to deal with tables without a primary key or a unique constraint/index by allowing the user to define a temporary custom key that will be used by ApexSQL Data Diff during the comparison process.
To access and edit the Custom comparison keys feature, expand the Advanced options in the New project window or opening an existing one, and select the Object filter tab:
The Tables grid will be shown, containing automatically paired tables from both the source and destination data sources:
Select a table for which a custom comparison key needs to be set, click the arrow button under the Comparison key column, and click the Edit custom key option from the drop-down list:
In the Customize comparison key window, all available paired columns are listed based on the previously selected table pairs. Here, it is possible to select a single or multiple columns to be used for the custom comparison key. This is also the final step in this process:
The feature offers a simple way to manage and control the comparison process when the automatic pairing cannot be used or when additional “tuning” is required in situations when the comparison shows “incomparable” tables because there are no primary keys or unique indexes defined on them.
Once the comparison process is done, the key icon will be appointed next to the column that was checked as the Custom comparison key in the Data differences panel:
The Custom comparison keys feature gives ApexSQL Data Diff precise and flexible control over the comparison engine behavior.
FAQs
Q: Are there any specific columns (data types) that must be used as custom comparison keys?
A: No, you can use any column (data type) as a custom comparison key.
Q: Is there a limit in numbers of columns that can be checked as custom comparison keys?
A: No, you can use as many columns as you need.
