Understanding TIMESTAMP (ROWVERSION) in SQL Server
page 5 of 7
by Abhishek Kumar Singh
Feedback
Average Rating: 
Views (Total / Last 10 Days): 221562/ 694

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())

View Entire Article

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.






Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2021 ASPAlliance.com  |  Page Processed at 2021-02-26 10:40:10 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search