SQL Server – Transaction Replication Ignores DML That Does Not Change Data

sql servertransactional-replication

I have a transactional replication setup between the two servers and I noticed that if a run a statement similar to this:

UPDATE mytable
SET mycolumn = mycolumn

the replication somehow knows to ignore this transaction and it does not get applied on the subscriber. I have confirmed it by running SQL Profiler and also by adding TIMESTAMP column to my subscriber table (it does not change). I suspect there is some sort of mechanism, which enables this kind of "smart" behavior and I was wondering if anybody could shed some light on it.

Thank you!

Best Answer

Tranactional Replication works by reading the transaction log.

This kind of "non updating update" often won't generate any transaction log records.

See Paul White's article The Impact of Non-Updating Updates for more about this.

Related Question