Using IDENTITY Values with Replication

When you assign an IDENTITY property to a column, the system automatically generates sequential incrementing numbers for new rows inserted into a table. Because identity values are usually unique, an identity column is frequently defined as a primary key.

In replication topologies, where a publication contains an identity column and new rows can be inserted at Subscribers, additional configuration may be necessary to ensure that no duplicate identity values or constraint violations occur.

To illustrate managing identity values with replicas, suppose three rows of data from Publisher A, containing the identity values 1, 2, and 3,

 are replicated to Subscriber A and Subscriber A allows inserts. If two new rows in the same article are inserted, one at Publisher A and one at Subscriber A, and no additional measures are taken by the replication agents, both rows are assigned an identity value of 4. An attempt is made by the replication agents to copy the new rows between the Subscriber and Publisher. If successful, two different rows with an identity value of 4 will exist on each replica. As a result,  each published article will contain multiple rows with the same identity values. If the identity column was defined as a primary key, or with a unique constraint, the data will not replicate.

Replication provides several options to ensure the same identity values are not assigned to rows inserted at different replicas, or that a primary key constraint violation does not occur.