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

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:

SQL
Copy
1-- Example of deprecated multi-part column name usage
2SELECT 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:

SQL
Copy
1-- Example of corrected query using two-part names
2SELECT 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.
Categories

Design Rules, Naming Rules, Deprecated Features, Code Smells

Additional Information
Example Test SQL
SQL
Copy
 1SELECT 
 2       *,Database1..Table4.Column1
 3FROM 
 4     ..Table1,
 5     Schema1.Table2, Database1..Table3
 6     INNER JOIN Database1..Table4   
 7        ON Database1..Table4.Column1=Database1..Table3.Column1 AND 
 8           Database1..Table4.Column2= Schema1.Table1.Column2
 9     INNER JOIN Database1.Schema2.Table4 ON 
10           Database1..Table2.Column1=Database1..Table3.Column1 AND 
11           Database1..Table2.Column2=Database1.Schema1.Table1.Column2
12WHERE 
13      Database1.Schema1.Table1.Column1= Schema1.Table2.Column1 AND 
14      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
…
See Also

Other Resources

Analysis Rules