More DBA job interview questions and answers at
http://dba.fyicenter.com/Interview-Questions/
(Continued from previous question...)
How to replicate col = col + 1 in Sybase ?
Firstly. While the rule that you never update a primary key may be a philosophical choice in a non-replicated system, it is an architectural requirement of a replicated system.
If you use simple data replication, and your primary table is:
id
---
1
2
3
and you issue a:
update table set id=id+1
Rep server will do this in the replicate:
begin tran
update table set id=2 where id=1
update table set id=3 where id=2
update table set id=4 where id=3
commit tran
Hands up all who can see a bit of a problem with this! Remember, repserver doesn't replicate statements, it replicates the results of statements.
One way to perform this update is to build a stored procedure on both sides that executes the necessary update and replicate the stored procedure call
(Continued on next question...)