Using TimeStamp columns to keep track of database record versions

2011-10-07
//
http://pagead2.googlesyndication.com/pagead/show_ads.js

Timestamp is a value that is incremented by the database whenever an insert or update operation is performed.

Even though the name Timestamp could be a bit misleading, this value has no relevance to a clock related time. This only shows a linear progression of time.

For an example , it is something like your database saying it has been two update or insert commands since your last visit.

You can see this value by referring to @@DBTS  [ select @@DBTS ]

Database Timestamp Value
Database Timestamp Value


How can this be helpful at all?

Well, in simple terms, this helps to keep track of versions of records.

For an example, assume a scenario where you have to fetch a record from a table, manipulate the data and write it back.

What if the record gets changed (from another database call) after you fetched data? You will manipulate the old data and update the record without knowing somebody has updated the record in between your fetching and updating commands.

How can timestamp help?

You can add a column to your table (you can call it “Version”) and set its data type to TimeStamp. Then whenever you update or insert a record to this table, this column will record the database timestamp after that transaction.

So before writing your manipulated data, you can check if the timestamp value remains the same as what you read at the beginning of the transaction.

Following example demonstrates how timestamp can be used to monitor versions:

The Person table used for this example has a column called “Version” which is of type Timestamp.

First simply query the Version column of the table for the person called “Robert”

Timestamp before update
Timestamp before update


After an update to the same record, we will check the version (timestamp) of the same record:

Timestamp after update
Timestamp after update


As you can see, the timestamp value for the record has been automatically updated.

Note: Timestamp columns are automatically updated by the database engine so you do not have to specify value when either inserting or updating a row of a table which has a timestamp (version) column.

So to insert a record to a table with a timestamp column simply omit the timestamp column from your insert statement.

i.e. – Person table has following columns [Id, Given_Name, Family_Name, Age, Version]

Insert statement would be:

Insert into Person(Id, Given_Name, Family_Name, Age)
Values(001, "Robert", "Nox", 78);

The database will take care of the timestamp (version) column.

//
http://pagead2.googlesyndication.com/pagead/show_ads.js

Was this post helpful to you? How can I improve? – Your comment is highly appreciated!

Cassian Menol Razeek

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: