SQL Server Transaction Replication Bounded Update (or why my UPDATE was transformed into an DELETE/INSERT pair)

By José Moreira B. Neto
Microsoft SQL Server Escalation Services

 

Bounded Update is the term used to describe certain types of UPDATE statements from the publisher that will replicate as DELETE/INSERT pairs on the subscriber. We perform a bounded update for every set based update that changes a column that is part of a unique index or constraint. In other words, if an UPDATE statement touches more than one row and modifies a column that is has any UNIQUE constraints, the UPDATE statement is sent to the subscriber as a DELETE/INSERT pair

 

Example:

 

Given this table and sample data:

 

CREATE TABLE dbo.myTable

      (

      PKCol int NOT NULL PRIMARY KEY CLUSTERED,

      UQCol int NULL UNIQUE NONCLUSTERED,

      RGCol int NULL

      )  ON [PRIMARY]

GO

 

INSERT myTable values (1,1,1)

INSERT myTable values (2,2,1)

 

The update below modifies a non-unique column in multiple rows. Log Reader creates a single UPDATE operation for each row:

 

UPDATE myTable SET RGCOL = 2

--{CALL [sp_MSupd_dbomyTable] (,,2,1,0x04)}

--{CALL [sp_MSupd_dbomyTable] (,,2,2,0x04)}

 

If you modify a non-unique column in a single row, Log Reader also creates a single UPDATE:

 

UPDATE MyTable SET RGCOL = 3 where PKCol = 1

--{CALL [sp_MSupd_dbomyTable] (,,3,1,0x04)

 

However if you modify a unique column in multiple rows, the Log Reader will generate DELETE / INSERT pairs:

 

UPDATE MyTable SET  UQCol = UQCol + 1

--{CALL [sp_MSdel_dbomyTable] (1)}    

--{CALL [sp_MSdel_dbomyTable] (2)}    

--{CALL [sp_MSins_dbomyTable] (1,2,3)}

--{CALL [sp_MSins_dbomyTable] (2,3,2)}

 

Modifying a unique column in a single row generates a single UPDATE operation.

 

UPDATE MyTable SET  UQCol = UQCol + 1 where PKcol = 2

--{CALL [sp_MSupd_dbomyTable] (,4,,2,0x02)}

 

Here is why we do this:

 

Assuming the table above contains these records:

 

PKCol       UQCol       RGCol

----------- ----------- ----------

1           3           3

2           4           2

 

Now user runs the following:

 

UPDATE MyTable SET  UQCol = UQCol + 1

 

The commands posted in the distribution database will be:

 

{CALL [sp_MSdel_dbomyTable] (1)}   

{CALL [sp_MSdel_dbomyTable] (2)}   

{CALL [sp_MSins_dbomyTable] (1,4,3)}

{CALL [sp_MSins_dbomyTable] (2,5,2)}

 

If we would update directly (not using the delete/insert pair), it would be the same as using the commands on the subscriber:

 

UPDATE MyTable SET UQCol = 4

UPDATE MyTable SET UQCol = 5

 

In that case, the statements fail since UQCol = 4 ad UQCol = 5 exist on the subscriber.

 

Violation of UNIQUE KEY constraint 'UQ__myTable__A93B77A34D94879B'. Cannot insert duplicate key in object 'dbo.myTable'. The duplicate key value is (4).

 

Violation of UNIQUE KEY constraint 'UQ__myTable__A93B77A34D94879B'. Cannot insert duplicate key in object 'dbo.myTable'. The duplicate key value is (5).

 

Deleting the row and inserting it back (DELETE/INSERT pair) is the correct way for the SQL Replication to perform these types of operation.