SQL Server - how to generate unique sequential number on update

Question

I have column in my table, say updateStamp. I'd like to get an approach to update that field with a new sequential number upon row update.

The database has lot of traffic, mostly read, but multiple concurrent updates could also happen in batches. Therefore the solution should cause minimal locks.

Reason for this requirement is that I need to have a solution for clients to iterate over the table forwards and if a row is updated - it should come up on the result set again.

So, query would then be like

SELECT * 
FROM mytable 
WHERE updateStamp > @lastReturnedUpdateStamp 
ORDER BY updateStamp

Unfortunately timestamps do not work here because multiple updates could happen at same time.

Answer 1

The timestamp (deprecated) or rowversion (current) data type is the only one I'm aware of that is updated on every write operation on the row.

It's not a time stamp per se - it doesn't store date, time in hours, seconds etc. - it's really more of a RowVersion (hence the name change) - a unique, ever-increasing number (binary) on the row.

It's typically used to check for any modifications between the time you have read the row, and the time you're going to update it.

Since it's not really a date/time information, you will most likely have to have another column for that human-readable information. You can add a LastModified DATETIME column to your table, and with a DEFAULT GETDATE() constraint, you can insert a new value upon insertion. For keeping that up to date, you'll have to write a AFTER UPDATE trigger to update the LastModified column when any update occurs.

SQL Server 2011 (a.k.a. "Denali") will bring us SEQUENCES which would be the perfect fit in your case here - but alas, that' still at least a year from official release.....