1

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?

Albert
  • 3,639
  • 13
  • 43
  • 58

4 Answers4

4

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?

Justin Swartsel
  • 3,451
  • 1
  • 20
  • 24
  • 3
    Just replace the Update clause (everything before the From) with 'Select Count(*)' – Charles Bretana Dec 10 '09 at 15:33
  • 1
    of course, on a busy production system, between the time you issue your `SELECT COUNT(*)` query and the actual `UPDATE`, that number could very well change, quite possibly even significantly. There's nothing really you can do about this, though, I think.... – marc_s Dec 10 '09 at 15:46
4

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

AdaTheDev
  • 142,592
  • 28
  • 206
  • 200
  • Using SELECT COUNT(*) will give you an inaccurate result if a row in the updated table is updated more than once by the same statement due to joins. Shouldn't normally be an issue, since that usually gives results that are undefined which is a bad practice, but it is possible. – Tom H Dec 10 '09 at 15:31
2

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.

Tom H
  • 46,766
  • 14
  • 87
  • 128
1

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

Gordon Bell
  • 13,337
  • 3
  • 45
  • 64