SA0119 : Consider aliasing all table sources in the query

Avoid using unaliased table sources in SQL statements for better clarity and maintenance.

Description

It’s common practice to alias table sources in the FROM clause of SELECT, UPDATE, and DELETE statements. Not using aliases can lead to confusion and make the SQL code harder to read and maintain, especially in complex queries with multiple tables.

For example:

SQL
1-- Example of a query missing table aliases
2SELECT FirstName, LastName FROM Employees
3JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

This query does not use aliases, which can cause confusion, especially when multiple tables have similar column names or when queries grow in complexity. Best practices suggest using aliases to improve query readability and manageability.

  • Lack of aliases can make it difficult to track which columns belong to which table, increasing the chance of errors.

  • Using aliases can improve performance by making query plans more efficient and easier to optimize.

How to fix

Improve the readability and maintainability of SQL code by aliasing all table sources in your queries.

Follow these steps to address the issue:

  1. Identify all tables used in your FROM clause within SELECT, UPDATE, and DELETE statements.

  2. Assign an alias to each table. An alias is a shorthand reference that simplifies reading and writing queries. Use a meaningful alias for each table to make your SQL statements more intuitive.

  3. Replace all explicit table names in your query with their corresponding aliases. Ensure consistency across the query to maintain clarity.

For example:

SQL
1-- Example of a corrected query using table aliases
2SELECT E.FirstName, E.LastName
3FROM Employees AS E
4JOIN Departments AS D ON E.DepartmentID = D.DepartmentID;

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters
Name Description Default Value
IgnoreSingleTableSources

The queries accessing single tables will be ignored.

yes

IgnoreTableValuesFunctions

The not aliased table valued functions will be ignored.

yes

IgnoreSystemObjects

The not aliased system tables or views will be ignored.

yes

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix
5 minutes per issue.
Categories

Design Rules, Code Smells

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
 1SELECT * 
 2FROM Sales.Customer
 3INNER JOIN Sales.vStoreWithAddresses AS sa 
 4    ON CustomerID = sa.BusinessEntityID
 5WHERE TerritoryID = 5
 6
 7SELECT * 
 8FROM Sales.Customer /*IGNORE:SA0119*/
 9INNER JOIN Sales.vStoreWithAddresses AS sa 
10    ON CustomerID = sa.BusinessEntityID
11WHERE TerritoryID = 5
12
13SELECT *
14FROM HumanResources.Employee 
15UNION
16SELECT *
17FROM HumanResources.Employee 
18OPTION (MERGE UNION); 
19
20SELECT * FROM Sales.SalesOrderHeader WITH (NOLOCK)

Analysis Results
  Message Line Column
1 SA0119 : Table source does not have an alias. Consider aliasing all table sources in the query. 2 11
See Also

Other Resources