SA0119 : Consider aliasing all table sources in the query

Introduction

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:

-- Example of a query missing table aliases
SELECT FirstName, LastName FROM Employees
JOIN 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:

-- Example of a corrected query using table aliases
SELECT E.FirstName, E.LastName
FROM Employees AS E
JOIN 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.

Additional Information

There is no additional info for this rule.

Example Test SQL

SELECT *
FROM Sales.Customer
INNER JOIN Sales.vStoreWithAddresses AS sa
    ON CustomerID = sa.BusinessEntityID
WHERE TerritoryID = 5

SELECT *
FROM Sales.Customer /*IGNORE:SA0119*/
INNER JOIN Sales.vStoreWithAddresses AS sa
    ON CustomerID = sa.BusinessEntityID
WHERE TerritoryID = 5

SELECT *
FROM HumanResources.Employee
UNION
SELECT *
FROM HumanResources.Employee
OPTION (MERGE UNION);

SELECT * 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

Analysis Rules