Replication and Primary Key IDENTITY column? | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Replication and Primary Key IDENTITY column?

Hi ; I want to know about any kind of relation between Primary Key IDENTITY Column of a table and replication. Is there any requirement for replication for the table which should have primary key Identity incremented by 2? I found in my databases which I work all the tables have Primary Key identity incremented by 2 and it was told to me that it is for future purpose if we need to implement replication. But, I don’t think it is the requirement for replication. Please, clear to me. Thanks. Essa, M. Mughal
Software Engineer
Canada
You need to use NOT FOR REPLICATION option for these IDENTITY columns when the Replication is involved. If the publishing table has an identity field, that field must be removed on the subscribing tables, though you can leave the identity fields on the publishing table. The primary reason to remove the identify field is that the replication process tries to write the original value to the table and gets an error saying that an attempt was made to insert a value into an identity field. Another reason is that an identity field could possibly assign a different value than the original source, which would violate any foreign key constraints to other tables. To handle identity fields on a subscribing table, create an extra column whose name does not appear in the publishing tables. This scenario works well when a table subscribes from multiple publishers. Each publisher can have the same values, so you need another primary key to give unique key values. Refer to thishttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6136 thread for interesting information. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Can you please explain this further?
I don’t understand your question, what is your doubt about above explanation in identify & replication blurb!
Best to open another thread by referring this post for a followup [:)].
]]>