Configure comparison keys

The topic describes how to specify a comparison key in a table or view and configure the comparison key selection strategy.

What is a comparison key?

A comparison key uniquely identifies each row in a table or view across source and target databases.

When comparing data sources, dbForge Studio for SQL Server uses a primary key or other unique identifiers in each data source as the comparison key. This enables the tool to match corresponding rows and compare their data accurately.

Example

Consider the following scenario.

Both the development and production databases contain the dbo.UnitPrice table.

Since rows can be inserted and deleted, you can’t be sure in advance whether the fifth row in the development database corresponds to the fifth row in the production database. Thus, comparing rows based on their order is unreliable.

Similarly, rows can’t be matched based on the Price column values, because multiple records may share the same price.

In the dbo.UnitPrice table, the Record_id column serves as the primary key. Because each Record_id is unique, it ensures that each row is distinctly identified. Matching the Record_id values in both the development and production databases guarantees that the rows refer to the same real-world record.

Note

If there is no matching primary key or other unique identifier, you must specify an appropriate comparison key when selecting tables and views for comparison. For example, if you know that two objects with the same name are never added to the database on the same day, you can use the UnitName and Date columns together as the comparison key.

Specify a comparison key

If dbForge Studio can’t find an appropriate comparison key for any table or view, the following warning message appears on the Mapping page of the New Data Comparison wizard - Objects to compare have no key column. This means that you must specify a comparison key for correct data comparison.

To specify a comparison key for the table:

1. On the Mapping page, in the Comparison Key column, click the arrow for the required table and select Custom.

2. In the Column Mapping dialog, in the Key column, select the checkbox for the columns which you want to set as comparison keys.

In the example, the keys are set for the ProductName and BatchDate columns.

Note

You must select at least one column as a comparison key.

3. Click OK.

4. Click Compare.

The data were successfully compared. The Data Comparison document displays the results, though the table has no primary key, and all different rows are clearly identified.

Recommendations for working with comparison keys

When working with comparison keys, consider the following:

• Use stable unique columns and avoid using NULL values in key columns. Columns used as comparison keys must be declared NOT NULL in both the source and target databases to avoid warnings and missing rows.
• Minimize the use of composite keys, as long keys that consist of multiple columns can slow down the comparison process. If a single column is unique, use that column as the key.
• Before synchronization, always verify the key uniqueness by using the following query:

SELECT KeyCol, COUNT(*) FROM Table GROUP BY KeyCol HAVING COUNT(*) > 1;

Otherwise, duplicates will cause conflicts and omit rows.

• For tables without a primary key, select the most distinctive columns, as in our example, ProductName and BatchDate.

• View the warnings on the Mapping page to fix errors and discrepancies before performing the comparison.

Limitations

• You can’t use a comparison key for the columns with the following data types: IMAGE, NTEXT, NVARCHAR(MAX), sql_variant, TEXT, VARBINARY(MAX), VARCHAR(MAX), or XML.
• When you use a backup as a data source, you can’t specify a comparison key.
• If the checkboxes in the Sync column are selected but the checkboxes in the Compare column are not, differences will not be shown after the comparison. However, synchronization will still occur, as the changes will be included in the deployment script.

Configure the comparison key selection strategy

You can configure the comparison key selection strategy in the Options dialog:

1. In the top menu, select Tools > Options.

2. Navigate to Data Comparison > General.

3. Under Comparison Key, select the comparison key option.

4. Click OK to save the changes.

Options for the comparison key selection strategy

The table describes the options of the comparison key selection strategy.

These options enable automatic key assignment when no comparison key exists, but they should be used carefully. For example, selecting First Column as the comparison key when the column is not unique will cause dbForge Studio to mark these rows as conflicts, excluding them from synchronization. Similarly, selecting All Mapped Columns will work correctly only if the row is unique. If the row is not unique, duplicates across all columns will be detected, leading to conflicts.