What do I gain by adding a timestamp column called recordversion to a table in ms-sql?

Question

What do I gain by adding a timestamp column called recordversion to a table in ms-sql?

Answer 1

Nothing that I'm aware of, or that Google seems to find quickly.

You con't get anything inherent by using that name for a column. Sure, you can create a column and do the record versioning as described in the next response, but there's nothing special about the column name. You could call the column anything you want and do versioning, and you could call any column RecordVersion and nothing special would happen.

Answer 2

You can use that column to make sure your users don't overwrite data from another user.

Lets say user A pulls up record 1 and at the same time user B pulls up record 1. User A edits the record and saves it. 5 minutes later, User B edits the record - but doesn't know about user A's changes. When he saves his changes, you use the recordversion column in your update where clause which will prevent User B from overwriting what User A did. You could detect this invalid condition and throw some kind of data out of date error.

Answer 3

Also keep in mind if you want to keep track of your data, it's a good idea to add these four columns to every table:

CreatedBy(varchar) | CreatedOn(date) | ModifiedBy(varchar) | ModifiedOn(date)

While it doesn't give you full history, it lets you know who and when created an entry, and who and when last modified it. Those 4 columns creates pretty powerfull tracking abalities without any serious overhead to your DB.

Obviously you could create a full blown logging system that tracks every change and gives you full blown history, but that's not the solution for the issue I think you are proposing.

Answer 4

Timestamp is mainly used for replication. I have also used it successfully to determine if the data has been updated since the last feed to the client (when I needed to send a delta feed) and thus pick out only the records which have changed since then. This does require having another table that stores the values of the timestamp (in a varbinary field) at the time you run the report so you can use it compare on the next run.

If you think that timestamp is recording the date or time of the last update, it does not do that, you would need dateTime fields and constraints (To get the orginal datetime)and triggers (to update) to store that information.