Why is primary key essential for transactional replication.

• Talha

  Old Hand

  Points: 306

  More actions

  August 5, 2011 at 1:05 am

  #245770

  Hi All,

  I am trying to find a concrete reason for the fact that Primary key is required for setting up replication. I have searched BOL and opther forums but every where I could find only one liner that Primary Key is needed for replication.

  Can anybody help me understand the relationship between a primary key and replication.

• Suresh B.

  SSC-Insane

  Points: 22986

  More actions

  August 5, 2011 at 2:59 am

  #1364158

  The reason is in the subscriber, rows are updated/deleted one-by-one using primary key.

  For example:

  If you delete 100 rows in the publisher using a single DELETE statement, in the subscriber 100 DELETE statements would be executed.

  -- on publisher

  DELETE FROM dbo.tbAddress WHERE City = 'LONDON'

  -- on subscriber

  DELETE FROM dbo.tbAddress WHERE pk = @pk