Understanding TIMESTAMP (ROWVERSION) in SQL Server: ASP Alliance

Print Add To Favorites Email To Friend Rate This Article
Understanding TIMESTAMP (ROWVERSION) in SQL Server

page 4 of 7

by Abhishek Kumar Singh
Feedback

Average Rating:

Views (Total / Last 10 Days): 15116/ 417

Article Contents:

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

« (Page 3)

View Entire Article

(Page 5) »

Article Feedback

Comment:
Title:
Name:
Url:	( Optional )

Please add 4 and 2 and type the answer here:

User Comments

Title: timestamp problem
Name: arpizt
Date: 10/29/2009 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: 10/28/2009 6:41:21 PM
Comment:
arpizt@gmail.com can you post your query here?

Title: timestamp problem
Name: arpizt@gmail.com
Date: 10/27/2009 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: 6/13/2009 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: 3/17/2009 9:47:06 AM
Comment:
excellent artice about TIMESTAMP.

Title: job
Name: eak
Date: 1/8/2009 2:17:28 AM
Comment:
http://www.sqlhacks.com/index.php/Dates/Timestamp

Title: good
Name: Jen
Date: 12/18/2008 4:57:37 AM
Comment:
good for quick walkthrough.