SA0203 : A deprecated system function is used
Introduction
Section titled “Introduction”The use of deprecated system functions in T-SQL code poses risks for future database compatibility and functionality, as they may be removed in later SQL Server versions.
Description
Section titled “Description”Using deprecated system functions can lead to issues when upgrading or maintaining databases. These functions are considered obsolete and are not recommended for use in new development efforts. They may be removed from SQL Server in future versions, leading to potential application failures.
For example:
-- Example using a deprecated system functionSELECT @@IDENTITY;The function @@IDENTITY retrieves the last identity value generated. However, it can cause incorrect results if triggers are involved. Instead, it is better to use SCOPE_IDENTITY() for more reliable outcomes.
-
Risk of application failure with future SQL Server releases.
-
Challenges in maintaining legacy code and ensuring forward compatibility.
How to fix
Section titled “How to fix”To ensure future compatibility and maintainability, replace deprecated system functions in T-SQL code with supported alternatives.
Follow these steps to address the issue:
1.Identify deprecated functions used in your T-SQL scripts. For example, consider the use of @@IDENTITY .
2.Consult the SQL Server documentation to find recommended alternatives for each deprecated function. For instance, use SCOPE_IDENTITY() instead of @@IDENTITY to get the last inserted identity value in the current scope.
3.Replace deprecated functions with their alternatives in your T-SQL code to avoid potential application failures and ensure better future compatibility.
For example:
-- Example of corrected query using SCOPE_IDENTITY instead of @@IDENTITYDECLARE @NewIdentityValue INT;INSERT INTO TableName (Column1) VALUES ('Data');SET @NewIdentityValue = SCOPE_IDENTITY();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 hours per issue.
Categories
Section titled “Categories”Deprecated Features, Bugs
Additional Information
Section titled “Additional Information”sys.fn_trace_geteventinfo (Transact-SQL)
sys.fn_trace_getfilterinfo (Transact-SQL)
sys.fn_trace_getinfo (Transact-SQL)
sys.fn_trace_gettable (Transact-SQL)
sys.fn_virtualservernodes (Transact-SQL)
sys.fn_servershareddrives (Transact-SQL)
Deprecated Database Engine Features in SQL Server 2017
Example Test SQL
Section titled “Example Test SQL”DECLARE @Handle varbinary(64);set @Handle = sql_handleSELECT @Handle = sql_handleFROM sys.dm_exec_requestsWHERE session_id = 52 and request_id = 0;SELECT * FROM sys.fn_get_sql(@Handle);
SELECT * FROM master.sys.fn_get_sql(@Handle);SELECT * FROM fn_get_sql(@Handle);Analysis Results
Section titled “Analysis Results”| Message | Line | Column | |
|---|---|---|---|
| 1 | SA0203 : A deprecated system function sys.fn_get_sql is used. | 6 | 18 |
| 2 | SA0203 : A deprecated system function sys.fn_get_sql is used. | 8 | 25 |
| 3 | SA0203 : A deprecated system function sys.fn_get_sql is used. | 9 | 15 |