TimeStamp datatype in SQL Server

Monday, May 3, 2010 |

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:

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

2 comments:

Anonymous said...

how is it possible that Restrict to user save any thing on desktop ?
i have 2003 Enterprise Edition... please tell me...

Ritesh Shah said...

I am sorry my friend. this is SQL Server blog, you should ask this question to Windows Server expert.