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

Using certain table hints with INSERT statements is problematic as they will be deprecated in future SQL Server versions.

Description

When developing T-SQL scripts on SQL Server, it’s important to be aware that some table hints—HOLDLOCK, SERIALIZABLE, READCOMMITTED, REPEATABLEREAD, and UPDLOCK—are planned for removal when used with INSERT statements. These hints do not enhance performance outcomes or offer benefits within the context of INSERT operations.

For example:

SQL
Copy
1-- Example of a potentially problematic query
2INSERT INTO TableName WITH (HOLDLOCK) VALUES ('Value1');

In the example above, the table hint HOLDLOCK is applied unnecessarily, as it will neither impact nor improve the performance or outcome of the INSERT operation. Removal of such hints is recommended for future compatibility and to prevent any issues when these hints are deprecated.

  • Potential future compatibility issues due to deprecation.

  • Misleading assumption that these hints might affect the behavior or performance of the INSERT operation.

How to fix

Remove deprecated table hints from INSERT statements to ensure future compatibility and avoid misleading assumptions about their impact.

Follow these steps to address the issue:

  1. Identify the INSERT statements in your T-SQL code that use deprecated table hints such as HOLDLOCK, SERIALIZABLE, READCOMMITTED, REPEATABLEREAD, or UPDLOCK.

  2. Remove the deprecated table hints from the INSERT statements to prevent future compatibility issues.

  3. If modifying the code is not feasible, consider using a rule suppression comment to suppress the rule violation.

For example:

SQL
Copy
1-- Example of a corrected query without deprecated hints
2INSERT INTO TableName VALUES ('Value1');

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
 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

Analysis Rules