Tuesday, May 08, 2012

Timestamp and RowVersion Usage and Behaviour in SQL SERVER

I have gone through a different experience today. On of my coleague ask me to get latest users data from a table. when i looked into table structure it does not have any date or time datatype. When i raise the question that if he wants me to return data on the basis of Id he replies no i have defined a timestamp column use that by using date time conversion. When i tried there is an error message that database engine is unable to convert timestamp into date time. On most of the times people think that timestamp is some type of date and time data type and can return date. 

In this blog we will look into timestamp datatype, its usage and what is the difference between timestamp and rowversion. From microsoft fourms i have got following details.

Timestamp

Defination
Timestamp is a data type that exposes automatically generated, unique binary numbers within a database. Timestamp is generally used as a mechanism for version-stamping table rows like replication. 

Timestamp as DateTime;
The timestamp data type is just an incrementing number and does not preserve a date or a time. To record a date or time, use a datetime data type.
------------------------ Sample Code ---------------------------------------------
Declare @tbl table (id int identity(1,1),date datetime,stamp timestamp)
Insert into @tbl values (getdate(),default)
select * from @tbl


Update @tbl set date = getdate() + 1
select * from @tbl
----------------------------------------------------------------------------------------

For detail about each topic in this post go to following link

No comments: