15

i've read a bit about ROWCOUNT but its not exactly what im looking for. from my understanding rowcount states the number of rows affected AFTER you run the query. what im looking for is knowing BEFORE you run the query. is this possible?

Joe G Joseph
  • 23,518
  • 5
  • 56
  • 58
user571099
  • 1,491
  • 6
  • 24
  • 42
  • No it isn't possible without doing two queries. Why do you need to do this though? Depending on the reason for the request you could maybe do the query check `@@ROWCOUNT` then commit if it is as expected. Or use `COUNT(*) OVER()` to return the row count with the query. – Martin Smith Sep 15 '12 at 12:09

5 Answers5

15

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;
Giacomo1968
  • 25,759
  • 11
  • 71
  • 103
jabs
  • 1,694
  • 4
  • 19
  • 36
12

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>
Joe G Joseph
  • 23,518
  • 5
  • 56
  • 58
  • Although you can use the SQL Server Analytical functions to get the total number of rows that are getting effected. But this is at Runtime only. – rvphx Nov 04 '14 at 06:13
  • will not be always correct as update will skip rows where nothing to need to be change. for example: new value and old value is already same. – Himanshu Saini Apr 10 '15 at 16:50
  • 2
    @HimanshuSaini : As far I know, SQL server will not check whether the old and new values are same, before updating. It Just updates all the records with matching conditions. Please share if you have any documents regarding that. That would be new learning for me – Joe G Joseph Aug 05 '15 at 22:53
3

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'
heretolearn
  • 6,387
  • 4
  • 30
  • 53
1

Simplest solution is to replace the columns in the SELECT * FROM... with SELECT Count(*) FROM ... and the rest of your query(the WHERE clause needs to be the same) before you run it. This will tell you how many rows will be affected

Conrad Lotz
  • 8,200
  • 3
  • 23
  • 27
0

The simplest solution doesn't seem to work in a case where theres a subquery. How would you select count(*) of this update:

BEGIN TRANSACTION;
update Table1 t1 set t1.column = t2.column
from (
  SELECT column from Table2 t2
) AA
where t1.[Identity] = t2.[Identity]
COMMIT;

Here I think you need the BEGIN TRANSACTION

user890332
  • 1,315
  • 15
  • 15