SA0219 : A deprecated build-in function is used

Introduction

The use of deprecated built-in functions in T-SQL code can cause compatibility issues in future SQL Server versions.

Description

When developing or maintaining SQL Server databases, it’s important to avoid using deprecated built-in functions. These functions, such as FILE_ID , INDEXKEY_PROPERTY , USER_ID , DATABASEPROPERTY , and PERMISSIONS , are in maintenance mode. This means they may be removed or replaced in future SQL Server versions, potentially breaking existing code.

For example:

-- Example of deprecated function usage
SELECT FILE_ID('SampleFileName');

This example uses the FILE_ID function, which has been replaced by FILE_IDEX . Continuing to use deprecated functions like this can lead to:

Unexpected errors or application failures when upgrading to newer SQL Server versions.
Increased maintenance burden as deprecated functions may not receive updates or improvements.

How to fix

To avoid compatibility issues in future SQL Server versions, replace deprecated built-in functions with their modern equivalents.

Follow these steps to address the issue:

1.Identify all deprecated functions used in your queries, such as FILE_ID , INDEXKEY_PROPERTY , USER_ID , DATABASEPROPERTY , and PERMISSIONS .

2.Check the official SQL Server documentation for each deprecated function to find the recommended alternative.

3.Replace the deprecated function in your code with its replacement. For example, replace FILE_ID with FILE_IDEX .

4.Test the updated queries to ensure they produce the expected results and perform well.

5.Plan to refactor your codebase to remove any remaining deprecated functions during regular maintenance cycles.

For example:

-- Example of corrected query using FILE_IDEX instead of FILE_ID
SELECT FILE_IDEX('SampleFileName');

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

20 minutes per issue.

Additional Information

FILE_ID (Transact-SQL)

INDEXKEY_PROPERTY (Transact-SQL)

DATABASEPROPERTY (Transact-SQL)

PERMISSIONS (Transact-SQL)

Deprecated Database Engine Features in SQL Server 2017

Example Test SQL

SELECT FILE_ID('AdventureWorks2012_Data')AS 'File ID';

SELECT
    INDEXKEY_PROPERTY(OBJECT_ID('Production.Location', 'U'),
        1,1,'ColumnId') AS [Column ID],
    INDEXKEY_PROPERTY(OBJECT_ID('Production.Location', 'U'),
        1,1,'IsDescending') AS [Asc or Desc order];

SELECT USER_ID('Harold');

Analysis Results

	Message	Line	Column
1	SA0219 : A deprecated build-in function FILE_ID is used.	1	7
2	SA0219 : A deprecated build-in function INDEXKEY_PROPERTY is used.	4	4
3	SA0219 : A deprecated build-in function INDEXKEY_PROPERTY is used.	6	4
4	SA0219 : A deprecated build-in function USER_ID is used.	9	7

Analysis Rules