SA0157 : Usage of three and four part column names is deprecated. Two-part names is the standard-compliant behavior

Introduction

Deprecated multi-part column names in SQL queries are a common issue that can lead to deprecated features usage and potential migration challenges.

Description

Using three- or four-part column names in T-SQL queries is deprecated in SQL Server. This approach can cause difficulties in maintaining and migrating databases, as it relies on features that might be removed in future SQL Server versions.

For example:

-- Example of deprecated multi-part column name usage
SELECT ServerName.DatabaseName.SchemaName.TableName.ColumnName FROM TableName;

Using this type of naming in your queries can lead to complications because:

It ties your query to specific server and database environments, making it less portable and flexible.
There is a risk of unexpected behavior or errors in future SQL Server releases if these deprecated features are removed.

How to fix

Change the query to use standard-compliant two-part names to avoid deprecated multi-part column names.

Follow these steps to address the issue:

1.Identify queries using multi-part column names. Look for patterns in the format ServerName.DatabaseName.SchemaName.TableName.ColumnName .

2.Modify the queries to use standard two-part names, consisting of the schema and table identifiers. Remove the server and database identifiers.

3.Verify the revised queries for any dependency on server or database-specific logic and refactor as necessary to maintain functionality.

4.Test the updated queries in your environment to ensure they produce the same results and do not cause any errors.

For example:

-- Example of corrected query using two-part names
SELECT SchemaName.TableName.ColumnName FROM SchemaName.TableName;

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

5 minutes per issue.

Additional Information

Deprecated Database Engine Features

Discontinued Database Engine Functionality

Example Test SQL

SELECT
       *,Database1..Table4.Column1
FROM
     ..Table1,
     Schema1.Table2, Database1..Table3
     INNER JOIN Database1..Table4
        ON Database1..Table4.Column1=Database1..Table3.Column1 AND
           Database1..Table4.Column2= Schema1.Table1.Column2
     INNER JOIN Database1.Schema2.Table4 ON
           Database1..Table2.Column1=Database1..Table3.Column1 AND
           Database1..Table2.Column2=Database1.Schema1.Table1.Column2
WHERE
      Database1.Schema1.Table1.Column1= Schema1.Table2.Column1 AND
      Database1.Schema1.Table3.Column1=Database1.Schema1.Table2.Column3  /*IGNORE:SA0157(LINE)*/

Analysis Results

	Message	Line	Column
1	SA0157 : Usage of three and four part column names is deprecated. Two-part names is the standard-compliant behavior.	2	9
2	SA0157 : Usage of three and four part column names is deprecated. Two-part names is the standard-compliant behavior.	7	11
3	SA0157 : Usage of three and four part column names is deprecated. Two-part names is the standard-compliant behavior.	7	37
4	SA0157 : Usage of three and four part column names is deprecated. Two-part names is the standard-compliant behavior.	8	11
5	SA0157 : Usage of three and four part column names is deprecated. Two-part names is the standard-compliant behavior.	8	38
6	SA0157 : Usage of three and four part column names is deprecated. Two-part names is the standard-compliant behavior.	10	11
7	SA0157 : Usage of three and four part column names is deprecated. Two-part names is the standard-compliant behavior.	10	37
8	SA0157 : Usage of three and four part column names is deprecated. Two-part names is the standard-compliant behavior.	11	11
9	SA0157 : Usage of three and four part column names is deprecated. Two-part names is the standard-compliant behavior.	11	37
10	SA0157 : Usage of three and four part column names is deprecated. Two-part names is the standard-compliant behavior.	13	6
	…

Analysis Rules