Updating identity column sql server matt leblanc not dating andrea anders
This is because you cannot explicitly specify the value of an identity column unless you issue SET IDENTITY_INSERT ON statement.Second, if you enable the IDENTITY INSERT and add a record with identity value of 200,000,001, you'll effectively reset the identity seed on the subscriber.The typical developer mentality is to use this (or any other) option because it's available.This is why it's crucial to separate developer and DBA duties.
This functionality is implemented through replication triggers and what is referred to as the two-phase commit.
First, let me offer a little background about identity columns to help you understand why they're different from any other column with a numeric data type.
Identity columns have monotonously increasing numeric values that SQL Server assigns to each row automatically when the row is created.
The next record you add directly (not through replication) to the subscriber will have the identity value of 200,000,002, which overlaps with the range of values we assigned to a publisher.
Fortunately, we can use IDENTITY, NOT FOR REPLICATION option when defining the column on the subscriber.