SA0227 : The TIMESTAMP syntax is deprecated, use ROWVERSION instead

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

Description

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:

SQL
Copy
1-- Example of problematic query
2CREATE TABLE ExampleTable (
3    ID INT,
4    TimestampColumn TIMESTAMP
5);

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.

How to fix

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:

SQL
Copy
1-- Example of corrected query
2CREATE TABLE ExampleTable (
3    ID INT,
4    TimestampColumn rowversion
5);

Scope

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

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix
3 minutes per issue.
Categories

Deprecated Features, Bugs

Additional Information
Example Test SQL
SQL
Copy
1CREATE TABLE ExampleTable1 (PriKey int PRIMARY KEY, timestamp); 
2ALTER TABLE ExampleTable2 ADD Timestamp
3
4CREATE TABLE ExampleTable3 (PriKey int PRIMARY KEY, VerCol rowversion) ;  
5ALTER TABLE ExampleTable4 ADD VerCol Rowversion NOT NULL
6ALTER TABLE ExampleTable5 ADD VerCol Timestamp
Analysis Results
  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
See Also

Other Resources

Analysis Rules