SA0225 : The usage of the NOLOCK and READUNCOMMITTED table hints is are deprecated in UPDATE, DELETE and MERGE statements

Using NOLOCK or READUNCOMMITTED table hints in FROM clauses of UPDATE, DELETE, or MERGE statements is deprecated and can lead to syntax errors and serious data integrity issues.

Description

When developers use the NOLOCK or READUNCOMMITTED table hints in the FROM clause of UPDATE, DELETE, or MERGE statements, it can cause significant issues. These hints, which allow reading uncommitted changes, are not suited for modifying operations in SQL Server.

For example:

SQL
Copy
1-- Problematic query example
2UPDATE TargetTable SET Column1 = 'Value'
3FROM SourceTable WITH (NOLOCK);

This example may cause syntax errors because using NOLOCK or READUNCOMMITTED in such contexts is not supported and can prevent the statement from executing correctly.

  • Potential data inconsistency due to reading uncommitted data.

  • Execution failures when using deprecated practices, particularly with MERGE statements which will result in syntax errors.

How to fix

To resolve issues identified by rule sa0225, remove the inappropriate table hints NOLOCK or READUNCOMMITTED from UPDATE, DELETE, or MERGE statements.

Follow these steps to address the issue:

  1. Identify UPDATE, DELETE, or MERGE statements that include NOLOCK or READUNCOMMITTED in their FROM clause.

  2. Remove the NOLOCK or READUNCOMMITTED table hints to prevent syntax errors and ensure the integrity of your SQL operations.

  3. Replace improper table hints with suitable transaction isolation levels if isolation is required for read operations.

For example, modify the problematic query:

SQL
Copy
1-- Corrected query without NOLOCK hint
2UPDATE TargetTable SET Column1 = 'Value'
3FROM SourceTable;

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

Deprecated Features, Bugs

Additional Information
Example Test SQL
SQL
Copy
 1UPDATE Production.Product WITH (TABLOCK, NOLOCK)
 2SET ListPrice = ListPrice * 1.10
 3WHERE ProductNumber LIKE 'BK-%';
 4
 5DELETE FROM Production.Product WITH (NOLOCK) 
 6WHERE ListPrice > 1000;
 7
 8DELETE FROM Production.Product 
 9FROM Production.Product p  WITH (NOLOCK) 
10INNER JOIN 
11Sales.ProductSales p2 ON p.ProductId = p2.ProductId
12WHERE ListPrice > 1000;
Analysis Results
  Message Line Column
1 SA0225 : The NOLOCK is deprecated for use on the target table of UPDATE, DELETE and MERGE statements. 1 41
2 SA0225 : The NOLOCK is deprecated for use on the target table of UPDATE, DELETE and MERGE statements. 5 37
3 SA0225 : The NOLOCK is deprecated for use on the target table of UPDATE, DELETE and MERGE statements. 9 33
See Also

Other Resources

Analysis Rules