Dates and Times in SQL Server: What about TIMESTAMP?

It occurred to me that we haven’t covered the TIMESTAMP data type in this series about dates and times.

TIMESTAMP is the Windows Millennium Edition of data types. It has nothing to do with date and time. It’s a row version. Microsoft asks that we stop calling it TIMESTAMP and use ROWVERSION instead.

Much like DECIMAL is a synonym of NUMERIC, so too is TIMESTAMP a synonym of ROWVERSION. Please call it a ROWVERSION and pretend that TIMESTAMP doesn’t exist. Microsoft is deeply sorry for the confusion.

Naming things is hard.

What is a TIMESTAMP ROWVERSION anyway?

Every SQL Server database has an internal counter which increments whenever a database modification takes place. This counter is exposed by the ROWVERSION data type, which is an eight-byte binary value.

If you run any modification statement against a table (INSERT, UPDATE, DELETE), the row version will increment, even if no actual data changes.

Confusing? Well, that’s what the documentation says:

Every time that a row with a rowversion column is modified or inserted, the incremented database rowversion value is inserted in the rowversion column. […] The rowversion value is incremented with any update statement, even if no row values are changed.

I personally have never intentionally used ROWVERSION in a database design. Eight-byte entries add up fast, so be wary when using this data type.

What should I use for an actual timestamp column?

If you want to record an actual timestamp in a column, use the DATETIME2 data type or TIME data type with an appropriate scale.

On the other hand, if you want to keep track of the internal version number of rows in a table, use ROWVERSION and don’t call it TIMESTAMP (that word is now deprecated, thank goodness). There can be only one ROWVERSION column in a table, and it should not be a primary key.

If you have ever used this data type on purpose, tell me about it on Twitter at @bornsql.