Friday, October 01, 2004

About TimeStamp datatype of SQL Server ...

SQL Server has a less known datatype called “TimeStamp”. But I wasn't able to find any article about it on the web. So I thought I would try a sample myself and write about it.

TimeStamp is actually used for record versioning in a table. When you Insert (or) Update a record in a table with a TimeStamp field, that field gets updated automatically.

Lets create a sample table to understand the usage of TimeStamp field.

Create table TimeStampExample
(
RunningNumber int identity,
LastName varchar(30),
tStamp timestamp
)

The above table “TimeStampExample” has been created with a TimeStamp field (tStamp). By the way its not mandatory to provide a field name for timestamp columns. The below table structure is perfectly valid only.

Create table TimeStampExample
(
RunningNumber int identity,
LastName varchar(30),
timestamp -- note we haven't mentioned a field name here
)

When a record is inserted the value of tStamp gets automatically set by SQL Server. Lets see that in action.

Insert TimeStampExample (LastName) values ('RajiniKanth')

After inserting a sample record lets query the table to see the complete record with timestamp value in it.

Select * from TimeStampExample

We need to understand that TimeStamp are just binary values that are incremented with every change in the database and therefore they are unique with the database.

For better understanding lets update the record and check whether tStamp has been updated or not. Make use of the below code snippet for checking that.

Update TimeStampExample Set LastName = 'Shivaji Rao' Where LastName = 'RajiniKanth'

Just query the table now and you could see that the TimeStamp field being updated automatically. Cool isn't it :)