Many times I need to move the data of a large table (let's call it source) to a clone of it (let's call it target). Due to the large size, instead of just deleting/inserting all, I prefer to upsert.
For easiness, let's assume an int PK col named "id".
Until now, in order to do this, I've used the datetime field dbupddate, existent on both tables, which holds the most recent time the row was inserted/updated. This is done by using a trigger which, for any insert/updates, sets dbupddate to getdate().
Thus, my run-of-the-mill upsert code until now looks something like:
update t set (col1=s.col1,col2=s.col2 etc)
from source s
inner join target t on s.id=t.id and s.dbupddate>t.dbupddate
insert target
select * from source s
where not exists (select 1 from target t where t.id=s.id)
Recently I stumbled on rowversion. I have read and understood up to an extent its function, but I'd like to know practically what benefits/drawbacks there are in case I change dbupddate to rowversion instead of datetime.
