Data records are identified by the values of primary key columns. Primary key columns must have the same data types. Column name check is case-insensitive, so for example firstName vs FirstName is considered as the same column.
Data record is considered as changed if it has changes in some non-primary-key column and that column has the same data type. If a non-primary-key column has a different data type then it is not compared at all.
If the table has no compatible primary key (same set of columns with same data types) then data records are compared just by row number and Data diff tab has an appropriate warning: No comparison key(s). Result can be truncated. Application does not know how to identify whether the record is the same or not, changed or not. Due to the missing key columns the application can not perform paging and has to limit the number of shown records. Record order in this case is also not guaranteed. Such a data diff result can be used just to get some kind of data changes overview, but it can't be used to identify the number of new and changed records.
By default the Data diff tab is sorting data by primary keys. You can change sort order. You can also change to sorting column but in this case Data diff may provide unreliable results with invalid changes (both false-positive and false-negative) and shows appropriate warning: Sorting on non-key column(s). To understand why it happens, let's consider the following example: some table with ID primary key, Name column to compare and the following data:
| Left table | Right table | |||
| ID 🔑 ▲ | Name | ID 🔑 ▲ | Name | |
| 1 | Apple | 1 | Green apple | |
| 2 | Banana | 2 | Banana | |
Such a table has record counts Total = 2, New = 0 and Changed = 1 for both sides.
Now if we perform sorting by name then in the right table record with ID=1 will have to be shown last because 'Green apple' > 'Banana':
| Left table | Right table | |||
| ID 🔑 | Name ▲ | ID 🔑 | Name ▲ | |
| 1 | Apple | |||
| 2 | Banana | 2 | Banana | |
| 1 | Green apple | |||
Such a table has record counts Total = 2, New = 1 and Changed = 0 for both sides which is obviously wrong. And visually it may cause an impression that we have more records than expected.
Last updated: 2023-09-20
