SA0203 : A deprecated system function is used

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

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:

SQL
Copy
1-- Example using a deprecated system function
2SELECT @@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

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:

SQL
Copy
1-- Example of corrected query using SCOPE_IDENTITY instead of @@IDENTITY
2DECLARE @NewIdentityValue INT;
3INSERT INTO TableName (Column1) VALUES ('Data');
4SET @NewIdentityValue = SCOPE_IDENTITY();

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 hours per issue.
Categories

Deprecated Features, Bugs

Additional Information
Example Test SQL
SQL
Copy
1DECLARE @Handle varbinary(64);  
2set @Handle = sql_handle   
3SELECT @Handle = sql_handle   
4FROM sys.dm_exec_requests   
5WHERE session_id = 52 and request_id = 0;  
6SELECT * FROM sys.fn_get_sql(@Handle);  
7
8SELECT * FROM master.sys.fn_get_sql(@Handle);  
9SELECT * FROM  fn_get_sql(@Handle);
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
See Also

Other Resources

Analysis Rules