SA0224 : The hint is deprecated for use on tables that are targets of INSERT statement

The topic describes the SA0224 analysis rule.

Message

The hint is deprecated for use on tables that are targets of INSERT statement

Description

The rule checks T-SQL code for usage of one of the table hints HOLDLOCK, SERIALIZABLE, READCOMMITTED, REPEATABLEREAD or UPDLOCK on the target tables of INSERT statements.

The ability to specify the HOLDLOCK, SERIALIZABLE, READCOMMITTED, REPEATABLEREAD, or UPDLOCK hints on tables that are targets of INSERT statements will be removed in a future version of SQL Server. These hints do not affect the performance of INSERT statements.

How to fix

Do not use the HOLDLOCK, SERIALIZABLE, READCOMMITTED, REPEATABLEREAD, or UPDLOCK hints in new development work.

If possible, modify code that currently use the deprecated hints. Otherwise use rule suppression comment to suppress the rule violation.

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
 1INSERT INTO Sales.SalesHistory WITH (HOLDLOCK, SERIALIZABLE, READCOMMITTED, REPEATABLEREAD, UPDLOCK)  
 2    (SalesOrderID,   
 3     SalesOrderDetailID,  
 4     CarrierTrackingNumber,   
 5     OrderQty,   
 6     ProductID,   
 7     SpecialOfferID,   
 8     UnitPrice,   
 9     UnitPriceDiscount,  
10     LineTotal,   
11     rowguid,   
12     ModifiedDate)  
13SELECT * FROM Sales.SalesOrderDetail;
14
15INSERT INTO Production.Location WITH (XLOCK)  
16(Name, CostRate, Availability)  
17VALUES ( N'Final Inventory', 15.00, 80.00);

Analysis Results
  Message Line Column
1 SA0224 : The HOLDLOCK hint is deprecated for use on tables that are targets of INSERT statement. 1 37
2 SA0224 : The SERIALIZABLE hint is deprecated for use on tables that are targets of INSERT statement. 1 47
3 SA0224 : The READCOMMITTED hint is deprecated for use on tables that are targets of INSERT statement. 1 61
4 SA0224 : The REPEATABLEREAD hint is deprecated for use on tables that are targets of INSERT statement. 1 76
5 SA0224 : The UPDLOCK hint is deprecated for use on tables that are targets of INSERT statement. 1 92
See Also

Other Resources