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 ]
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”
After an update to the same record, we will check the version (timestamp) of the same record:
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
I Learnt Today 