how to know how many rows will be affected before running a query in microsoft sql server 2008
You can also use BEGIN TRANSACTION
before the operation is executed. You can see the number of rows affected. From there, either COMMIT
the results or use ROLLBACK
to put the data back in the original state.
BEGIN TRANSACTION;
UPDATE table
SET col = 'something'
WHERE col2 = 'something else';
Review changed data and then:
COMMIT;
or
ROLLBACK;
Short answer is no..
You cannot get the number of rows before executing the query..atleast in SQL server.
The best way to do it is use
Select count(*) from <table> where <condtion>
then execute your actual query
[delete]or [update] [set col='val']
from <table> where <condtion>
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
or DELETE 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 or deleted.
Eg:
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'