Share via

  • Article

How to: Manage Identity Columns (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 How to: Create a Publication and Define Articles (SQL Server Management Studio). 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 How to: View and Modify Publication and Article Properties (SQL Server Management Studio).

To specify an identity column management option

  1. 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.

  2. On the Articles page, select a table with an identity column.

  3. Click Article Properties, and then click Set Properties of Highlighted Table Article.

  4. 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).

  5. 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 Replicating Identity Columns.

    Option

    Value

    Description

    Publisher range size

    Integer value for range size (for example, 20000).

    See the "Assigning Identity Ranges" section of Replicating Identity Columns.

    Subscriber range size

    Integer value for range size (for example, 10000).

    See the "Assigning Identity Ranges" section of Replicating 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.

    NoteNote
    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 3.5 SP1 or previous versions of other SQL Server editions. For more information, see the "Assigning Identity Ranges" section of Replicating 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.

  6. Click OK.

To modify identity ranges and thresholds after a table is published

  1. On the Articles page of the Publication Properties - <Publication> dialog box, select a table with an identity column.

  2. Click Article Properties, and then click Set Properties of Highlighted Table Article.

  3. 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.

  4. Click OK.

  5. Click OK on the Publication Properties - <Publication> dialog box.