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
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 (
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.
Microsoft Dynamics Nav (also known as Navision – it is an ERP-system) use it quite a lot. It might be a ‘legacy’-feature from when MS bought Navision, but it is still there 10-15 years after bought the company
The rowversion/timestamp is great for two things: change detection and (as a logical extension thereof) optimistic record locking in the application tier. Plenty of articles about the latter on the interwebs.