#0181 – SQL Server – DDL – ROWVERSION v/s TIMESTAMP – a key difference


A couple of years ago when I started blogging, I wrote about an approach for implementing optimistic concurrency in an application (Link). The approach revolves around the storage, retrieval and comparison of the ROWVERSION value – which is an improvement upon the TIMESTAMP data-type, which has been marked as deprecated.

There are a handful of differences between the ROW VERSION and TIMESTAMPS columns. Yet, most accidental DBAs and novice database developers make the mistake of thinking that they are one and the same. While I had mentioned all the differences in my post on optimistic concurrency, here’s a recap:

  1. The ROWVERSION data type is just an incrementing number and does not preserve a date or a time
  2. A table can have only one ROWVERSION column
  3. ROWVERSION should not be used as keys, especially primary keys
  4. Using a SELECT INTO statement has the potential to generate duplicate ROWVERSION values
  5. Finally for those who came from SQL 2005, the TIMESTAMP is deprecated. Please avoid using this in new DDL statements
  6. Unlike TIMESTAMP, the ROWVERSION column needs a column name in the DDL statements

The last difference is what throws off most people – they simply replace all instances of “TIMESTAMP” in their DDL code and expect it to work – which will not be the case.

Here’s a simple demonstration.

A typical TIMESTAMP implementation would look like this:

USE tempdb
GO

--Safety Check
IF OBJECT_ID('RowVersionDemo') IS NOT NULL
BEGIN
    DROP TABLE RowVersionDemo
END
GO

--Create a test table
CREATE TABLE RowVersionDemo (Id INT IDENTITY(1,1),
                             Data VARCHAR(20),
                             TIMESTAMP
                            )
GO

--Insert some test data
INSERT INTO RowVersionDemo (Data) VALUES ('Windows'),
                                         ('SQL Server')
GO

--Fetch Data
SELECT RowVersionDemo.Id, RowVersionDemo.Data, RowVersionDemo.TIMESTAMP
FROM RowVersionDemo

TIMESTAMP value inserted into a table

As you can see, we just placed a TIMESTAMP column in the table definition – without really specifying the column name. Simply replacing all instances of TIMESTAMP with ROWVERSION will result in an error:

--ROWVERSION - ERROR
USE tempdb
GO

--Safety Check
IF OBJECT_ID('RowVersionDemo') IS NOT NULL
BEGIN
    DROP TABLE RowVersionDemo
END
GO

--Create a test table
CREATE TABLE RowVersionDemo (Id INT IDENTITY(1,1),
                             Data VARCHAR(20),
                             ROWVERSION
                            )
GO

Msg 173, Level 15, State 1, Line 5

The definition for column ‘ROWVERSION’ must include a data type.

ROWVERSION is a data-type and needs a column name to be associated with it. Hence, the following will work:

--ROWVERSION - FIXED
USE tempdb
GO

--Safety Check
IF OBJECT_ID('RowVersionDemo') IS NOT NULL
BEGIN
    DROP TABLE RowVersionDemo
END
GO

--Create a test table
CREATE TABLE RowVersionDemo (Id INT IDENTITY(1,1),
                             Data VARCHAR(20),
                             DataVersion ROWVERSION
                            )
GO

--Insert some test data
INSERT INTO RowVersionDemo (Data) VALUES ('Windows'),
                                         ('SQL Server')
GO

--Fetch Data
SELECT RowVersionDemo.Id, RowVersionDemo.Data, RowVersionDemo.DataVersion
FROM RowVersionDemo

ROWVERSION values stored in the database

The above simple demonstration proves that TIMESTAMP and ROWVERSION cannot be used in the same way when defining the database tables. Conversion from TIMESTAMP to ROWVERSION is not a direct find-replace operation.

There are more knowledge nuggets coming up related to the ROWVERSION data-type. Stay tuned to this blog for more!

Further Reading

  • Implementing Optimistic Concurrency using ROWVERSION:

Until we meet next time,
Be courteous. Drive responsibly.

Advertisements

6 thoughts on “#0181 – SQL Server – DDL – ROWVERSION v/s TIMESTAMP – a key difference

  1. rojipt

    Nice article Nakul. I especially like the first part you have linked.

    I’ve a doubt whether, to some, the wordings in this post may suggest that TIMESTAMP and ROWVERSION are different datatypes whereas they are just synonyms. Everything is same, except for the silly behavior you mentioned in #6. Also the synonym TIMESTAMP is just marked for deprecation, but it still works even with SQL 2012.

    And even in SQL 2012, the table designer GUI shows only TIMESTAMP not ROWVERSION.

    Regards
    Roji Thomas
    http://sqlindian.com

    Like

    Reply
  2. Nakul Vachhrajani

    Hello, Roji!

    Thank-you for your feedback.

    The fact that SSMS and rest of the SQL Server meta-data still shows ROWVERSION as TIMESTAMP is a bug as acknowledged by Microsoft in their MS Connect. TIMESTAMP is a misleading term – causing someone to think that there is a date/time element to it, when in fact, that’s not the case. ROWVERSION is a much more accurate term.

    More interesting bits & pieces around ROWVERSION are coming up in the next couple of posts (next week – Monday & Thursday), where I will be referencing the MS Connect documentation mentioned in my comments above.

    In the meanwhile, I thank-you for taking the time out, reading my post and sending your valuable feedback. Do do keep reading, and do keep the feedback coming!

    Like

    Reply
  3. jcelko

    It is worth mentioning that TIMESTAMP is the ANSI Standard term for DATETIME2(n) in T-SQL dialect.
    As MS moves to ANSI, you might see TIMESTAMP come back in the futrue.

    Like

    Reply
  4. Nakul Vachhrajani

    @jcelko: Thank-you for sharing that piece of information! I did not know that. TIMESTAMP as DATETIME2(n) makes sense – at least that way there is no confusion on the meaning of “TIMESTAMP”, which is the case now.

    Thank-you for taking the time out to read my post and sharing with all the relationship with the ANSI standard. Thank-you very much!

    Like

    Reply
  5. rojipt

    Thanks for the clarifications Nakul and Good to see you here CELKO.

    The point I was trying to drive home is that, in the context of datatype, the properties and behavior of TIMESTAMP and ROWVERSION are exactly same since they are just synonyms of the same type. That was always the case including in SQL 2012.

    The documentation upto SQL 2005 lists TIMESTAMP as the base type and ROWVERSION as the synonym (http://msdn.microsoft.com/en-us/library/ms177566(v=sql.90).aspx) Whereas later versions lists ROWVERSION as the base type and TIEMSTAMP as the synonym (http://msdn.microsoft.com/en-us/library/ms177566(v=sql.105).aspx).

    However this change is only a documentation change. ROWVERSION was never implemented as a base type. This is the reason why the GUI and scripting tools still shows TIMESTAMP datatype.

    You can confirm this by running the below queries.

    select * from sys.types
    WHERE name = ‘rowversion’

    select * from sys.types
    WHERE name = ‘timestamp’

    You can see that a type named ROWVERSIN does not exists. There are articles on the web suggesting that ROWVERSION is a new datatype introduced in SQL 2008, which is plain wrong.

    Here is the official list of types added in 2008 (http://technet.microsoft.com/en-us/magazine/2008.04.datatypes.aspx)

    You are right about the reason for the deprecation being the confusion the term TIMESTAMP creates especially since the ANSI specification says that it is a datatime type.

    There is no need to change the existing TIMESTAMP implementation to ROWVERSION given the current implementation. I really doubt whether we will see the current TIMESTAMP implementation actually replaced by ROWVERSION in the near future.

    I am looking forward for your future posts on the topic.

    Regards
    Roji Thomas
    http://sqlindian.com

    Like

    Reply
  6. Pingback: #0182 – SQL Server – ROWVERSION – Uniqueness Myth – Possibility of duplicate values exists | SQLTwins by Nakul Vachhrajani

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s