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

Share this Post

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.

Given this table and sample data:

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

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

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

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

Here is why we do this:

Assuming the table above contains these records:

Now user runs the following:

The commands posted in the distribution database will be:

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

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


Share this Post

About: ReplTalk


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

  1. what will happen if run below statement?

    UPDATE myTable SET RGCOL = RGCOL

    will it issue sp_MSupd_dbomyTable proc to pass the command?

  2. Another question – I heard that there's a switch in one of the commands, possibly added in a SQL Server 2005 SP, that will convert it back to an update. If so, what is it?  Thanks.

Leave a Reply to mbourgon Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.