Cannot update a timestamp column

 Msg 272, Level 16, State 1, Line 1

Cannot update a timestamp column.

The timestamp data type is an 8-byte data type that exposes automatically generated, unique binary numbers within a database. It is generally used as a mechanism for version-stamping table rows.

Each database has a counter that is incremented for each insert or update operation that is performed on a table that contains a timestamp column within the database. This counter, the database timestamp, tracks a relative time within a database and not an actual time that can be associated with a clock.
Since the timestamp column is automatically generated by the database and is automatically incremented by the database during an update on the table, this error will be encountered if the timestamp column is manually updated through an UPDATE command.

A table can only have one timestamp column. Every time that a row with a timestamp column is modified or inserted, the incremented database timestamp value is inserted in the timestamp column.

CREATE TABLE [dbo].[Team] (
    [TeamID]     INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
    [TeamName]   VARCHAR(50),
    TIMESTAMP )

Insert one record into the table.

INSERT INTO [dbo].[Team] ( [TeamName], [Timestamp] )
VALUES ( 'Miami Marlins', DEFAULT )

select * from [dbo].[Team]

Output:

TeamID	TeamName	TIMESTAMP
1	Miami Marlins	0x00000000000007D1

Let's try to update which will generate the error msg.

UPDATE [dbo].[Team]
SET [Timestamp] = @@DBTS
WHERE [TeamName] = 'Miami Marlins'

Msg 272, Level 16, State 1, Line 11

Cannot update a timestamp column.

The timestamp column is usually used to determine whether any value in the row has changed since the last time it was read. If any change is made to the row, the timestamp value is updated. If no change is made to the row, the timestamp value is the same as when it was previously read.

If there’s really a need to update the timestamp column of a table just to mark that particular record as having been updated, here’s one work around that can be done without really affecting the row but having the timestamp column updated:

UPDATE [dbo].[Team]
SET [TeamName] = [TeamName]
WHERE [TeamName] = 'Miami Marlins'

select * from [dbo].[Team]

Output:

TeamID	TeamName	TIMESTAMP
1	Miami Marlins	0x00000000000007D2