SQl Management Studio - Find "Rows Affected" BEFORE running Update Statement

Before running an Update statement in SQL Server Management Studio, I'd like to see how many rows it is going to affect so I know my logic is sound. Any way to do this?

I've tried running an execution 开发者_高级运维plan a few times, and after running when I hover over the green update icon it shows rows affected, but sometimes it shows 20.5 rows for that stat, which makes no sense.

Any ideas?

The estimated execution plan is going to give you rows affected based on statistics, so it won't really help you in this case.

What I would recommend is copying your UPDATE statement and turning it into a SELECT. Run that to see how many rows come back and you have your answer to how many rows would have been updated. Can you post the SQL?

Couple of ways:

1) tweak the UPDATE to be a 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 tweak to comment out the UPDATE...SET... part to be SELECT COUNT(*)

or....

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

If I have a statement such as:

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

then to get the count I'll just replace the start with the following:

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

Another option would be to simply wrap it in a transaction and immediately issue a ROLLBACK. It might take longer that way and you will end up with locks on the table, but you don't run the risk of a typo between your UPDATE and SELECT.

Not really, other than just doing a select count(*) using the same criteria as your update statement.