Manage Identity Columns

Article
03/07/2017

This topic describes how to manage identity columns in SQL Server 2014 by using SQL Server Management Studio or Transact-SQL. 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. For information about the identity range management options provided by replication, see Replicate Identity Columns.

Before You Begin

Recommendations

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 Publish Data and Database Objects.
To create an automatically incrementing number that can be used in multiple tables or that can be called from applications without referencing any table, see Sequence Numbers.

Using SQL Server Management Studio

Specify an identity column management option on the Properties tab of the Article Properties -<Article> dialog box of the New Publication Wizard. For more information about using this wizard, see Create a Publication. In the New Publication Wizard:

If you select Merge publication or Transactional publication with updating subscriptions on the Publication Type page, select automatic or manual identity range management (automatic, the default, is recommended). After the table is published, the property cannot be modified, but other related properties can be modified.
If you select other publication types, identity range management should be set to manual.

Modify identity ranges and thresholds on the Properties tab of the Article Properties -<Article>, which is available in the Publication Properties - <Publication> dialog box. For more information about accessing this dialog box, see View and Modify Publication Properties.

To specify an identity column management option

If the Publisher is running a version of SQL Server prior to SQL Server 2005, on the Publication Type page of the New Publication Wizard, select Merge publication or Transactional publication with updating subscriptions.
On the Articles page, select a table with an identity column.
Click Article Properties, and then click Set Properties of Highlighted Table Article.
On the Properties tab of the Article Properties - <Article> dialog box, in the Identity Range Management section, set the Automatically manage identity ranges property to Automatic or Manual (for Publishers running SQL Server 2005 or later), or True or False (for Publishers running a version of SQL Server prior to SQL Server 2005).

If you selected Automatic or True in step 4, enter values for the options in the following table. For more information on how these settings are used, see the "Assigning Identity Ranges" section of Replicate Identity Columns.

Option	Value	Description
Publisher range size	Integer value for range size (for example, 20000).	See the "Assigning Identity Ranges" section of Replicate Identity Columns.
Subscriber range size	Integer value for range size (for example, 10000).	See the "Assigning Identity Ranges" section of Replicate Identity Columns.
Range threshold percentage	Integer value for percent threshold (for example, 90 is equivalent to 90 percent).	Percent of total identity values used at a node before a new identity range is assigned. Note: This value must be specified, but it is only used by: Subscribers using queued updating subscriptions; and Subscribers to merge publications running SQL Server Compact or previous versions of other SQL Server editions. For more information, see the "Assigning Identity Ranges" section of Replicate Identity Columns.
Next range starting value	Integer value. Read-only.	The value at which the next range will start. For example, if the current range is 5001-6000, this value will be 6001.
Maximum identity value	Integer value. Read-only.	The largest value for the identity column. Determined by the base data type of the column.
Increment	Integer value. Read-only.	The amount by which the number in the identity column should increase or decrease for each insert: typically set to 1.

Click OK.

To modify identity ranges and thresholds after a table is published

On the Articles page of the Publication Properties - <Publication> dialog box, select a table with an identity column.
Click Article Properties, and then click Set Properties of Highlighted Table Article.
On the Properties tab of the Article Properties - <Article> dialog box, in the Identity Range Management section, enter values for one or more of the following properties: Publisher range size, Subscriber range size, and Range threshold percentage.
Click OK.
Click OK on the Publication Properties - <Publication> dialog box.

Using Transact-SQL

You can use replication stored procedures to specify identity range management options when an article is created.

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 Define an Article.

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 Define an Article.
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 Replicate 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 Replicate Identity Columns. For more information about defining articles, see Define an Article.

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 Define an Article.
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 Replicate 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.

Share via