TimeStamp datatype in SQL Server

Well TimeStamp datatype is one of the old datatype in SQL Server and going to deprecated in newer version so it is not advisable to use it. The main intention to write this article is, recently I have see few people discussing TimeStamp datatype with misconception in one of the forum.

I wonder people got this datatype wrong by understanding it as a real data or time datatype. Actually TimeStamp datatype has nothing to do with Date or time in SQL Server.

As per MSDN, TimeStamp is

“timestamp is a  data type that exposes automatically generated binary numbers, which are guaranteed to be unique within a database. timestamp is used typically as a mechanism for version-stamping table rows. The storage size is 8 bytes.”

Let us see one small example which proves above statement.

create table TimeStampTesting

(

Name varchar(10),

TS TimeStamp

)

Insert Into TimeStampTesting(Name)

Select 'Ritesh' union all

Select 'Rajan' union all

Select 'Bihag'

GO

--since we are making order by on TS

--Bihag should be first as that record was inserted last

Select * from TimeStampTesting order by TS desc

Go

Update TimeStampTesting set Name='Rajan S.' where Name='Rajan'

GO

--if you observe, this time Bihag wouldn't first

--but Rajan S. would be the first as it updated last

--so TS is a binary unique number which updates itself automatically

--for new upate and/or insert

Select * from TimeStampTesting order by TS desc

Go

BTW, now a day, you should use RowVersion datatype rather than TimeStamp as I told you above too that TimeStamp will be deprecated and RowVersion is synonyms for TimeStamp. For more information, look at the below URL:

http://msdn.microsoft.com/en-us/library/ms177566.aspx

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com