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

Do not use SET ROWCOUNT to restrict the number of rows

The rule checks for usage of the SET ROWCOUNT setting.

It is recommended to use the TOP clause or the new in SQL 2012 FETCH keyword instead of SET ROWCOUNT as it will not be supported in the future versions of SQL Server for INSERT,UPDATE and DELETE statements.

In addition to that is being phased out, the SET ROWCOUNT has another problem – when a ROWCOUNT is set and there is INSERT, UPDATE, DELETE or MERGE statements which fire a trigger, all the statements in the trigger will have the same row limit applied.

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.

 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

Other Resources