SA0226 : The usage of ROWGUIDCOL and IDENTITYCOL as column name in DML statements is deprecated
Introduction
Section titled “Introduction”Deprecated usage of ROWGUIDCOL and IDENTITYCOL in T-SQL code can lead to maintenance challenges and compatibility issues with future SQL Server versions.
Description
Section titled “Description”In SQL Server, T-SQL queries that incorporate the use of ROWGUIDCOL and IDENTITYCOL may encounter issues because these names are deprecated. It is recommended to employ $identity and $rowguid instead to future-proof your code and avoid potential inefficiencies.
For example:
-- Deprecated usageSELECT ROWGUIDCOL, IDENTITYCOL FROM MyTable;This query uses deprecated column references, which can lead to compatibility issues and are not as well supported in future SQL Server releases. It is considered a best practice to use the recommended alternatives to ensure compatibility and maintainability of your database code.
-
Outdated references hinder database portability and compatibility with future SQL Server versions.
-
Continuing to use deprecated syntax may lead to performance inefficiencies or limited database functionality.
How to fix
Section titled “How to fix”To address the deprecated usage of ROWGUIDCOL and IDENTITYCOL in T-SQL queries, replace them with $rowguid and $identity respectively. This change ensures future compatibility and optimal performance.
Follow these steps to address the issue:
1.Identify queries using ROWGUIDCOL and IDENTITYCOL in your database scripts. You can do this by searching for occurrences of these terms in your codebase.
2.Replace each instance of ROWGUIDCOL with $rowguid to conform to best practices and supported syntax.
3.Similarly, replace each instance of IDENTITYCOL with $identity to ensure compatibility with future SQL Server versions.
4.Run the updated queries to verify that they behave as expected, ensuring that the replacements have been successful.
For example:
-- Updated query using recommended syntaxSELECT $rowguid, $identity FROM MyTable;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”declare @table as table(id uniqueidentifier ROWGUIDCOL, id2 int IDENTITY(1,1))
select rowguidcol, identitycol from @table
select $rowguid, $identity from @tableAnalysis Results
Section titled “Analysis Results”| Message | Line | Column | |
|---|---|---|---|
| 1 | SA0226 : The use of ROWGUIDCOL as column name in DML statements is deprecated. Use $rowguid instead. | 3 | 7 |
| 2 | SA0226 : The use of IDENTITYCOL as column name in DML statements is deprecated. Use $identity instead. | 3 | 19 |