SA0227 : The TIMESTAMP syntax is deprecated, use ROWVERSION instead
Introduction
Section titled “Introduction”Using the TIMESTAMP syntax is problematic and may affect future compatibility.
Description
Section titled “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:
-- Example of problematic queryCREATE 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
TIMESTAMPkeyword does not represent a date and time data type, contrary to what may be expected. -
Scripts using
TIMESTAMPmay fail or behave unexpectedly in newer SQL Server versions.
How to fix
Section titled “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:
-- Example of corrected queryCREATE TABLE ExampleTable ( ID INT, TimestampColumn rowversion);The rule has a Batch scope and is applied only on the SQL script.
Parameters
Section titled “Parameters”Rule has no parameters.
Remarks
Section titled “Remarks”The rule does not need Analysis Context or SQL Connection.
Effort To Fix
Section titled “Effort To Fix”3 minutes per issue.
Categories
Section titled “Categories”Deprecated Features, Bugs
Additional Information
Section titled “Additional Information”Deprecated Database Engine Features in SQL Server 2017
Example Test SQL
Section titled “Example Test SQL”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 NULLALTER TABLE ExampleTable5 ADD VerCol TimestampAnalysis Results
Section titled “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 |