Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Why there is an extra "(1 row(s) affected)"

The SSMS shows an extra (1 row(s) affected) every time when I execute insert/update. For example, execute the following SQL

declare @a table (a int)
insert into @a values (1), (2)
update @a set a = 3 

And the SSMS will display the following message.

(2 row(s) affected)

(1 row(s) affected)

(2 row(s) affected)

(1 row(s) affected)

I didn't find any database/server trigger. What could cause the extra (1 row(s) affected)?

like image 923
ca9163d9 Avatar asked Mar 01 '12 07:03

ca9163d9


People also ask

What is 1 row affected in SQL?

When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of rows affected by the trigger or triggers. For all other types of statements, the return value is -1.

How many rows can be inserted at once?

The Maximum number of rows you can insert in one statement is 1000 when using INSERT INTO ... VALUES... i.e. INSERT INTO TableName( Colum1) VALUES (1), (2), (3),...... upto 1000 rows.

How do you update more than one row?

There are a couple of ways to do it. INSERT INTO students (id, score1, score2) VALUES (1, 5, 8), (2, 10, 8), (3, 8, 3), (4, 10, 7) ON DUPLICATE KEY UPDATE score1 = VALUES(score1), score2 = VALUES(score2);

Which clause is used on multiple rows?

Multiple Row Sub Query Multiple-row subqueries are used most commonly in WHERE and HAVING clauses. Since it returns multiple rows, it must be handled by set comparison operators (IN, ALL, ANY).


2 Answers

That usually means you have the actual execution plan option turned on. The execution plan is sent as an extra rowset, resulting in an extra (1 row(s) affected) message.

To disable actual execution plan press Ctrl+M.

like image 136
Andomar Avatar answered Oct 17 '22 22:10

Andomar


this should not happen. try to look into the actual execution plan, what is happening there.

like image 25
Vikram Avatar answered Oct 17 '22 22:10

Vikram