SA0136 : Use fully qualified object names in SELECT, UPDATE, DELETE, MERGE and EXECUTE statements

In T-SQL code, not specifying fully qualified object names can lead to ambiguity, performance issues, and security risks. Fully qualifying object names means specifying the database, schema, and objectName. This practice ensures clarity and improves the efficiency of query execution.

For example:

1-- Without fully qualified name
2SELECT * FROM Employees;
3
4-- With fully qualified name
5SELECT * FROM HR.Employees;

The first query without a fully qualified name may cause the SQL Server to take extra time to resolve the correct object, leading to potential performance degradation. Specifying the schema (e.g., HR.Employees) helps avoid such delays and ensures the correct object is being accessed, even when objects with the same name exist in different schemas.

• Ambiguity in identifying the correct object, leading to potential runtime errors or incorrect data retrieval.

• Performance overhead due to SQL Server’s need to determine the correct object through name resolution.

• Security issues, as inadvertent access to incorrect objects may lead to exposure or modification of sensitive data.

Ensure that all object names in your T-SQL statements are fully qualified with the schema to avoid ambiguity, enhance performance, and strengthen security.

Follow these steps to address the issue:

1. Identify the T-SQL statements where object names are used without schema qualification. Common statements include SELECT, UPDATE, DELETE, INSERT, and EXECUTE.

2. Modify each unqualified object name by adding the appropriate schema name. This involves using the format schema_name.object_name. Ensure that you know the correct schema for each object.

3. Test the modified queries to ensure they work as expected and that they access the correct objects, especially if multiple objects with similar names exist in different schemas.

For example:

1-- Correct the object name by specifying the schema
2SELECT * FROM HR.Employees;
3-- Change UPDATE statement to use fully qualified object names
4UPDATE Sales.Orders 
5SET OrderStatus = 'Completed' 
6WHERE OrderID = 12345;

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

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

Design Rules, Code Smells

There is no additional info for this rule.

Other Resources