LogoASPAlliance: Articles, reviews, and samples for .NET Developers
Understanding TIMESTAMP (ROWVERSION) in SQL Server
by Abhishek Kumar Singh
Average Rating: 
Views (Total / Last 10 Days): 221730/ 708


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

[DateTime]  DATETIME)


Listing 2

[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


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

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

SET @timest = 5
SELECT @timest
If you execute the above query this will result following output:

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

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

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.


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.

©Copyright 1998-2021  |  Page Processed at 2021-03-02 8:59:25 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search