SQLErudition.com

The TIMESTAMP datatype in SQL Server is in no way related to any Date/Time datatype or any operation related to dates or time. It is a simple monotonously incrementing counter that increases by one every time there is an INSERT or UPDATE operation in the database. It is stored as BINARY but can be converted to BIGINT or INT type to make the values seem more user-friendly. The IDENTITY property of a table is quite similar when we think about a monotonously incrementing value. While the IDENTITY counter is tracked at a table level, the TIMESTAMP counter is tracked at a database level. The current value of the TIMESTAMP counter can be returned by using the Configration Function @@DBTS. We can assume a TIMESTAMP to be a GUID in the smaller context of a database. So theoretically no two tables in a database can have the same TIMESTAMP value in any of their rows. But practically it is possible to go around this via SELECT..INTO syntax to create a new table, as I’ll demostrate later in this post.

As calling it a TIMESTAMP has caused confusion among SQL developers, Microsoft introduced ROWVERSION as a new name for it. Calling it ROWVERSION also emphasizes its utility for implementing optimistic locking in the code. TIMESTAMP has been deprecated and will be discontinued in a future SQL Server release so use ROWVERSION in all new development work.

It is optional to give the column a name in the DDL with the TIMESTAMP datatype. If a column name is not specified then SQL Server uses the default name of “TIMESTAMP” for the column. Helpful, eh?

On the other hand, if using the ROWVERSION as datatype, a column name must be given just like creating a column any other datatype.

A table can have only one column of TIMESTAMP (or ROWVERSION) type.

You cannot enter/update a value explicitly into a column of TIMESTAMP or ROWVERSION datatype. The database manages it internally. If you do try then an error is returned –

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.

The DBTS value starts from 2000 in a brand new database without any table containing a TIMESTAMP/ROWVERSION column.

CREATE DATABASE [TestDB] 
GO

-- Find the default value of 
-- the TIMESTAMP/ROWVERSION counter 
SELECT @@DBTS AsBinary, 
       CAST(@@DBTS AS BIGINT) AsBigint 

/* Result
AsBinary                AsBigint 
0x00000000000007D0      2000 
*/

Does the value of the counter change after new table is created with a TIMESTAMP/ROWVERSION?

No. Just creating a table does not change the counter.

CREATE TABLE a (
       id INT IDENTITY, 
       TIMESTAMP)
GO
SELECT @@DBTS AsBinary, 
       CAST(@@DBTS AS BIGINT) AsBigint 
/* Result
AsBinary               AsBigint 
0x00000000000007D0     2000 
*/

Insert a row in a table and check counter value. It increases by one.

INSERT INTO a DEFAULT VALUES
GO
SELECT *, 
       CAST(timestamp AS BIGINT) 
FROM a 

SELECT @@DBTS AsBinary, 
       CAST(@@DBTS  AS BIGINT) AsBigint 
/* Result
AsBinary               AsBigint 
0x00000000000007D1     2001 
*/

Create a new table, this time with ROWVERSION and insert a row. The counter still increases by one.

CREATE TABLE b (
       id INT IDENTITY, 
       col2  ROWVERSION) 
GO
INSERT INTO b DEFAULT VALUES
GO
SELECT *, 
       CAST(col2  AS BIGINT) 
FROM b 

-- Find the value of the counter after 
-- a row is inserted 
SELECT @@DBTS AsBinary, 
       CAST(@@DBTS  AS BIGINT) AsBigint 
/* Result
AsBinary               AsBigint 
0x00000000000007D2     2002 
*/

Do the INSERTS in the same transaction have the same counter value? Let us try.

BEGIN TRAN 
INSERT INTO a DEFAULT VALUES
INSERT INTO b DEFAULT VALUES
COMMIT

SELECT *, 
       CAST(timestamp AS BIGINT) 
FROM a 

SELECT *, 
       CAST(col2 AS BIGINT) 
FROM b 

-- Find the value of the counter after rows 
-- are inserted in multiple tables in a transaction. 
-- Each row still gets a unique counter value.
SELECT @@DBTS AsBinary, 
       CAST(@@DBTS  AS BIGINT) AsBigint 
/* Result
AsBinary               AsBigint 
0x00000000000007D4     2004 
*/

Does the counter increase in case of a failed transaction?

BEGIN TRAN 
INSERT INTO a DEFAULT VALUES
INSERT INTO b DEFAULT VALUES
ROLLBACK

SELECT *, 
       CAST(timestamp AS BIGINT) 
FROM a 

SELECT *, 
       CAST(col2 AS BIGINT) 
FROM b 

-- Find the counter value after a failed transaction
-- Yes. The counter value still increases.
SELECT @@DBTS AsBinary, 
CAST(@@DBTS AS BIGINT) AsBigint 
/* Result
AsBinary               AsBigint 
0x00000000000007D6     2006 
*/

Is is possible to have duplicate TIMESTAMP values across tables of a database?
Yes. Although values in TIMESTAMP/ROWVERSION columns cannot be explicitly inserted or modified they can be copied from one table to another using the SELECT..INTO syntax, which in turn could result in duplicate counter values across tables

SELECT *
INTO c -- New table
FROM a

SELECT *, 
       CAST(timestamp AS BIGINT) 
FROM a -- Source table 

SELECT *, 
       CAST(timestamp AS BIGINT) 
FROM c -- New table 

-- The counter value does not change.
SELECT @@DBTS AsBinary, 
       CAST(@@DBTS AS BIGINT) AsBigint 
/*
AsBinary               AsBigint 
0x00000000000007D6     2006 
*/

Does the counter continue with insertions into new table that was created with SELECT…INTO? Yes, the counter changes because the new table gets the column definition of the source table including the ROWVERSION column.

INSERT INTO c DEFAULT VALUES
SELECT *, 
       CAST(timestamp AS BIGINT) 
FROM c -- New table 

-- Find the value of the counter after a row 
-- is inserted into the new table.
-- The counter value does change.
SELECT @@DBTS AsBinary, 
       CAST(@@DBTS AS BIGINT) AsBigint 
/*
AsBinary               AsBigint 
0x00000000000007D7     2007 
*/

Final review of the counter values in tables

SELECT *, CAST(timestamp AS BIGINT) 
FROM a 

SELECT *, CAST(col2 AS BIGINT) 
FROM b 

SELECT *, CAST(timestamp AS BIGINT) 
FROM c