Sunday, March 01, 2015

Timestamp Datatype in SQL Server

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. 

1 comment:

  1. Good post... Randheer...do you work for Microsoft by any chance?
    -Nasar

    ReplyDelete