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?
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.