1

Say I have an SQL statement like this (executed in SQL Studio manager):

UPDATE Person SET Flag=1

Say there are 10 million persons and you stop the query running before it finishes. Then will it rollback or just stop at the point you clicked stop e.g. five million rows updated and five million rows not updated.

I know you can wrap the update in a transaction. I guess I am asking if sql studio manager has its own transaction.

w0051977
  • 15,099
  • 32
  • 152
  • 329
  • All statements run in a transaction. For a single statement auto commit transaction it will either all succeed or all fail. As long as SQL Server recognizes the attention event and cancels the statement before it completes then it will rollback. – Martin Smith Feb 18 '14 at 17:16
  • @Martin Smith, could you clarify what you mean by: "recognises the attention event". Thanks. – w0051977 Feb 18 '14 at 17:19
  • 2
    Described [here](https://blogs.msdn.com/b/psssql/archive/2008/07/23/how-it-works-attention-attention-or-should-i-say-cancel-the-query-and-be-sure-to-process-your-results.aspx) and [here](http://blogs.msdn.com/b/khen1234/archive/2005/10/20/483015.aspx) second one mentions that it might not be processed immediately/ – Martin Smith Feb 18 '14 at 17:25

1 Answers1

1

You have three types of transaction in sql server Implicit Transaction , Explicit Transaction and Auto commit transaction.

Autocommit mode is the default mode of SQL Server. Every statement is committed or rolled back when it completes.

1) If a statement completes successfully, it is committed.

2) if something goes wrong and any error is raised , it is rolled back.

Assuming that you had Default transaction mode when you executed the above update statement. Because something went wrong( you stopped the execution). the transaction will be rolled back and data will be left in a consistent state.

M.Ali
  • 67,945
  • 13
  • 101
  • 127