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.

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

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:
-
Identify all SQL statements using the SET ROWCOUNT for INSERT, UPDATE, or DELETE operations.
-
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.
-
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.
-
Update tests and triggers to validate that the new logic behaves as expected without the limitations of SET ROWCOUNT.
For example:
1-- Example of corrected query using TOP clause 2DELETE TOP (5) FROM Employees WHERE DepartmentId = 1;
Or using FETCH NEXT:
1-- Example using FETCH NEXT 2DELETE FROM Employees 3WHERE DepartmentId = 1 4ORDER BY EmployeeId 5OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY;

The rule has a Batch scope and is applied only on the SQL script.

Rule has no parameters.

The rule does not need Analysis Context or SQL Connection.


Design Rules, Deprecated Features, Bugs

There is no additional info for this rule.

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

Message | Line | Column | |
---|---|---|---|
1 | SA0113 : Do not use SET ROWCOUNT to restrict the number of rows. | 11 | 4 |
