SQL Server Error Messages - Msg 273

Error Message

Server: Msg 273, Level 16, State 1, Line 1
Cannot insert an explicit value into a timestamp column.
Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column.

Causes

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.

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.

Since the timestamp column is automatically generated by the database, this error will be encountered if a value is specified to the timestamp column when a new row is inserted into a table that contains a timestamp column.

To illustrate, here’s a table that contains a timestamp column:

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

Inserting a new row in this table and specifying a value to the timestamp column will generate this error:

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

Msg 273, Level 16, State 1, Line 1
Cannot insert an explicit value into a timestamp column.
Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column.

The @@DBTS system function returns the value of the current or last-used timestamp value for the current database.

Solution / Work Around

There are 2 ways of working around this issue, as the error message suggests. The first method is not to include the timestamp column in the list of columns specified in the INSERT statement:

INSERT INTO [dbo].[Team] ( [TeamName] )
VALUES ( 'New York Yankees' )

The second method is to specify DEFAULT as the value in the timestamp column, as can be seen in the following statement:

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

The timestamp column of a row can be used to easily 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.