ROWVERSION and TIMESTAMP Data Types - SQL Server for Oracle DBAs

In SQL Server, ROWVERSION and TIMESTAMP data types represent automatically generated binary numbers, unique within the database.

ROWVERSION and TIMESTAMP are synonyms, ROWVERSION is available since SQL Server 2005, while TIMESTAMP is deprecated and will be removed in a future version of SQL Server .

ROWVERSION (TIMESTAMP) is an incrementing 8-byte binary number, and unlike Oracle TIMESTAMP data type, it does not store any datetime related information.

You can use timestamp columns to build custom data replication and synchronization solutions.

ROWVERSION and TIMESTAMP Overview

Summary information:

Syntax ROWVERSION Available since SQL Server 2005
TIMESTAMP Deprecated
Generated Numbers Unique within a database
Storage Size 8 bytes
Internal Representation BINARY(8) For non-nullable
VARBINARY(8) For nullable
Number of Columns Allowed One per table

Last Update: Microsoft SQL Server 2012

ROWVERSION and TIMESTAMP Details

When you insert or update a ROWVERSION/TIMESTAMP column, a new unique number across the database is generated and assigned to the column.

You can use @@DBTS function to get the current (last used) timestamp value:

SQL Server:

  -- Get the last-used timestamp value
  SELECT @@DBTS; 
  /* 0x00000000000007D0 */

Now let's create 2 table with ROWVERSION columns and insert rows:

SQL Server:

  -- First table with ROWVERSION column
  CREATE TABLE states
  (
     id CHAR(2),
     name VARCHAR(90),
     rv ROWVERSION
  );
 
  -- Second table with ROWVERSION column
  CREATE TABLE cities
  (
     name VARCHAR(90),
     state CHAR(2),
     rv ROWVERSION
  );
 
  INSERT INTO states (id, name) VALUES ('CA', 'California');
  INSERT INTO cities (name, state) VALUES ('San Mateo', 'CA');

Now querying the ROWVERSION columns in these 2 tables, you can see that timestamps were sequentially generated:

SQL Server:

  SELECT rv FROM states;
  /* 0x00000000000007D1 */
 
  SELECT rv FROM cities; 
  /* 0x00000000000007D2 */
 
  -- Last used timestamp
  SELECT @@DBTS;
  /* 0x00000000000007D2 */

If you update a row, the ROWVERSION column gets a new value:

SQL Server:

  UPDATE cities 
  SET name = 'San Jose'
  WHERE name = 'San Mateo';
 
  SELECT rv FROM cities; 
  /* 0x00000000000007D3 */
 
  -- Last used timestamp
  SELECT @@DBTS;
  /* 0x00000000000007D3 */

Explicit Insert Not Allowed

SQL Server does not allow you to insert a value to a timestamp column explicitly:

SQL Server:

   INSERT INTO cities VALUES ('San Diego', 'CA', 0x0);
   /* Msg 273, Level 16, State 1, Line 1
   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. */

Resources

SQLines Services

SQLines offers services to migrate Oracle databases and applications to Microsoft SQL Server. For more information, please Contact Us.

You could leave a comment if you were logged in.