Compare the data of database objects
The Compare Data action compares the output of tables, views, and materialized views. To find this action, select two objects in the Database tool window ( ) , right-click the selection and navigate to Tools. Also, you can double-click these objects and then compare them from the editor.
You can compare the contents of two objects that belong to different types. For example, the output of a table and a materialized view.
Description of Tolerance and Detect column insertion is available in Controls of the Diff Viewer for contents.
By default, when you issue a query, the number of returned rows is limited to 500. This limitation is introduced to avoid an overload (for example, when your SELECT statement returns one million rows). To increase the number of compared rows, configure the Limit page size to parameter.
Compare the contents of two tables from the Database tool window
By default, when you issue a query, the number of returned rows is limited to 500. This limitation is introduced to avoid an overload (for example, when your SELECT statement returns one million rows). To increase the number of compared rows, configure the Limit page size to parameter.
In the Database tool window ( ) , select two tables.
Right-click the selection and navigate to .
If needed, change the value of the Tolerance parameter in the comparison dialog. The Tolerance parameter defines a maximum number of differences that are allowed between two result sets. For example, if you want to consider two rows as equal if their data differs in a single column, enter
1in the Tolerance field.
Compare the contents of two tables from the data editor
Open two tables that you want to compare. To open a table, double-click it in the Database tool window.
In the editor, click the Compare Data button (
) and select the second table.
Controls of the Diff Viewer for contents
In the Diff Viewer for contents, you can use the same sorting functionality that is available in the data editor. For more information about sorting columns, refer to Sort data.
The primary purpose of the Diff Viewer for contents is to show the differences and similarities of data.
To highlight the differences, DataSpell uses the following color coding:
Color | Description |
|---|---|
| Rows that differ. |
| Cells that differ in a column. |
| Rows that are considered equal. |
Detect column insertion
When the tables have a different number of columns, extra columns in the table with more columns are ignored. If the Detect column insertion option is on, the most different columns are ignored. In the following picture, the first column in the second table is the most different and so it is ignored. As a result, the second row is shown as containing the same data.
If the option is off, ignored are the last of the columns. On the following picture, the last column in the second table is ignored. So all the rows are shown as containing different data.
Tolerance
The Tolerance parameter defines how many columns might differ to consider two rows equal. For example, if you set Tolerance to one, rows that differ in one column are considered equal.
With tolerance set to zero, such rows are considered different.
With this setting, you can also check the columns that differ when data in rows is different. Such rows in those columns are highlighted. Increase the Tolerance option if you have different data more than in one row. For example, with Tolerance set to 1, you can see that between two tables only the last_name column differs.
