How to: Manage Identity Columns (Replication Transact-SQL Programming)
When Subscriber inserts are replicated back to the Publisher, identity columns must be managed to avoid assignment of the same identity value at both the Subscriber and Publisher. Replication can manage identity ranges automatically or you can choose to manually handle identity range management. You can use replication stored procedures to specify identity range management options when an article is created. For information on the identity range management options provided by replication, see Replicating Identity Columns.
Note
When publishing a table in more than one publication, you must specify the same identity range management options for both publications. For more information, see "Publishing Tables in More Than One Publication" in Publishing Data and Database Objects.
To enable automatic identity range management when defining articles for a transactional publication
At the Publisher on the publication database, execute sp_addarticle. If the source table being published has an identity column, specify a value of auto for @identityrangemanagementoption, the range of identity values assigned to the Publisher for @pub_identity_range, the range of identity values assigned to each Subscribers for @identity_range, and the percent of total identity values used before a new identity range is assigned for @threshold. For more information about defining articles, see How to: Define an Article (Replication Transact-SQL Programming).
Note
Ensure that the data type of the identity column is large enough to support the total range of identities being assigned to all Subscribers.
To disable automatic identity range management when defining articles for a transactional publication
At the Publisher on the publication database, execute sp_addarticle. Specify a value of manual for @identityrangemanagementoption. For more information about defining articles, see How to: Define an Article (Replication Transact-SQL Programming).
Assign ranges to identity article columns at the Subscriber to avoid generating conflicts for updating Subscribers. For more information, see the section on assigning ranges for manual identity range management in the topic Replicating Identity Columns.
To enable automatic identity range management when defining articles for a merge publication
At the Publisher on the publication database, execute sp_addmergearticle. If the source table being published has an identity column, specify a value of auto for @identityrangemanagementoption, the range of identity values assigned to a server subscription for @pub_identity_range, the range of identity values assigned to the Publisher and each client subscription for @identity_range, and the percent of total identity values used before a new identity range is assigned for @threshold. For more information on when new identity ranges are assigned, see Assigning Identity Ranges in the topic Replicating Identity Columns. For more information about defining articles, see How to: Define an Article (Replication Transact-SQL Programming).
Note
Ensure that the data type of the identity column is large enough to support the total range of identities being assigned to all Subscribers, particularly for Subscribers with server subscriptions.
To disable automatic identity range management when defining articles for a merge publication
At the Publisher on the publication database, execute sp_addmergearticle. Specify one of the following values for @identityrangemanagementoption:
manual - Identity ranges must be assigned manually for updating Subscribers.
none - Identity columns at the Publisher will not be defined as identity columns at the Subscriber.
For more information about defining articles, see How to: Define an Article (Replication Transact-SQL Programming).
Assign ranges to identity article columns at the Subscriber to avoid generating conflicts for updating Subscribers.
To change automatic identity range management settings for an existing article in a snapshot or transactional publication
At the Publisher on the publication database, execute sp_helparticle and note the value of identityrangemanagementoption in the result set. If this value is 0, automatic identity range management is not enabled.
If the value of identityrangemanagementoption in the result set is 1, change the settings as follows:
To change the assigned identity ranges, execute sp_changearticle at the Publisher on the publication database. Specify a value of identity_range or pub_identity_range for @property and the new range value for @value.
To change the threshold at which new ranges are assigned, execute sp_changearticle at the Publisher on the publication database. Specify a value of threshold for @property and the new threshold value for @value.
To change automatic identity range management settings for an existing article in a merge publication
At the Publisher on the publication database, execute sp_helpmergearticle and note the value of identity_support in the result set. If this value is 0, automatic identity range management is not enabled.
If the value of identity_support in the result set is 1, change the settings as follows:
To change the assigned identity ranges, execute sp_changemergearticle at the Publisher on the publication database. Specify a value of identity_range or pub_identity_range for @property and the new range value for @value.
To change the threshold at which new ranges are assigned, execute sp_changemergearticle at the Publisher on the publication database. Specify a value of threshold for @property and the new threshold value for @value. For more information on when new identity ranges are assigned, see Assigning Identity Ranges in the topic Replicating Identity Columns.
To disable automatic identity range management, execute sp_changemergearticle at the Publisher on the publication database. Specify a value of identityrangemanagementoption for @property and either manual or none for @value.