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

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:

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:

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.

Other Resources