This documentation is archived and is not being maintained.

Using Row-Level and Column-Level Tracking

Microsoft SQL Server 2005 Compact Edition (SQL Server Compact Edition) supports both row-level and column-level tracking. You can set the tracking type for each article by using the Publication Wizard or stored procedures.

Row-level and column-level tracking determines how data is transferred and how conflicts are detected during synchronization.

Column-level tracking reduces the amount of information that must be sent to the Publisher during synchronization, but row-level tracking requires less tracking overhead because more storage is required to track the changes.

When synchronizing with a SQL Server 2000 Publisher, the whole row is sent to the Publisher, even if only one column is changed.

Column-level tracking can also reduce the number of conflicts when different users are changing the same data. 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.

Schema changes are always processed before data synchronization occurs.

For more information about tracking and merge replication, see "Merge Replication Conflict Detection and Resolution" in the SQL Server Books Online.

When you use the Publication Wizard to create a publication, you enable tracking when you add the articles. Both row-level and column-level tracking are configured at the same time.

Column-level tracking is the default tracking for all articles unless otherwise specified.

The sp_addmergearticle stored procedure lets you specify the tracking type when you add an article to a publication. Set the @column_tracking parameter of the sp_addmergearticle stored procedure to 'true' for column-level tracking or to 'false' for row-level tracking.

If you are changing the properties of an existing inactive merge article in a publication, set the @property parameter of the sp_changemergearticle stored procedure to 'column_tracking', and then set the @value parameter to 'true' for column-level tracking or to 'false' for row-level tracking.

If these properties are changed after the publication has active subscriptions, the current snapshot will become obsolete and existing subscriptions will be marked for reinitialization.