Skip to content

SA0227 : The TIMESTAMP syntax is deprecated, use ROWVERSION instead

Using the TIMESTAMP syntax is problematic and may affect future compatibility.

The use of the TIMESTAMP syntax in T-SQL code, particularly in ALTER TABLE and CREATE TABLE statements, is discouraged as it is deprecated in SQL Server. This syntax is not aligned with the ISO standard for a timestamp data type and instead functions as a synonym for rowversion . Its inconsistent behavior can lead to confusion and may break SQL scripts in future versions of SQL Server.

For example:

-- Example of problematic query
CREATE TABLE ExampleTable (
ID INT,
TimestampColumn TIMESTAMP
);

The above example is problematic because it uses the deprecated TIMESTAMP syntax, which may result in maintenance issues and compatibility problems in future SQL Server releases.

  • The TIMESTAMP keyword does not represent a date and time data type, contrary to what may be expected.

  • Scripts using TIMESTAMP may fail or behave unexpectedly in newer SQL Server versions.

This rule addresses the use of the TIMESTAMP syntax, which is deprecated and should be replaced with rowversion for better compatibility and adherence to standards.

Follow these steps to address the issue:

1.Identify all instances of the TIMESTAMP syntax in your T-SQL scripts, especially in CREATE TABLE and ALTER TABLE statements.

2.Replace the TIMESTAMP keyword with rowversion for any columns intended to store the automatically generated unique binary numbers.

3.Test your scripts to ensure that they function correctly with the updated rowversion syntax and no unintended side effects are introduced.

4.Review and modify any application logic or documentation referring to TIMESTAMP data type to reflect the change to rowversion .

For example:

-- Example of corrected query
CREATE TABLE ExampleTable (
ID INT,
TimestampColumn rowversion
);

The rule has a Batch scope and is applied only on the SQL script.

Rule has no parameters.

The rule does not need Analysis Context or SQL Connection.

3 minutes per issue.

Deprecated Features, Bugs

Deprecated Database Engine Features in SQL Server 2017

CREATE TABLE ExampleTable1 (PriKey int PRIMARY KEY, timestamp);
ALTER TABLE ExampleTable2 ADD Timestamp
CREATE TABLE ExampleTable3 (PriKey int PRIMARY KEY, VerCol rowversion) ;
ALTER TABLE ExampleTable4 ADD VerCol Rowversion NOT NULL
ALTER TABLE ExampleTable5 ADD VerCol Timestamp
  Message Line Column
1 SA0227 : The TIMESTAMP syntax is deprecated, use ROWVERSION instead. 2 52
2 SA0227 : The TIMESTAMP syntax is deprecated, use ROWVERSION instead. 3 30

Analysis Rules