Published:
15 Dec 2008
|
Abstract
TIMESTAMP is one of the more useful T-SQL statements in SQL Server. In this article, Abhishek describes the usage of TIMESTAMP, called ROWVERSION in SQL Server, with the help of relevant T-SQL queries and corresponding output along with a detailed description. He also focuses on whether it is related to DATETIME in SQL. He examines the usage of TIMESTAMP using CREATE TABLE, INSERT, and UPDATE commands and also outlines the benefit of it in multi-user table access. He wraps up the article by providing the differences between TIMESTAMP and UNIQUEIDENTIFIER. |
|
by Abhishek Kumar Singh
Feedback
|
Average Rating:
Views (Total / Last 10 Days):
183926/
74
|
|
|
Introduction |
I have included TIMESTAMP in this article as it first seems as
if TIMESTAMP is something related to SMALLDATETIME or DATETIME. To be frank, I
was also thinking as if I were a beginner in SQL Server. But in fact, TIMESTAMP
value does not contain any date or time related value. It is not dependent on
system date. In fact, it contains binary format string to denote a version of a
row in table. That is why it is also called ROWVERSION. Also, ROWVERSION is the
keyword in SQL Server which has the same behavior as TIMESTAMP.
|
Using TIMESTAMP in CREATE TABLE command |
Wikipedia.org defines timestamp in very clear manner, as
follows:
A timestamp is a sequence of characters, denoting the date
and/or time at which a certain event occurred.
To go ahead, let us create a table with the following
structure:
Listing 1
CREATE TABLE MyTestTable(RowID INT IDENTITY(1,1) NOT NULL,
[TimeStamp] TIMESTAMP, [SmallDateTime] SMALLDATETIME,
[DateTime] DATETIME)
OR
Listing 2
CREATE TABLE MyTestTable1(RowID INT IDENTITY(1,1) NOT NULL,
TIMESTAMP, [SmallDateTime] SMALLDATETIME,
[DateTime] DATETIME)
Notice the second statement - If we just specify data type
TIMESTAMP without a column name for it then SQL Server automatically sets its
column name as "Timestamp."
Please note that a table can have only one
TIMESTAMP column. We can have either of a TIMESTAMP or ROWVERSION column type
in table, but not both. I have used column type TIMESTAMP in the above
table CREATE command.
|
Using TIMESTAMP with INSERT command |
Insert one row in the table as given below.
Listing 3
INSERT INTO MyTestTable([SmallDateTime],[DateTime])VALUES(GETDATE(), GETDATE())
Now open the table using SQL Server Management Studio or
executing SELECT query.
Listing 4
SELECT * FROM MyTestTable
The values in the table would be something like below. (In
your result set, data/time values will be your current date of the computer.)
Listing 5
RowID TimeStamp SmallDateTime DateTime
----------- ------------------ ----------------------- -----------------------
1 <span class=Bold>0x0000000000000FA1</span> 2008-09-20 02:27:00 2008-09-20 02:26:35.700
Though we had not inserted value in TimeStamp column, it has
one value. This value is nothing but a binary formatted value. Each insert of a
row in the table will automatically set the incremented
by 1 value in the TimeStamp column.
For example, insert another row in the MyTestTable by
executing the same insert query given above. Now open the table again. It would
have added a second row with a new TIMESTAMP value which is nothing but the
value incremented by one of pervious TIMESTAMP 0x0000000000000FA1.
Listing 6
RowID TimeStamp SmallDateTime DateTime
----------- ------------------ ----------------------- -----------------------
1 0x0000000000000FA1 2008-09-20 02:27:00 2008-09-20 02:26:35.700
2 <span class=Bold>0x0000000000000FA2</span> 2008-09-20 02:39:00 2008-09-20 02:38:39.763
|
Using TIMESTAMP with UPDATE command |
Similarly, a TIMESTAMP value of row(s) would be
automatically updated if we UPDATE any value of row(s) in the table. New
TIMESTAMP value will always be the next incremented value of the largest
TIMESTAMP value exist among all rows in the table.
For example, Execute following UPDATE command on the table.
Listing 7
UPDATE MyTestTable SET SmallDateTime=0
WHERE RowID = 1
If you open the table now, the TIMESTAMP column of RowID
must have incremented by one to the largest TIMESTAMP available in the table.
Listing 8
RowID TimeStamp SmallDateTime DateTime
----------- ------------------ ----------------------- -----------------------
1 <span class=Bold>0x0000000000000FA3</span> 1900-01-01 00:00:00 2008-09-20 02:26:35.700
2 0x0000000000000FA2 2008-09-20 02:39:00 2008-09-20 02:38:39.763
|
Benefit of TIMESTAMP in multi-user access on TABLE |
Hence, TIMESTAMP/ROWVERSION provides a row-versioning
feature in the table. Row-versioning can be used to examine the changes in
table. It can also help to manage the synchronization in multiuse access. For
example, if two users, A and B, are accessing a table T at the same time. Both
are intended to make some changes in a set of rows in T by using the latest values
available in that. Both set A and B can read the required values from rows with
the TIIMESTAMP column values. Before going to update the value in specific row
in the table, if both A and B checks if the already read TIMESTAMP value still
matches the TIMESTAMP value of the same row in the table, then they can go for
update. If the TIMESTAMP value differs then it means another user has already modified
that row, so it requires reading that table (at least modified rows) again to
get the updated values. In this case, both user A and B are synchronized to
each other.
Few more operations with TIMESTAMP
In general we do not need to update a timestamp column value
manually, but if we need to do it then we can use binary and varbinary values
to update a timestamp column value.
Though you can use integer values to assign in timestamp
type variable in SQL. For example:
Listing 9
DECLARE @timest AS TIMESTAMP
SET @timest = 5
SELECT @timest
If you execute the above query this will result following output:
0x0000000000000005
Now if you try following a query to insert a row with
specific timestamp value in the above created table:
Listing 10
INSERT INTO MyTestTable([TIMESTAMP],[SmallDateTime],[DateTime])
VALUES(5,GETDATE(), GETDATE())
it will show the following error message:
Cannot insert an explicit value into a
timestamp column. Use INSERT with a column list to exclude the timestamp
column, or insert a DEFAULT into the timestamp column.
As the error message indicates, we can either remove the
timestamp column from the insert statement or set DEFAULT keyword as the value
to automatic timestamp value to be inserted.
Listing 11
INSERT INTO MyTestTable1([TIMESTAMP],[SmallDateTime],[DateTime])
VALUES(DEFAULT,GETDATE(), GETDATE())
|
TIMESTAMP vs. UNIQUEIDENTIFIER |
In SQL Server we have one more data type called
UNIQUEIDENTIFIER. This is also used frequently to track the changes in table as
well as to program some security based features. The following are the
differences between TIMESTAMP and UNIQUEIDENTIFIER.
·
Size of TIMESTAMP value is 8 bytes whereas size of UNIQUEIDENTIFIER
is 16 bytes.
·
TIMESTAMP is not based on system date or time. However, UNIQUEIDENTIFIER
value is based on the computer's MAC addresses and system date time.
·
The purpose to TIMESTAMP is to track the operation in tables on
the database level. The purpose of UNIQUEIDENTIFIER
is to have a unique value assigned to a row (maybe as primary key). It remains unique in any system in the world.
|
Conclusion |
In a more complex scenario, TIMESTAMP features are being
used in data-level security requirements, like in building digital signatures.
This mechanism helps in verifying that data or document is not modified since
at a certain point of time or change. This is used in encryption-decryption
techniques, for example RSA algorithm uses timestamp in coding.
|
|
|
User Comments
Title:
gfh
Name:
gh
Date:
2012-12-25 1:39:03 AM
Comment:
gh
|
Title:
abc
Name:
abc
Date:
2012-09-21 4:48:29 AM
Comment:
testing
|
Title:
nfl jerseys cheap
Name:
NIKE NFL jerseys
Date:
2012-07-02 10:12:14 AM
Comment:
http://www.jersey2shop.com http://www.cheapjersey2store.com http://www.jerseycaptain.com http://www.yourjerseyhome.com We are professional jerseys manufacturer from china,wholesal.cheap nike nfl jerseys, mlb jerseys, nhl jerseys,nba jerseys and shoes Cheap NFL,NBA,MLB,NHL ,heap jerseys,2012 nike nfl Jerseys,nba jersey and shorts,oklahoma city thunder jersey,official jeremy lin new york knicks jersey,NFL Jerseys Wholesale,blake griffin jersey blue,NFL jerseys For Sale online.All Our Jerseys Are Sewn On and Directly From Chinese Jerseys Factory ,Wholesale cheap jerseys,Cheap mlb jerseys,]Nike NFL Jerseys,Cheap China Wholesae,Wholesale jerseys From China,2012 nike nfl Jerseys,Jerseys From China,,2012 nike nfl Jerseys,Revolution 30 nba jerseys,jersey of nba chicago bulls direk rose ,nfl jerseys,green bay packers jerseys wholesale,Buffalo Bills nike nfl jerseys sale,good supplier soccer jerseys,cool base mlb jerseys,Revolution 30 nba jerseys,2012 stanley cup nhl jersey, We are professional jerseys manufacturer from china,wholesal.cheap nike nfl jerseys, mlb jerseys, nhl jerseys,nba jerseys and shoes. www.yourjerseyhome.com
|
Title:
2012 NFL jerseys
Name:
NIKE NFL jerseys
Date:
2012-07-02 10:08:11 AM
Comment:
[/pre]Cheap NFL,NBA,MLB,NHL [url=http://www.jersey2shop.com/]cheap jerseys [/url] [url=http://www.jersey2shop.com/]2012 nike nfl Jerseys[/url] [url=http://www.jersey2shop.com/]nba jersey and shorts[/url] [url=http://www.jersey2shop.com/]oklahoma city thunder jersey [/url] [url=http://www.jersey2shop.com/NFL-Jerseys-c68/]official jeremy lin new york knicks jersey [/url] [url=http://www.jersey2shop.com/NBA-Jerseys-c77/]NFL Jerseys Wholesale [/url] NHL Jerseys China [url=http://www.jersey2shop.com/MLB-Jerseys-c94/]blake griffin jersey blue [/url]NFL jerseys For Sale online.All Our Jerseys Are Sewn On and Directly From Chinese Jerseys Factory [/pre] [pre]We Are Professional China jerseys Wholesaler [url=http://www.cheapjersey2store.com/]Wholesale cheap jerseys[/url]Cheap mlb jerseys [url= http://www.cheapjersey2store.com/]Nike NFL Jerseys [/url] [url= http://www.cheapjersey2store.com/ [/url]Cheap China Wholesael[/url] [url= http://www.cheapjersey2store.com/]Wholesale jerseys From China[/url] [url=http://www.cheapjersey2store.com/]2012 nike nfl Jerseys[/url]Free Shipping,Cheap Price,7 Days Deliver [/pre] [/pre] We are professional jerseys manufacturer from china,wholesal sports [url= http://www.cheapjersey2store.com/]Jerseys From China[/url] [url=http://www.cheapjersey2store.com/NFL-Jerseys-c68]NFL jerseys China[/url] [url=http://www.cheapjersey2store.com/NHL-Jerseys-c96/]NHL Jerseys China[/url] [url=http://www.cheapjersey2store.com/NBA-Jerseys-c77/]NBA Jerseys China[/url] [url=http://www.cheapjersey2store.com/MLB-Jerseys-c94/]MLB Jerseys China[/url] [url= http://www.cheapjersey2store.com/]China Jerseys[/url],Free Shipping [/pre] [/pre] We are professional jerseys manufacturer from china,wholesal sports [url= http://www.jerseycaptain.com/]cheap jerseys [/url] [url= http://www.jerseycaptain.com/]2012 nike nfl Jerseys[/url] [url=http://www.jerseycaptain.com/NFL-Jerseys-c68] Revolution 30 nba jerseys [/url] [url=http://www.jerseycaptain.com
|
Title:
Understanding TIMESTAMP (ROWVERSION) in SQL Server
Name:
Stuart Steedman
Date:
2011-01-26 4:08:12 AM
Comment:
Thanks, Abhisheck. Good article. I can now see how the timestamp column could be used to reliably filter all new/changed records.
Stu
|
Title:
Exact use of timestamp
Name:
Anu
Date:
2010-10-08 7:01:33 AM
Comment:
please tell me when and why tp use timestamp.. Confused a lot..
|
Title:
Mr
Name:
tv Krishna Kanth
Date:
2010-07-20 7:33:53 AM
Comment:
To know the latest Timestamp in table you can write a query like "select @@dbts from tblName" .. Yes TimeStamp is datatype which is ofcourse not related to any date & time like data types. here dbtp means data base time stamp you can remember simply with this expansion.. When defining/using a timestamp in a table no need to declare its column name..But the rowversion would ask for the column name eventhough they both are similar in functionality as per my knowledge..
|
Title:
Nonsense
Name:
RIG
Date:
2010-04-23 6:43:30 AM
Comment:
"Wikipedia.org defines timestamp in very clear manner, as follows:
A timestamp is a sequence of characters, denoting the date and/or time at which a certain event occurred."
Right. But TIMESTAMP in MS SQL Server has nothing to do with date or time.
|
Title:
excellent
Name:
Jeetendra
Date:
2010-04-16 3:52:33 AM
Comment:
This is Excellent. Thanks.
|
Title:
good
Name:
saravanan
Date:
2010-01-16 2:20:08 AM
Comment:
good article
|
Title:
timestamp problem
Name:
arpizt
Date:
2009-10-29 9:35:55 PM
Comment:
"UPDATE table SET field = current_timestamp where (field=1)" Error msg this field cannot been update.
Thanks.
|
Title:
arpizt@gmail.com
Name:
abhishek
Date:
2009-10-28 6:41:21 PM
Comment:
arpizt@gmail.com can you post your query here?
|
Title:
timestamp problem
Name:
arpizt@gmail.com
Date:
2009-10-27 5:53:58 AM
Comment:
Hi,
I try to run the insert sql, but got this error ("Column or expression 'PaymentDate' cannot be updated." Why? How to overcome this problem?
|
Title:
Abhishek Your Time Stamp Aarticle too good
Name:
Gbbs Mith
Date:
2009-06-13 5:02:18 AM
Comment:
The article is very good because it was justified clearly time and datetime data type. Hope more such article
Thanks
|
Title:
Beginner
Name:
nagarajan
Date:
2009-03-17 9:47:06 AM
Comment:
excellent artice about TIMESTAMP.
|
Title:
good
Name:
Jen
Date:
2008-12-18 4:57:37 AM
Comment:
good for quick walkthrough.
|
|
|
|