Difference Between ROWVERSION and TimeStamp – SQLSERVER
No difference! rowversion is new name for timestamp or we can say a synonym for timestamp datatype. Actually, the “Timestamp” datatype was defined as a counter of database row updates. Later, the ISO defined the “Timestamp” datatype as date and time information, causing a conflict between the SQL standards definition and the specific usage by MS SQL-Server. So Microsoft replaced the name “timestamp” by a new name “rowversion”
Though SQLSERVER 2008 accepts column type as rowversion in CREATE TABLE query however SSMS lags behind and displays datatype timestamp in options list for available datatypes.
All previous rules that were associated with Timestamp datatype are same for rowversion, like:
We can have only one timestamp (now rowversion) column in one table, if you try to add more than one rowversion column you will be prompted with an error: A table can only have one timestamp column. Because table ” already has one, the column ” cannot be added.
For those who are not familiar with timestamp datatype: rowversion is basically a mechanism for version-stamping table rows. It’s storage size is 8 bytes is just an incrementing number which does not preserve a date or a time on which the record was last updated. To record a date or time, use a datetime2 data type is the option.
SQLSERVER Databases has a counter that is incremented on each insert or update operation that is performed on a table that contains a rowversion column within the database. Based on the counter, this tracks a relative time within a database, not an actual time that can be associated with a clock.
Insertion and update on the
rowversiondatatype column is not allowed. If you try, you will get the below error.
On Insert: –Msg 273, Level 16, State 1, Line 1 Cannot insert an explicit value into a timestamp column
On Update: –Msg 272, Level 16, State 1, Line, Cannot update a timestamp column.
As you can notice, every time SQL Server generates error it referrs to the rowversion datatype as the timestamp. This is because rowversion is a synonym for timestamp, and every time that a row with a rowversion column is updated or inserted, the incremented database rowversion value is inserted in the rowversion column. This property makes a rowversion column a poor candidate for keys, especially primary keys.