SA0101 : Avoid using hints to force a particular behavior

The topic describes the SA0101 analysis rule.

Message

Avoid using hints to force a particular behavior

Description

The rule checks for usage of query hints, table hints or join hints in the SELECT, UPDATE, DELETE, MERGE and INSERT statements.

Because the SQL Server query optimizer typically selects the best execution plan for a query, it is recommended to be use hints only as a last resort by experienced developers and database administrators.

Note Note

The rule does not report when a NOLOCK hint is found, as it is done by rule SA0108.
How to fix

Avoid using hints in SQL queries.

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
3 hours per issue.
Categories

Design Rules, Bugs

Additional Information
Example Test SQL
SQL
Copy
 1--- Query Hints ---
 2SELECT * 
 3FROM Sales.Customer AS c
 4INNER JOIN Sales.vStoreWithAddresses AS sa 
 5    ON c.CustomerID = sa.BusinessEntityID
 6WHERE TerritoryID = 5
 7OPTION (MERGE JOIN);
 8
 9-- Join Hints
10SELECT p.Name, pr.ProductReviewID
11FROM Production.Product p
12LEFT OUTER HASH JOIN Production.ProductReview pr
13ON p.ProductID = pr.ProductID
14ORDER BY ProductReviewID DESC;
15
16--- Table Hints ---
17SELECT *
18FROM Sales.SalesOrderHeader AS h
19INNER JOIN Sales.SalesOrderDetail AS d WITH (FORCESEEK,NOLOCK) /*IGNORE:SA0101(line)*/
20    ON h.SalesOrderID = d.SalesOrderID 
21WHERE h.TotalDue > 100
22AND (d.OrderQty > 5 OR d.LineTotal < 1000.00);
23
24UPDATE Production.Product WITH (TABLOCK)
25SET ListPrice = ListPrice * 1.10
26WHERE ProductNumber LIKE 'BK-%';
Analysis Results
  Message Line Column
1 SA0101 : Avoid using hints to force a particular behavior. 7 0
2 SA0101 : Avoid using hints to force a particular behavior. 12 11
3 SA0101 : Avoid using hints to force a particular behavior. 24 32
See Also

Other Resources

Analysis Rules