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:

CREATETABLE dbo.myTable

(

PKCol intNOTNULLPRIMARYKEYCLUSTERED,

UQCol intNULLUNIQUENONCLUSTERED,

RGCol intNULL

)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.