SA0021 : Deprecated usage of table hints without WITH keyword

Introduction

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:

--
SELECT * 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:

  SELECT * 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.

Additional Information

There is no additional info for this rule.

Example Test SQL

SELECT  au_id
FROM  dbo.authors( UPDLOCK, PAGLOCK) -- Usage of the WITH keyword is recommended

SELECT  au_id
FROM  dbo.authors  WITH( UPDLOCK, PAGLOCK)

SELECT  nolock.*
FROM  sys.objects as nolock

SELECT  *
FROM  sys.objects nol

SELECT  *
FROM  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

Analysis Rules