Can you figure out the output in this Scenario on replication?

Question

I have two Server A, B.  one database on A named DB1, so we replicate DB1 to DB2 on Server B.  this is an trasaction replication.

suppose all the setting in replication is in default

In DB1,i have two tables table1 and table2, they have same schema and data, the table1 is in replication, was replicated to table3 on server B.

so i will execute two different update on table1 from table2, so what we will see if you start the sql profile on Sever B?

 

columns in table1 and table2
(PartNumber primary key, CodeName nvarchar(20),TypeCode nvarchar(20))

 

1. first update

    update a set a.CodeName=b.CodeName,a.TypeCode=b.TypeCode  from table1 a
    inner join table2 on a.PartNumber=b.PartNumber

 

    in this case, will you see any operation was replicated to table3 on Server B from the sql profile?

2. Second Update

  
    update a set a.PartNumber=b.PartNumber,a.CodeName=b.CodeName,a.TypeCode=b.TypeCode  from table1 a
  inner join table2 on a.PartNumber=b.PartNumber

 

    in this case, what operations will you see were replicated to table3 on Server B from the sql profile?

Answer 1

No difference in replication point of view. If statement updated any row in table a, that change is replicated. To confirm, just run profiler on server b. 

Answer 2

hi Rimao, thanks you reply. i just found it is difference if you update the primary key.

when the primary key is updated, even the table a and table b is the same. you will can see

batch delete/insert on server B by checking the sql profile.

but if the primary key is not updated, in this case, we will see no DML on server B

this is very intersting.

thanks

kevin