Click or drag to resize

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.

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.

Categories

Design Rules

Additional Information
Example Test Script
SQL
 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

 MessageLineColumn
1SA0101 : Avoid using Hints to force a particular behavior.70
2SA0101 : Avoid using Hints to force a particular behavior.1211
3SA0101 : Avoid using Hints to force a particular behavior.2432
See Also

Other Resources