Click or drag to resize

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

The topic describes the SA0113 analysis rule.

Message

Do not use SET ROWCOUNT to restrict the number of rows

Description

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.

Scope

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

Parameters
NameDescriptionDefault Value
RuleSuppressMark

Suppress rule reporting a rule violation in case a comment containing the value of this parameter is found close to the source of violation.

IGNORE:SA0113

Remarks

The rule does not need Analysis Context or SQL Connection.

Categories

Design Rules

Additional Information

There is no additional info for this rule.

Example Test Script
 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

 MessageLineColumn
1SA0113 : Do not use SET ROWCOUNT to restrict the number of rows.114
See Also

Other Resources