Click or drag to resize

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

The topic describes the SA0225 analysis rule.

Message

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

Description

The rule checks for usage of NOLOCK and READUNCOMMITTED table hints in the FROM clause of UPDATE, DELETE or MERGE statements.

The NOLOCK and READUNCOMMITTED table hints are deprecated for usage on the target table in the FROM clause of UPDATE, DELETE and MERGE statements.

NOTE: The READUNCOMMITTED table hint produces syntax error when used with UPDATE, DELETE and MERGE statements. The NOLOCK hint produces syntax error with MERGE statement.

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.

Categories

New Rules, Deprecated Features

Additional Information
Example Test Script
SQL
 1UPDATE Production.Product WITH (TABLOCK, NOLOCK) -- READUNCOMMITTED not syntatically valid
 2SET ListPrice = ListPrice * 1.10
 3WHERE ProductNumber LIKE 'BK-%';
 4
 5DELETE FROM Production.Product WITH (NOLOCK)  -- READUNCOMMITTED not syntatically valid
 6WHERE ListPrice > 1000;
 7
 8
 9MERGE INTO Sales.SalesReason WITH (XLOCK)  AS Target  -- NOLOCK and READUNCOMMITTED are not syntatically valid
10USING (VALUES ('Recommendation','Other'), ('Review', 'Marketing'), 
11              ('Internet', 'Promotion'))  
12       AS Source (NewName, NewReasonType)  
13ON Target.Name = Source.NewName  
14WHEN MATCHED THEN  
15UPDATE SET ReasonType = Source.NewReasonType  
16WHEN NOT MATCHED BY TARGET THEN  
17INSERT (Name, ReasonType) VALUES (NewName, NewReasonType)  
18 OUTPUT $action INTO #SummaryOfChanges;

Analysis Results

 MessageLineColumn
1SA0225 : The NOLOCK is deprecated for use on the target table of UPDATE, DELETE and MERGE statements.241
2SA0225 : The NOLOCK is deprecated for use on the target table of UPDATE, DELETE and MERGE statements.637
See Also

Other Resources