Export (0) Print
Expand All
Expand Minimize
3 out of 7 rated this helpful - Rate this topic

timestamp (SQL Server Compact)

Is a data type that exposes automatically generated, unique binary numbers in a database. rowversion and timestamp are data type synonyms. rowversion is generally used as a mechanism for version-stamping table rows. The storage size is 8 bytes. The rowversion data type is an incrementing number and does not preserve a date or a time. To record a date or time, use a datetime data type.

The characteristics of rowversion data type are as follows:

  • A table can have only one rowversion column.

  • The value in the rowversion column is updated every time a row that contains a rowversion column is inserted or updated. This makes a rowversion column a poor candidate for keys, especially primary keys.

  • rowversion is a read-only column and cannot be updated.

  • rowversion is unique across the database.

  • To return the current rowversion value for a database, use the command @@DBTS.

Important note Important

When a transaction is rolled back or some validation fails, the rowversion counter cannot be reset to the old value. This is prohibited because if other transactions were occurring at the same time, resetting the counter could cause problems. To find the current value of rowversion, you can query it by using @@DBTS. During replication between SQL Server to SQL Server Compact, or vice-versa, timestamp columns are not replicated from one database to another. This helps ensure the uniqueness of the timestamp. A copied value can conflict with an existing value of some data row, or a future value.

If you are using the timestamp data type, there is a difference in the query statement written for SQL Server and SQL Server Compact. In SQL Server, the column name is optional if you use the timestamp data type. However, in SQL Server Compact, the column name is mandatory.

If you are using the rowversion data type, you should always specify the column name, whether you are using SQL Server or SQL Server Compact. The following examples illustrate queries that use timestamp and rowversion keyword. 

SQL Server

SQL Server Compact 3.5

Timestamp query

CREATE TABLE ExampleTable (PriKey int PRIMARY KEY, timestamp);

Timestamp query

CREATE TABLE ExampleTable (PriKey int PRIMARY KEY, c3 timestamp);

Rowversion query

Create table t1 (c1 int PRIMARY KEY, c2 nvarchar(50), c3 rowversion);

Rowversion query

Create table t1 (c1 int PRIMARY KEY, c2 nvarchar(50), c3 rowversion);
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft. All rights reserved.