SQl Management Studio - Find "Rows Affected" Before Launching the Update Application

Question

Before running the Update statement in SQL Server Management Studio, I would like to see how many rows this will affect, so I know my logic sounds. Any way to do this?

I tried to execute the execution plan several times, and after starting, when I hang up the green update icon, it shows the affected lines, but sometimes it shows 20.5 lines for this stat, which does not make sense.

Any ideas?

Answer 1

The proposed execution plan will result in rows being affected based on statistics, so this will not help you in this case.

What I would recommend is copying your UPDATE statement and turning it into a SELECT. Run this to see how many rows are being returned, and you have an answer to how many rows would be updated. Can you host SQL?

Answer 2

A couple of ways:

1) configure UPDATE as SELECT COUNT (*): e.g.

UPDATE t SET t.Value = 'Something' FROM MyTable t WHERE t.OtherValue = 'Something Else' 

becomes:

 SELECT COUNT(*) FROM MyTable t WHERE t.OtherValue = 'Something Else' 

Just a quick setup to comment on UPDATE ... SET ... part to be SELECT COUNT (*)

or....

2) Run UPDATE in BEGIN TRANSACTION .... ROLLBACK TRANSACTION block

Answer 3

If I have an operator, for example:

 UPDATE MT FROM My_Table MT INNER JOIN Some_Other_Table SOT ON .... WHERE .... 

then, to get the score, I just replace the beginning with the following:

 SELECT COUNT(DISTINCT MT.<primary key column>) FROM My_Table MT INNER JOIN Some_Other_Table SOT ON .... WHERE .... 

Another option is to simply wrap it in a transaction and immediately issue a ROLLBACK. This may take longer and you will get locks on the table, but you do not risk a typo between your UPDATE and SELECT.

Answer 4

In fact, except that you do select count (*) using the same criteria as the update statement.