9

I have a table that has millions of rows.

Accidentally I wrote an update query over a table without where clause and clicked execute.

It started executing. After two seconds I realized the query is wrong and I clicked 'Stop' button in Sql Server Management Studio. The query execution was stopped, this all happened within 7 seconds.

Now I am curious to know if there are any rows affected. If any which are they? How to find it?

Keren Caelen
  • 1,466
  • 3
  • 17
  • 38
MaxRecursion
  • 4,773
  • 12
  • 42
  • 76
  • possible duplicate of [How can I rollback an UPDATE query in SQL server 2005?](http://stackoverflow.com/questions/721471/how-can-i-rollback-an-update-query-in-sql-server-2005) – Kermit Nov 23 '12 at 15:24
  • I dont think so njk. I dont want to rollback, I want to know number of rows affected. Thanks – MaxRecursion Nov 23 '12 at 15:28
  • 2
    @AkshayKulkarni You have no rollback to do cause no commit was made. SQL server does the commit at the end of the statement but you canceled. So no row affected – Marc Nov 23 '12 at 15:32

1 Answers1

13

A single update statement will not update some rows. It's all rows or none

This is the atomicity in the ACID properties which SQL server respects well.

Atomicity requires that each transaction is "all or nothing": if one part of the transaction fails, the entire transaction fails, and the database state is left unchanged. An atomic system must guarantee atomicity in each and every situation, including power failures, errors, and crashes.

Then the commit is at the end of the statement, so when you cancel there's no commit

Community
  • 1
  • 1
Marc
  • 16,170
  • 20
  • 76
  • 119