SA0113 : Do not use SET ROWCOUNT to restrict the number of rows

The use of SET ROWCOUNT in certain SQL operations leads to future compatibility issues and unexpected behavior in triggers.

Description

The problem focuses on the use of SET ROWCOUNT with INSERT, UPDATE, and DELETE statements in SQL Server. This feature is being phased out for these types of operations and will not be supported in future versions. Additionally, SET ROWCOUNT can create unpredictable outcomes when associated triggers are involved.

For example:

SQL
1-- Example of problematic usage with SET ROWCOUNT
2SET ROWCOUNT 5;
3DELETE FROM Employees WHERE DepartmentId = 1;

The example above illustrates a DELETE operation with SET ROWCOUNT. While the row limit is applied, it affects not only the DELETE operation but also any triggers fired due to this action, resulting in consistent row limits across subsequent trigger operations, which may not be the desired behavior.

  • Upcoming SQL Server versions will no longer support SET ROWCOUNT for modification operations, causing potential disruptions during updates.

  • Triggers initiated by these operations with SET ROWCOUNT experience the same row count limitation, potentially leading to logic errors or incomplete data processing.

How to fix

Replace SET ROWCOUNT with the TOP clause or the FETCH keyword to ensure future compatibility and avoid triggering issues in SQL operations.

Follow these steps to address the issue:

  1. Identify all SQL statements using the SET ROWCOUNT for INSERT, UPDATE, or DELETE operations.

  2. Replace SET ROWCOUNT with the TOP clause in your SQL queries to limit the number of rows affected. Ensure any triggers also consider the changed logic if necessary.

  3. Alternatively, use the FETCH NEXT clause with OFFSET if you are operating on SQL Server 2012 or later. This provides more flexibility in handling data pagination and limits.

  4. Update tests and triggers to validate that the new logic behaves as expected without the limitations of SET ROWCOUNT.

For example:

SQL
1-- Example of corrected query using TOP clause
2DELETE TOP (5) FROM Employees WHERE DepartmentId = 1;

Or using FETCH NEXT:

SQL
1-- Example using FETCH NEXT
2DELETE FROM Employees 
3WHERE DepartmentId = 1 
4ORDER BY EmployeeId 
5OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY;

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

Design Rules, Deprecated Features, Bugs

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
 1CREATE PROCEDURE mysp_RowCountTest
 2AS
 3
 4SET ROWCOUNT 4; /*IGNORE:SA0113*/
 5SET NOCOUNT ON;
 6
 7UPDATE Production.ProductInventory
 8SET Quantity = 400
 9WHERE Quantity < 300;
10
11SET ROWCOUNT 5;
12
13UPDATE Production.ProductInventory
14SET Quantity = 400
15WHERE Quantity < 300;

Analysis Results
  Message Line Column
1 SA0113 : Do not use SET ROWCOUNT to restrict the number of rows. 11 4
See Also

Other Resources