Row-Level Tracking and Column-Level Tracking
Several options are available for specifying how the Merge Agent recognizes a conflict. One option is specifying whether conflicts are recognized at the row level or at the column level.
When conflicts are recognized at the row level, changes made to corresponding rows are judged a conflict, whether or not the changes are made to the same column. For example, suppose one change is made to the address column of a Publisher row, and a second change is made to the phone number column (in the same table) of the corresponding Subscriber row. With row-level tracking, a conflict is detected, because changes were made to both rows. With column-level tracking, no conflict is detected, because changes were made to different columns in the rows.
Resolution of the conflict is the same, regardless of which tracking option is used; the entire row of data is overwritten by data from the conflict winner. In the earlier example, suppose the phone number is changed at both the Publisher and Subscriber, and the address is changed only in the Subscriber row. If the Publisher wins the conflict, the entire Publisher row overwrites the Subscriber row for both row-level tracking and column-level tracking; thus, the original value for phone number in the Publisher overwrites the changed value in the Subscriber. If you are using column-level tracking, and one user changes the address for a particular row, and another user changes the phone number for the same row, there is no conflict and both changes will be accepted.
The application semantics usually determine which tracking option to use. For example, if you are updating customer data that is generally entered at the same time, such as an address and phone number, row-level tracking should be chosen. If column-level tracking were chosen in this situation, changes to the customer address in one location and to the customer phone number in another location would not be detected as a conflict: the data would be merged on synchronization and the error would be missed. In other situations, updating individual columns from different sites may be the most logical choice. For example, two sites may have access to different types of statistical information on a customer, such as income level and total dollar amount of credit card purchases. Selecting column-level tracking ensures that both sites can enter the statistical data for different columns without generating unnecessary conflicts.
Row-level tracking involves less tracking overhead. Column-level tracking may result in fewer conflicts being detected by the Merge Agent, but can be more resource intensive in terms of the storage needed to track changes. Column-level tracking may generate less network traffic during synchronization because only the changed columns are transferred to the partner database (the publication database or the subscription database).
To set row- or column-level tracking for an article