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:
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
Advertisements

4 Responses to “TimeStamp datatype in SQL Server”

  1. Anonymous Says:

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

  2. Anonymous Says:

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

  3. Ritesh Shah Says:

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

  4. Ritesh Shah Says:

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


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: