@@ROWCOUNT

@@ROWCOUNT#

Returns the number of rows affected by the most recently completed statement in the current session. Querona evaluates @@ROWCOUNT in memory; it is never pushed down to a source.

Syntax#

@@ROWCOUNT

Return types#

int

Remarks#

The value set by a statement depends on its kind:

  • SELECT — the number of rows returned.

  • INSERT, UPDATE, DELETE — the number of rows affected.

  • An assignment (SET @x = ..., DECLARE @x int = ..., or SELECT @x = ... FROM ...) — 1 for a scalar assignment. See the limitation below for the multi-row assignment form.

  • USE, SET statements, DDL, TRUNCATE TABLE, BEGIN TRANSACTION, COMMIT and PRINT — reset @@ROWCOUNT to 0.

  • The condition of an IF or a WHILE resets @@ROWCOUNT to 0, independently of the statement it guards.

  • DECLARE @x int without an initializer, and a bare BEGIN ... END block, leave @@ROWCOUNT unchanged — they carry over whatever the preceding statement set.

Every statement that does set the value overwrites whatever was there before — including a read of @@ROWCOUNT itself. A SELECT @@ROWCOUNT returns one row, so it also sets @@ROWCOUNT to 1 for whatever reads it next; capture the value into a variable if it is needed more than once.

SET NOCOUNT ON suppresses only the rows-affected message sent to the client — it does not affect @@ROWCOUNT, which keeps reporting the true value either way. See SET NOCOUNT.

Note

Limitation — multi-row assignment SELECT. SELECT @x = SomeColumn FROM t is rejected when the query matches more than one row (“Subquery can return only one row but more rows were received from a subquery”). In SQL Server the same statement succeeds, assigns the last row’s value and sets @@ROWCOUNT to the number of rows scanned. Until this is supported, use an aggregate (SELECT @x = MAX(SomeColumn) FROM t) or SELECT TOP 1, both of which assign a single row and set @@ROWCOUNT to 1.

SET ROWCOUNT is an unrelated statement (a session-level row limit) despite the similar name; see SET ROWCOUNT.

For counts that can exceed the int range, use ROWCOUNT_BIG, which returns the same value as a bigint.

Warning

@@ROWCOUNT wraps rather than saturating when a statement affects more than 2 147 483 647 rows: the count is truncated to 32 bits, so a statement affecting 2 147 483 648 rows reports -2147483648 and no error is raised. A @@ROWCOUNT value can therefore be negative. This matches SQL Server. Use ROWCOUNT_BIG whenever a count that large is possible.

Examples#

Optimistic-concurrency check on a key- and version-qualified UPDATE:

UPDATE dbo.Customer
   SET Name = 'New name'
 WHERE CustomerId = 42 AND RowVersion = 0x00000000000007D1;

SELECT @@ROWCOUNT AS RowsUpdated;   -- 1 = saved; 0 = another session already changed the row

Batch sequencing — reading @@ROWCOUNT resets it for the next read:

INSERT INTO dbo.T VALUES (1), (2), (3);
SELECT @@ROWCOUNT;   -- 3
SELECT @@ROWCOUNT;   -- 1 (the previous SELECT itself reported one row)

See Also#