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

Introduction

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:

-- Example of problematic usage with SET ROWCOUNT
SET ROWCOUNT 5;
DELETE 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:

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

Or using FETCH NEXT:

-- Example using FETCH NEXT
DELETE FROM Employees
WHERE DepartmentId = 1
ORDER BY EmployeeId
OFFSET 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.

Additional Information

There is no additional info for this rule.

Example Test SQL

CREATE PROCEDURE mysp_RowCountTest
AS

SET ROWCOUNT 4; /*IGNORE:SA0113*/
SET NOCOUNT ON;

UPDATE Production.ProductInventory
SET Quantity = 400
WHERE Quantity < 300;

SET ROWCOUNT 5;

UPDATE Production.ProductInventory
SET Quantity = 400
WHERE Quantity < 300;

Analysis Results

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

Analysis Rules