SA0169 : Use @@ROWCOUNT only after SELECT, INSERT, UPDATE, DELETE or MERGE statements |
![]() |
Ensure proper usage of @@ROWCOUNT to accurately track affected rows.

In T-SQL code, @@ROWCOUNT is a system function used to capture the number of rows affected by the last executed SELECT, INSERT, UPDATE, DELETE, or MERGE statement. Using it incorrectly can lead to inaccurate results, especially when not immediately following one of these statements.
For example:
1-- Problematic use of @@ROWCOUNT 2SELECT * FROM Employees; 3-- Some other statements 4PRINT @@ROWCOUNT;
In this example, @@ROWCOUNT may not deliver the expected number of rows affected by the SELECT query, as its value might have changed or been preserved from unrelated operations.
-
Incorrect row count can lead to inaccurate program logic or misleading information displayed to users or used in further processing steps.
-
Preserved values could cause confusion and bugs if assumptions are made about the row count in subsequent logic.

Ensure the accurate use of @@ROWCOUNT to capture the correct number of rows affected by SQL statements.
Follow these steps to address the issue:
-
Immediately use @@ROWCOUNT after the SELECT, INSERT, UPDATE, DELETE, or MERGE statement to capture the precise number of affected rows.
-
Avoid executing other statements between the SQL operation and the @@ROWCOUNT call, as it could alter the value or cause confusion.
-
Consider storing the value of @@ROWCOUNT in a variable if you need to reference it later in your code.
For example:
1-- Correct use of @@ROWCOUNT 2SELECT * FROM Employees; 3PRINT @@ROWCOUNT; 4 5-- Storing @@ROWCOUNT in a variable 6UPDATE Employees SET Salary = Salary * 1.1; 7DECLARE @AffectedRows INT; 8SET @AffectedRows = @@ROWCOUNT; 9PRINT @AffectedRows;

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

Name | Description | Default Value |
---|---|---|
IgnoreRowcountInFirstSatementInsideTrigger |
The parameter specifies whether or not to ignore the @@ROWCOUNT when found inside the first statement of a trigger. |
yes |

The rule does not need Analysis Context or SQL Connection.


Design Rules, Bugs


SQL
1CREATE TABLE Test.Greeting 2( 3 GreetingId INT IDENTITY (1,1) PRIMARY KEY, 4 Message nvarchar(255) NOT NULL, 5) 6PRINT @@ROWCOUNT 7 8INSERT INTO Test.Greeting (Message) 9VALUES ('How do yo do?'), 10 ('Good morning!'), 11 ('Good night!') 12PRINT @@ROWCOUNT 13 14DELETE Test.Greeting WHERE GreetingId = 3 15PRINT @@ROWCOUNT |

Message | Line | Column | |
---|---|---|---|
1 | SA0169 : Use @@ROWCOUNT only after SELECT, INSERT, UPDATE, DELETE or MERGE statements. | 6 | 6 |
