Managing Replicated timestamp Data

Microsoft® SQL Server™ 2000 timestamp data refers to database-specific incrementing binary numbers that indicate the relative sequence in which data modifications take place in a database; timestamp data is unrelated to both chronological time and calendar date.

A uniqueidentifier data type column is used to detect conflicts for this replication type; timestamp data is no longer used for conflict detection. For information about upgrade issues associated with this change, see Replication and Upgrading.

The literal values for a timestamp column are replicated, but the data type for the replicated values is changed to binary (8) on the Subscriber.

For merge replication and queued updating Subscriber (including immediate updating with queued updating as a failover) articles containing a timestamp column, the timestamp column is replicated, but the literal timestamp values are not. The timestamp values are regenerated at initial synchronization time when the rows are applied at the Subscriber. This allows timestamps to be used by client applications at the Subscriber for functions such as optimistic locking. In those cases, the ODBC driver, OLE DB provider, DB-Library cursor, or server cursor used by the application to implement optimistic locking compares the timestamp value of the row being updated with the current value of the original row. If the timestamp values are different, indicating the row has changed, the application can take appropriate action (rolling back the transaction, rereading the data, and so on).

The processing of timestamp data has implications for the detection of conflicts. For a conflict to occur with row-level tracking, the same row must be updated at both replicas. For a conflict to occur with column-level tracking, the same column within the same row must be updated at both replicas. Because timestamp values change whenever a row is updated, the distinction between row-level and column-level tracking would disappear with the presence of a timestamp column, unless special measures were taken. With column tracking turned on, every time updates were made at both locations, even to different columns within the rows, both timestamp values would change, and a column-level conflict would be flagged. Effectively, column-level tracking would always work the same as row-level tracking, and no merging of data updated in different columns could take place.

Merge replication tracking resolves this problem by ignoring timestamp values The queued updating Subscribers option for transactional replication uses only row-level tracking to detect conflicts, so this is not an issue. For example, suppose a merge replication table contains four columns: a uniqueidentifier column, an integer column, a character column, and a timestamp column. The value for the integer column of row 1 on the Publisher is updated, and the value for the character column of row 1 is changed on the Subscriber. When column-level tracking is turned on, the data merges without a conflict. If the Merge Agent did not ignore the timestamp values with column level tracking turned on (with row-level tracking, a conflict would have been detected in any case), this non-conflicting update would have been flagged as a conflict, and the data would not have been merged correctly. Therefore, the Merge Agent does not compare the timestamp columns and does not take any action if their values changed.