Skip to content

SA0082 : Consider prefixing column names with table name or table alias

Ensure columns are properly prefixed to prevent ambiguity and enhance query clarity.

When referencing columns without being explicit about their origin can lead to confusing results or errors. This problem is particularly common in queries involving multiple tables in operations such as SELECT , UPDATE , and DELETE . Ensuring columns are properly prefixed can prevent ambiguity and enhance query clarity.

For example:

-- Example of a problematic query with unclear column references
SELECT Name, City
FROM Employees e
JOIN Locations l ON e.LocationID = l.ID;

In the example above, it’s unclear to which table the columns Name and City belong. This can result in errors if these columns exist in both tables, leading to unpredictable results.

  • Ambiguous column references can cause errors if identical column names exist in multiple tables.

  • Using non-existent table aliases or not associating column references with the correct tables can lead to incorrect query outcomes or execution failures.

Prefix column names with the table name or alias to improve readability and avoid ambiguity in SQL queries.

Follow these steps to address the issue:

1.Identify all columns in your query that are referenced without a prefix, especially in SELECT , UPDATE , or DELETE operations involving multiple tables.

2.Determine the origin of each column by referring to the tables or aliases used in the query. Assign a clear and distinct alias to each table, if not already done.

3.Replace ambiguous column references with prefixed ones, using the table name or alias followed by a period and the column name (e.g., e.Name ).

4.Review the query to ensure all columns have explicit references and there are no potential conflicts between column names.

For example:

-- Revised query with clear column references
SELECT e.Name, l.City
FROM Employees e
JOIN Locations l ON e.LocationID = l.ID;

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

Name Description Default Value
IgnoreSingleTableSources Ignore non aliased columns in statements which reference single table source. yes
CheckAliasedColumnExists If parameter value is set to ‘yes’, the aliased columns will be checked for existing in the parent table. yes

The rule requires Analysis Context. If context is missing, the rule will be skipped during analysis.

5 minutes per issue.

Design Rules, Code Smells

There is no additional info for this rule.

SELECT p.[Title]
, [FirstName]
, [MiddleName]
, [LastName]
, [Suffix]
, [AddressLine1]
FROM Person AS p
INNER JOIN Address AS a
ON p.AddressId = a.Id
DELETE FROM edh
FROM HumanResources.EmployeeDepartmentHistory AS edh
WHERE ShiftID = 5 AND
edh.StartDate IN( '2010-05-22', '2010-05-22' ) AND
DepartmentID LIKE '43 '
  Message Line Column
1 SA0082 : The column [FirstName] is not prefixed with table alias name. 2 8
2 SA0082 : The column [MiddleName] is not prefixed with table alias name. 3 8
3 SA0082 : The column [LastName] is not prefixed with table alias name. 4 8
4 SA0082 : The column [Suffix] is not prefixed with table alias name. 5 8
5 SA0082 : The column [AddressLine1] is not prefixed with table alias name. 6 8
6 SA0082 : The column [Id] is missing in the table aliased with [a]. 9 18
7 SA0082 : The column [ShiftID] is not prefixed with table alias name. 13 7
8 SA0082 : The column [DepartmentID] is not prefixed with table alias name. 15 7

Analysis Rules