Management Studio 2005: Will Cancelling a Statement trigger a Rollback?

Question

A few minutes ago, while working out a new sproc, I executed the wrong delete statement. Something like this:

Delete From SomeTable Where SomeStatusID=1

10 seconds into it, I realized that I typed in the wrong status and hit cancel. It said that the statement was canceled.

I did a restore to a separate database to get back the table that I just presumably nuked, thinking that since this was not in a transaction some records were probably deleted.

Oddly, the records were all intact. Just curious as to why this was -- did it treat the individual delete statement as a transaction in this case, even though there was not an explicit transaction defined?

Answer 1

There are always transactions. The only thing that changes is how those transactions are isolated from each other and that in management studio the transaction might not be defined explicitly and is auto-committed when the query finishes.

Answer 2

By default, a single DML statement is executed as a transaction. If the statement succeeds, the transaction is committed. If the statement is cancelled or otherwise fails, the transaction is rolled back.

This behavior is built in to the engine, and is not related to management studio. See Autocommit Transactions in the docs.