Recently, I was helping a team implement optimistic concurrency into their application. In my previous post (SQL Server - DDL - ROWVERSION v/s TIMESTAMP - a key difference), we reviewed a critical DDL difference with respect to table definition between the ROWVERSION and TIMESTAMP columns.

Many DBAs and developers believe that ROWVERSION is a unique value. This statement causes an interpretation that a table with a ROWVERSION column will always have unique values across the database.

The above statement is not accurate – the ROWVERSION column does get a new value during an insert, however, there is a possibility that ROWVERSION columns can hold duplicate values. This can happen during a bulk insert (SELECT…INTO).

Here’s a simple demonstration:

USE tempdb
GO

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

IF OBJECT_ID('SourceData') IS NOT NULL
BEGIN
    DROP TABLE SourceData
END
GO

--Create the demo (source) table
CREATE TABLE SourceData (RowId INT IDENTITY (1,1),
                         RowVal VARCHAR(20),
                         DataVersion ROWVERSION
                        )
GO

--Insert some test data
INSERT INTO SourceData (RowVal) VALUES ('Windows'),
                                       ('SQL Server'),
                                       ('BizTalk Server'),
                                       ('Exchange Server')

--Bulk Insert the source data into destination table
SELECT SourceData.RowId, SourceData.RowVal, SourceData.DataVersion
INTO RowVersionDemo FROM SourceData

--Select the data inserted (for demo purposes)
SELECT RowId, RowVal, DataVersion
FROM SourceData

SELECT RowId, RowVal, DataVersion
FROM RowVersionDemo
GO

--Check the data-types of the columns created as part of the BULK-INSERT:
SELECT isc.TABLE_NAME,
       isc.COLUMN_NAME,
       isc.DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS AS isc
WHERE isc.TABLE_NAME = 'RowVersionDemo'
GO

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

IF OBJECT_ID('SourceData') IS NOT NULL
BEGIN
    DROP TABLE SourceData
END
GO

We can see here that SQL Server inserted the column data “as-is” into the destination tables. A look at the data-types of the destination table shows us that it has been created as a “timestamp” column, which is a documented bug within Microsoft SQL Server.

In short, using ROWVERSION does not guarantee uniqueness – one can have duplicate values amongst the ROWVERSION columns within the same database.

References:

• Connect Cases related to ROWVERSION being displayed as “TIMESTAMP”
  • https://connect.microsoft.com/SQLServer/feedback/details/274333/deprecate-timestamp-the-keyword-not-rowversion-itself
  • https://connect.microsoft.com/SQLServer/feedback/details/530944/sql-2008-still-shows-timestamp-not-rowversion
• Optimistic Concurrency using ROWVERSION: http://beyondrelational.com/modules/2/blogs/77/posts/11268/optimistic-concurrency-a-conceptual-introduction.aspx

Until we meet next time,

Be courteous. Drive responsibly.