MSSQL @@ROWCOUNT Tutorial
The @@ROW COUNT variable returns the number of rows read by the last executed statement. If any statement does not return any rows, then value of @@ROWCOUNT variable is set to zero.
MSSQL @@ROWCOUNT VARIABLE SYNTAX
@@ROWCOUNT
Using @@ implies that it is a global variable. Also @@ROWCOUNT returns the value of int type i.e. the maximum no of rows @@ ROWCOUNT can return is 231 (2,147,483,647). For returning rows greater than this limit, ROWCOUNT_BIG function is used.
MSSQL @@ROWCOUNT STATEMENTS
SQL statements can set the value in @@ROWCOUNT in the following ways:
- 1. Set @@ROWCOUNT to the number of rows affected or read. Rows may or may not be sent to the client.
- 2. Preserve @@ROWCOUNT from the previous statement execution.
- 3. Reset @@ROWCOUNT to 0 but do not return the value to the client.
Below is an example of @@ROW COUNT function from “Employee” table:
MSSQL @@ROW COUNT Example
SELECT * FROM Employee
SELECT @@ROWCOUNT
SELECT @@ROWCOUNT
Using Database table “Employee”
| Employee ID | Employee Name | Age | Gender | Location | Salary |
| 1001 | Henry | 54 | Male | New York | 100000 |
| 1002 | Tina | 36 | Female | Moscow | 80000 |
| 1003 | John | 24 | Male | London | 40000 |
| 1004 | Mile | 31 | Male | London | 70000 |
| 1005 | Tara | 26 | Female | Moscow | 50000 |
| 1006 | Sophie | 29 | Female | London | 60000 |
MSSQL @@ROWCOUNT Results
MSSQL @@ROWCOUNT output from the “Employee” table would be
6
1
The first @@ROWCOUNT returns the number of rows read by “Select * From Employee” statement 1.e. 6. While the Second @@ROWCOUNT returns the number of rows read by first “SELECT @@ROWCOUNT” Statement i.e. 1.
