Updating an identity column sql server

03-Jul-2017 03:17

The identity values are assigned from a pool at the Publisher.

When you add an article with an identity column to a publication in the New Publication Wizard or by using For example, you could specify 10000 for @identity_range and 500000 for @pub_identity_range.

The identity values are assigned from a pool at the Distributor.

The pool size is based on the size of the data type and the increment used for the identity column.

The secondary range is equal in size to the primary range; when the primary range is exhausted, the secondary range is used, and the Merge Agent assigns a new range to the Subscriber.When you assign an IDENTITY property to a column, Microsoft SQL Server automatically generates sequential numbers for new rows inserted in the table containing the identity column. Because identity columns might be included as a part of the primary key, it is important to avoid duplicate values in the identity columns.To use identity columns in a replication topology that has updates at more than one node, each node in the replication topology must use a different range of identity values, so that duplicates do not occur.The new range becomes the secondary range, and the process continues as the Subscriber uses identity values.

Subscribers running SQL Server Compact or previous versions of SQL Server are assigned only the primary range; assignment of new ranges is controlled by the @threshold parameter.Merge replication and transactional replication use different methods for assigning ranges; these methods are described in this section.There are two types of ranges to take into account when replicating identity columns: the ranges assigned to the Publisher and Subscribers, and the range of the data type in the column.Specifying a higher threshold results in smaller gaps, but the system is less fault-tolerant: if the Merge Agent cannot run for some reason, a Subscriber could more easily run out of identities.

ID is an identity column. Updating can be much more efficient then delete insert. Through the UI in SQL Server 2005 manager, change the column remove the.… continue reading »

Read more