Dear All,
I ran below update query in sql developer management studio and suddenly realised that , it's not correct one and manually cancelled the query.
Can anybody help me with, if some rows were update here or not.
Regards,
Tapan Sharma
if you cancelled the query, then SQL Server rolled back any updates that it had made so far. That means that your data is now in the same state as before you began the update (that you subsequently cancelled)
For adhoc updates I do this:
BEGIN TRANSACTION
update phonenumberextension
set rkn_state=bs.state_dcrm from #testingBSS bs where bs.state_dcrm =1 and bs.msisdn_dcrm='12042180350'
-- COMMIT
-- ROLLBACK
and then if I get the right number of rows etc. I highlight either COMMIT or ROLLBACK and execute that.
Your code looks like it would have done a Cartesian join between [phonenumberextension] and [#testingBSS] ?
Yeah , i know actually i was testing something and ran some query by mistake so just want to know if any row was updated or not
Thanks for explanation but as far as i know there would be no rollback in sql server
If you run, say, an UPDATE there is an implicit transaction (assuming you haven't started one explicitly). If there is an error, power cut, or the update terminates for some other reason then it will be rolled back (including after the power comes back on and the server restarts 
)
If you have the opportunity to start an Explicit transaction then that will remain open until you either Commit it, or Rollback
I do the same thing - except I un-comment the rollback the first time I execute it. If needed, I put select statements before and after the update/insert to show the shows prior to the update/insert and what they would look like after the update/insert.
Once I have executed the code - reviewed the results and confirmed they are valid - then I un-comment the commit and comment out the rollback then execute the code again to commit the modifications.
The problem with not immediately rolling back or committing the transaction is that it will block until either is done. If you take even as little as 5 seconds to confirm the results - you could cause users to hang waiting for you to clear the transaction. I know 5 seconds doesn't sound like a lot of time - but to a user trying to access data it could seem like forever.
The example I gave was just for tidiness to the O/P, but my normal method is not to comment the ROLLBACK at all - so if it is just "executed" it rolls back, I have to highlight "excluding rollback" to run it holding the transaction open - which I sometimes do in order to then do a SELECT ... to verify that the expected has happened - and then either highlight COMMIT or ROLLBACK and exec that
Yes, agreed, I would rarely being doing it like this on PRODUCTION, but I do like to go to this Belt &Braces on DEV, otherwise I just wind up restoring to reverse a why-did-I-do-that !
My pre-SELECT is normally something like this:
BEGIN TRANSACTION
update U
set
-- SELECT U.SomeID, U.InterestingColumn, O.InterestingColumn,
Col1 = O.ColX
FROM Table2Update AS U
JOIN OtherTable AS O
ON O.SomeID = U.SomeID
-- COMMIT
ROLLBACK
Only mentioning this in case it spawns ideas and suggestions of what others do.
One important thing to remember is that SQL Server implements ACID. That means that the database is maintained in a consistent state. To do that, a half-finished update must be rolled back, or the server no longer maintains ACID