Using NOT FOR REPLICATION

The NOT FOR REPLICATION option is used by Microsoft® SQL Server™ 2000 replication to implement ranges of identity values in a partitioned environment. The NOT FOR REPLICATION option is especially useful in transactional or merge replication when a published table is partitioned with rows from various sites.

When a replication agent connects to a table with any login, all of the NOT FOR REPLICATION options on the table are activated. When the option is set, SQL Server 2000 maintains the original identity values on rows added by the replication agent but continues to increment the identity value on rows added by other users. When a user adds a new row to the table, the identity value is incremented in the normal way. When a replication agent replicates that row to a Subscriber, the identity value is not changed when the row is inserted in the Subscriber table.

For example, consider a table that contains rows inserted from two sources: Publisher A and Publisher B. The rows inserted at Publisher A are identified by increasing values from 1 through 1000, and those rows at Publisher B are identified by values from 1001 through 2000. If a process at Publisher A inserts a row locally into the table, SQL Server assigns the first row a value of 1, the next row a value of 2, and so forth, in automatically increasing increments. Similarly, if a process at Publisher B inserts a row locally into the table, the first row is assigned a value of 1001, the next row a value of 1002, and so forth. When rows at Publisher A are replicated to B, the identity values remain 1, 2, and so forth, but local seed values at B are not reset.

Regardless of its role in replication, the IDENTITY property does not enforce uniqueness by itself, but merely inserts the next value. Although you can provide an explicit value using SET IDENTITY INSERT, that function is not appropriate for replication because it also reseeds the value. The NOT FOR REPLICATION option was created specifically for applications using replication. For example, without this option, as soon as the first row from Publisher B (with value 1001) is propagated to Publisher A, Publisher A's next value would be 1002. The NOT FOR REPLICATION option is a way of telling SQL Server 2000 that the replication process gets a waiver when supplying an explicit value and that the local value should not be reseeded. Each Publisher using this option gets the same reseeding waiver.

Custom stored procedures that use INSERT, UPDATE, and DELETE statements with full column lists are required before replication will work with identity properties. If full column lists are not used, an error will be returned.

The following code example illustrates how to implement identities with different ranges at each Publisher:

• At Publisher A, start at 1 and increment by 1.

  CREATE TABLE authors ( COL1 INT IDENTITY (1, 1) NOT FOR REPLICATION PRIMARY KEY ) 
  

• At Publisher B, start at 1001 and increment by 1.

  CREATE TABLE authors ( COL1 INT IDENTITY (1001, 1) NOT FOR REPLICATION PRIMARY KEY ) 
  

After activating the NOT FOR REPLICATION option, connections from replication agents to Publisher A insert rows with values such as 1, 2, 3, 4. These are replicated to Publisher B without being changed (that is, 1, 2, 3, 4). Connections from replication agents at Publisher B get values 1001, 1002, 1003, 1004. Those are replicated to A without being changed. When all data is distributed or merged, both Publishers have values 1, 2, 3, 4, 1001, 1002, 1003, 1004. The next locally inserted value at Publisher A is 5. The next locally inserted value at Publisher B is 1005.

It is advisable to always use the NOT FOR REPLICATION option along with the CHECK constraint to ensure that the identity values being assigned are within the allowed range. For example:

CREATE TABLE sales 
(sale_id INT IDENTITY(100001,1) 
    NOT FOR REPLICATION
    CHECK NOT FOR REPLICATION (sale_id <= 200000), 
sales_region CHAR(2),
CONSTRAINT id_pk PRIMARY KEY (sale_id)
)

Even if someone used SET IDENTITY INSERT, all values inserted locally must obey the range. However, a replication process is still exempt from the check.

Note  If you are using transactional replication with the immediate-updating Subscribers option, do not use the IDENTITY NOT FOR REPLICATION design. Instead, create the IDENTITY property at the Publisher only, and have the Subscriber use just the base data type (for example, int). Then, the next identity value is always generated at the Publisher.