SQL Server vector clock

Question

Is there a global sequence number in SQL Server which guarantees to increment periodically (even when system time regresses), and can be accessed as part of an insert or update operation?

Answer 1

Yes the rowversion data type, and the @@dbts function are what you're looking for.

This pattern, of marking rows using a rowversion is implemented at a lower level by the Change Tracking feature. Which adds tracking of insert/updates and deletes, and doesn't require you to add a column to your table.

Answer 2

I'm pretty sure ROWVERSION does what you want. A ROWVERSION-typed column is guaranteed to be unique within any single database, and, per the SQL documentation, it is nothing more than an incrementing number. If you just save MAX(ROWVERSION) each time you've finished updated your data, you can find updated or inserted rows in your next update pass by looking fo0r ROWVERSIONs that are bigger than the saved MAX(). Note that you cannot catch deletes in this fashion!

Another approach is to use LineageId's and triggers. I'm happy to explain that approach if it would help, but I think ROWVERSION is a simpler solution.