SA0158 : Deprecated usage of space as separator for table hints. Use a comma instead of space

Using a space as a separator for table hints can lead to SQL parsing errors and unintended query behavior.

Description

In T-SQL code, table hints are used to direct the query optimizer on how to process certain tables in a query. However, using an incorrect separator, such as a space, between table hints can cause SQL Server to misinterpret the intended hints. This can result in syntax errors or the database ignoring some table hints, leading to unexpected query results.

For example:

SQL
Copy
1-- Example of problematic query using space as a separator
2SELECT * FROM TableName WITH (NOLOCK INDEX = IX_IndexName);

In this example, using a space between NOLOCK and INDEX is incorrect. SQL Server expects a comma to separate multiple hints, leading to potential parsing issues or ignored hints.

  • Potential syntax errors if incorrect separation is used, impacting query execution.

  • Risk of hints being ignored, affecting performance and consistency in query results.

How to fix

Ensure table hints are specified using the WITH keyword and commas as separators to avoid parsing errors and ensure expected behavior.

Follow these steps to address the issue:

  1. Identify the query where table hints are used with spaces as separators instead of commas.

  2. Replace spaces between multiple table hints with commas to correct the syntax.

  3. Ensure that the table hints follow the WITH keyword and are enclosed in parentheses.

  4. Review and test the query to confirm that the hints are correctly applied and the query executes as expected.

For example:

SQL
Copy
1-- Corrected query using commas between hints
2SELECT * FROM TableName WITH (NOLOCK, INDEX = IX_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
Example Test SQL
SQL
Copy
1SELECT     au_id
2FROM       dbo.authors with ( UPDLOCK ,PAGLOCK ) -- Usage of the WITH keyword is recommended
3
4SELECT     au_id
5FROM       dbo.authors  WITH( UPDLOCK PAGLOCK )
6
7
8INSERT INTO Person WITH (TABLOCK, HOLDLOCK)  (FirstName) 
9VALUES ('Bruce');
Analysis Results
  Message Line Column
1 SA0158 : Deprecated usage of space as separator for table hints. Use a comma instead of space. 5 30
2 SA0158 : Deprecated usage of space as separator for table hints. Use a comma instead of space. 5 38
See Also

Other Resources

Analysis Rules