SA0021 : Deprecated usage of table hints without WITH keyword

Incorrect usage of table hints without the WITH keyword can cause errors in SQL Server.

Description

Uing table hints without the WITH keyword can lead to issues in query execution, especially in SQL Server 2005 and later versions. T-SQL requires the WITH keyword for specifying multiple table hints in a query, ensuring proper optimization and execution.

Example of a problematic query using hints without WITH:

SQL
Copy
1-- 
2SELECT * FROM TableName (NOLOCK, INDEX(IndexName));

This example is problematic because SQL Server will generate a syntax error. The correct approach is to use the WITH keyword to separate multiple hints, ensuring compatibility and proper hint application:

  • Not using the WITH keyword may result in syntax errors in SQL Server 2005 and higher.

  • Incorrect hint application can lead to unexpected query behavior or performance issues.

How to fix

Ensure that table hints in SQL queries are specified using the WITH keyword to avoid syntax errors and ensure proper hint application.

Follow these steps to address the issue:

  1. Identify the part of the query where table hints are used without the WITH keyword. These often follow table names directly.

  2. Modify the query to include the WITH keyword before opening the parentheses for the hints.

  3. Check for any additional hints and ensure they are within the parentheses, separated by commas.

Correct usage with the WITH keyword:

SQL
Copy
1SELECT * FROM TableName WITH (NOLOCK, INDEX(IndexName));

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
2 minutes per issue.
Categories

Design Rules, Deprecated Features, Bugs

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
Copy
 1SELECT  au_id
 2FROM  dbo.authors( UPDLOCK, PAGLOCK) -- Usage of the WITH keyword is recommended  
 3
 4SELECT  au_id
 5FROM  dbo.authors  WITH( UPDLOCK, PAGLOCK)
 6
 7SELECT  nolock.*
 8FROM  sys.objects as nolock
 9
10SELECT  *
11FROM  sys.objects nol
12
13SELECT  *
14FROM  sys.objects nolock
Analysis Results
  Message Line Column
1 SA0021 : Deprecated usage of table hints without WITH keyword. 2 19
2 SA0021 : Deprecated usage of table hints without WITH keyword. 2 28
3 SA0021 : Table hint is not enclosed in parentheses and will be considered as a table alias. 14 18
See Also

Other Resources

Analysis Rules