Updating identity column sql server Virtual adult living free
I'll save the discussion of peer-to-peer publications for another tip.
Options to manage identity seeds for replicated tables If you must use updateable subscriptions, you need to define identity ranges on publisher and subscriber servers to avoid the creation of duplicate primary keys.
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.
Identity columns are good candidates for a table's primary key because they're unique for each row and cannot be updated without deleting and re-creating the row.
SQL Server replication scenarios To make this tip easier to follow, let's imagine we're trying to replicate a table with the following schema: Click here to view schema.
With this scenario, we still presume that data is replicated in one direction, from publishers to subscriber(s), and no direct data changes occur on the subscriber.
First, with such architecture, an attempt to add a replicated record to the subscriber will fail.In this case, we must use IDENTITY, NOT FOR REPLICATION option both on publisher(s) and on subscribers.Once again, please do not read this tip as a recommendation to use updateable subscriptions when they're not necessary.Normally, identity columns have INT or BIGINT data types, although you could use other numeric data types as well.By default, SQL Server seeds such columns at 1 and increments by 1, but you can change to the seed and increment of your liking.