AutoIdentityRange Property

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

The AutoIdentityRange property specifies whether to automatically assign an identity range to a table that has an identity column and is an article in a publication that allows queued updates. The identity range is assigned at both the Publisher and Subscriber.

AutoIdentityRange [=value]


An expression that evaluates to an object in the Applies To list




Read/write when creating an article. Read-only when referencing an existing article.

HRESULT GetAutoIdentityRange(LPBOOL pRetVal);
HRESULT SetAutoIdentityRange(BOOL NewValue);

Assigning an automatic identity range helps avoid conflicts in identity column values when data is inserted at the Subscriber in merge replication, or in transactional replication that allows queued updates. The identity range specifies the maximum number of new rows that can be inserted into an identity column in a table at a Publisher or Subscriber before a new identity range must be allocated.

Use the PublisherIdentityRangeSize and SubscriberIdentityRangeSize properties to set identity range sizes. Use the IdentityRangeThreshold property to control when a new identity range is allocated. When the number of new rows reaches the percentage specified by IdentityRangeTreshold, the new range is allocated.

When the AutoIdentityRange property is set to TRUE, identity ranges can be assigned to an identity column that contains unique values within a table. Unique values in an identity column are assigned automatically when new rows are inserted into the target table at the Publisher or Subscriber.

To configure the use of automatic identity ranges
  1. Use the HasIdentityColumn property to determine whether a table has an identity column. If you are using AutoIdentityRange with a TransArticle2 object, use the AllowedQueuedTransactions property to determine whether the publication allows queued updates.

  2. If HasIdentityColumn returns TRUE (and if AllowedQueuedTransactions returns TRUE for a transactional publication), set AutoIdentityRange to TRUE.

  3. Use the PublisherIdentityRangeSize property to set the identity range size at the Publisher.

  4. Use the SubscriberIdentityRangeSize property to set the identity range size at the Subscriber.

  5. Use the IdentityRangeThreshold property to specify (as a percentage of a Publisher's or Subscriber's range size) when a new identity range is allocated.

    If an application calls AutoIdentityRange on an instance of Microsoft SQL Server version 7.0, the constant, SQLDMO_E_SQL80ONLY, and the message "This property or method requires Microsoft SQL Server 2000 or later" are returned.

Community Additions