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:
- The ROWVERSION data type is just an incrementing number and does not preserve a date or a time
- A table can have only one ROWVERSION column
- ROWVERSION should not be used as keys, especially primary keys
- Using a SELECT INTO statement has the potential to generate duplicate ROWVERSION values
- Finally for those who came from SQL 2005, the TIMESTAMP is deprecated. Please avoid using this in new DDL statements
- 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
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
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.
SQLTwins by Nakul Vachhrajani 