What is @@rowcount with MSSQL
- @@ROWCOUNT returns the number of rows affected or read depending on the operation.
- @@ROWCOUNT returns the number of rows returned to the user for a SELECT.
- @@ROWCOUNT returns the number of rows affected after issuing a INSERT, DELETE, or UPDATE.
- '@@ROWCOUNT returns the number of rows affected after a previous statement execution.
- DECLARE CURSOR and FETCH set the @@ROWCOUNT value to 1.
- EXECUTE preserves the previous @@ROWCOUNT.
Data used
USE sql911;
go
SELECT sales_date,sales_qty,sales_price,emp_short
FROM sales;
go
go
SELECT sales_date,sales_qty,sales_price,emp_short
FROM sales;
go
sales_date sales_qty sales_price emp_short ----------------------- --------------------------------------- --------------------------------------- ---------- 2005-10-06 10:01:01.000 10.00 11.75 000310 2006-10-06 10:01:01.000 10.00 11.75 000310 2005-11-06 10:01:01.000 9.00 11.75 000110 2006-10-06 10:01:01.000 10.00 11.75 000310 2005-12-06 11:01:01.000 8.00 11.75 000010 2006-02-06 13:01:01.000 NULL NULL 000190 2006-03-06 14:01:01.000 5.00 11.75 000110 2006-10-06 10:01:01.000 10.00 11.75 000190 2006-10-06 10:01:01.000 10.00 11.75 000190 2006-04-06 15:01:01.000 4.00 11.75 000030 2006-05-06 16:01:01.000 3.00 11.75 000010 2004-10-06 10:01:01.000 NULL NULL 000310 2005-11-06 10:01:01.000 9.00 11.75 000010 2006-06-06 17:01:01.000 2.00 11.75 000190 2006-01-06 12:01:01.000 7.00 11.75 000310 2006-02-06 13:01:01.000 6.00 11.75 000110 2005-03-06 14:01:01.000 5.00 11.75 000190 2006-10-06 10:01:01.000 10.00 11.75 000110 2006-10-06 10:01:01.000 3.00 11.75 000110 2007-06-06 17:01:01.000 2.00 11.75 000010 2007-04-06 00:00:00.000 4.00 11.75 000010 2007-05-01 00:00:00.000 5.00 11.75 000030 2007-07-06 00:00:00.000 1.00 11.75 000190 2007-05-03 09:00:00.000 3.00 11.75 000010 2007-05-05 04:00:00.000 1.00 11.75 000010 2006-10-06 10:01:01.000 10.00 11.75 000030 2004-03-06 14:01:01.000 NULL NULL 000110 2005-04-06 15:01:01.000 4.00 11.75 000010 2005-05-06 16:01:01.000 3.00 11.75 000190 2006-06-06 17:01:01.000 2.00 11.75 000030 2006-07-06 19:01:01.000 1.00 11.75 000310 2006-03-06 14:01:01.000 5.00 11.75 000310 2006-04-06 15:01:01.000 4.00 11.75 000010 2007-05-07 11:00:00.000 2.00 11.75 000010 2006-06-06 17:01:01.000 NULL NULL 000110 2006-07-06 19:01:01.000 1.00 11.75 000190 2005-10-06 10:01:01.000 10.00 11.75 000030 2007-05-08 15:00:00.000 1.50 11.75 000010 2005-12-06 11:01:01.000 8.00 11.75 000190 2006-01-06 12:01:01.000 7.00 11.75 000030 2007-05-07 11:30:00.000 2.00 10.50 000030 2007-05-08 13:30:00.000 1.00 10.00 000030 2006-07-06 19:01:01.000 1.00 11.75 000010 2007-05-09 14:00:00.000 1.25 10.00 000110 2007-11-06 10:01:01.000 9.00 11.75 000030 2007-05-10 14:00:00.000 5.00 10.00 000190 2007-05-11 14:15:00.000 4.00 10.00 000190 2007-05-12 15:00:00.000 2.00 10.00 000190 2007-05-13 16:00:00.000 1.00 10.00 000190 2007-05-08 05:00:00.000 1.00 10.00 000310 2007-05-07 00:00:00.000 2.00 10.00 000310 2007-05-14 11:00:00.000 3.00 10.00 000310 2006-07-06 00:00:00.000 1.00 11.75 000190 2007-05-11 13:00:00.000 3.50 10.00 000310 2007-05-08 05:00:00.000 1.00 10.00 000010 2007-05-08 07:00:00.000 2.00 10.00 000010 2007-05-08 11:00:00.000 3.00 10.00 000010 (57 row(s) affected)
SELECT @@ROWCOUNT AS 'Rows previously affected';
go
go
Rows previously affected ------------------------ 57 (1 row(s) affected)
- Notice it tells you that it sent back 57 rows to the user, this is the number of rows affected by the PREVIOUS request.
Summaries use of @@rowcount
SELECT COUNT(*) AS '# of rows' FROM sales;
go
go
# of rows ----------- 57 (1 row(s) affected)
SELECT @@ROWCOUNT AS 'Rows previously affected';
go
go
Rows previously affected ------------------------ 1 (1 row(s) affected)
- @@rowcount = 1
- Notice it tells you that it sent back only 1 row to the user.
- Although 57 rows where read, only 1 row what returned to the user, the summary row: count(*).