|
|
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.
|
It should be noted that the TIMESTAMP data type is a synonym of ROWVERSION data type and is subject to the behavior of data type synonyms. The TIMESTAMP syntax is deprecated and will be removed in a future version of SQL Server. In DDL statements, it is suggested to use ROWVERSION instead of TIMESTAMP wherever possible. In the following paragraphs and sample codes, ROWVERSION will be used instead of TIMESTAMP
Each database has a counter that is incremented for each insert or update operation that is performed on a table that contains a ROWVERSION column within the database. This counter, the database ROWVERSION, 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 ROWVERSION column. Every time that a row with a ROWVERSION column is modified or inserted, the incremented database ROWVERSION value is inserted in the ROWVERSION column. It should be noted that the ROWVERSION value is incremented with any UPDATE statement, even if no row values are modified. As an example, if a column value is 100, and an UPDATE statement sets the value to the same value of 100, this action is considered an update even though the resulting row value is the same as before and thus the ROWVERSION is incremented.
Since the ROWVERSION column is automatically generated by the database, this error will be encountered if a value is specified to the ROWVERSION column when a new row is inserted into a table that contains a ROWVERSION column.
To illustrate, here’s a table that contains a ROWVERSION column:
CREATE TABLE [dbo].[Team] (
[TeamID] INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
[TeamName] VARCHAR(50),
[RowVersion] ROWVERSION )
GO
Inserting a new row in this table and specifying a value to the timestamp column will generate this error:
INSERT INTO [dbo].[Team] ( [TeamName], [RowVersion] )
VALUES ( 'Detroit Tigers', @@DBTS ),
( 'Houston Astros', @@DBTS ),
( 'Kansas City Royals', @@DBTS ),
( 'Los Angeles Dodgers', @@DBTS ),
( 'Miami Marlins', @@DBTS ),
( 'New York Yankees', @@DBTS )
GO
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.
Figure 1: Error Message 273 - Cannot insert an explicit value into a TIMESTAMP column.
The @@DBTS system function returns the value of the current or last-used ROWVERSION value for the current database. The current database will have a guaranteed unique ROWVERSION value.
Solution / Work Around
There are 2 ways of working around this issue, as the error message suggests.
Option #1 - Exclude ROWVERSION Column in INSERT Statement
The first method is not to include the ROWVERSION column in the list of columns specified in the INSERT statement:
INSERT INTO [dbo].[Team] ( [TeamName] )
VALUES ( 'Detroit Tigers' ),
( 'Houston Astros' ),
( 'Kansas City Royals' ),
( 'Los Angeles Dodgers' ),
( 'Miami Marlins' ),
( 'New York Yankees' )
GO
SELECT * FROM [dbo].[Team]
GO
| TeamID | TeamName | RowVersion |
|--------|---------------------|------------|
| 1 | Detroit Tigers | Ó |
| 2 | Houston Astros | Ô |
| 3 | Kansas City Royals | Õ |
| 4 | Los Angeles Dodgers | Ö |
| 5 | Miami Marlins | × |
| 6 | New York Yankees | Ø |
Figure 2: Error Message 273 - Solution #1 - Exclude ROWVERSION Column in INSERT Statement
Option #2 - Use DEFAULT Value
The second method is to specify DEFAULT as the value in the ROWVERSION column, as can be seen in the following statement:
INSERT INTO [dbo].[Team] ( [TeamName], [RowVersion] )
VALUES ( 'Detroit Tigers', DEFAULT ),
( 'Houston Astros', DEFAULT ),
( 'Kansas City Royals', DEFAULT ),
( 'Los Angeles Dodgers', DEFAULT ),
( 'Miami Marlins', DEFAULT ),
( 'New York Yankees', DEFAULT )
GO
SELECT * FROM [dbo].[Team]
GO
| TeamID | TeamName | RowVersion |
|--------|---------------------|------------|
| 1 | Detroit Tigers | Ù |
| 2 | Houston Astros | Ú |
| 3 | Kansas City Royals | Û |
| 4 | Los Angeles Dodgers | Ü |
| 5 | Miami Marlins | Ý |
| 6 | New York Yankees | Þ |
Figure 3: Error Message 273 - Solution #2 - Use DEFAULT Value
The ROWVERSION 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 ROWVERSION value is updated. If no change is made to the row, the ROWVERSION value is the same as when it was previously read.
|