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.