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

Introduction

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:

-- Problematic query example
UPDATE TargetTable SET Column1 = 'Value'
FROM 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:

-- Corrected query without NOLOCK hint
UPDATE TargetTable SET Column1 = 'Value'
FROM 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.

Additional Information

Deprecated Database Engine Features in SQL Server 2017

Example Test SQL

UPDATE Production.Product WITH (TABLOCK, NOLOCK)
SET ListPrice = ListPrice * 1.10
WHERE ProductNumber LIKE 'BK-%';

DELETE FROM Production.Product WITH (NOLOCK)
WHERE ListPrice > 1000;

DELETE FROM Production.Product
FROM Production.Product p  WITH (NOLOCK)
INNER JOIN
Sales.ProductSales p2 ON p.ProductId = p2.ProductId
WHERE 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

Analysis Rules