4

In haste, I stupidly ran a statement to update a table without qualifying it with a where statement. So it started to update all records in the table.

I immediately noticed the error and hit the 'Cancel Execution" button in SQL Server Management Studio, but it took a minute to stop running.

So my question is, did it roll back the changes or were they made until it was told to stop?

I can't tell which records were updated just by looking at them. I'd have to restore the table if it did make any changes.

Thanks.

I wanted to run: 

Update tableA 
set newdate = '2019-01-01' 
where account = 'abc'

but instead I ran: 

Update tableA 
set newdate = '2019-01-01'

The database is a transactional type database.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
John
  • 1,310
  • 3
  • 32
  • 58

2 Answers2

5

SQL Server has the default transaction behaviour by default. That means that each sentence you run in the query editor is like:

BEGIN TRANSACTION
<YOUR COMMAND>
COMMIT TRANSACTION

So, if you have cancelled before finished, the transaction should be rolled back.

Alvaro Mendoza
  • 61
  • 3
3

If the query hasn't finished before being cancelled then yes, it was rolled back. Either whole update was executed or nothing was changed.

nimdil
  • 1,361
  • 10
  • 20
  • Given that the update is a single statement, and should be executing within a single transaction, I agree with your answer. But, you might want to include a doc reference to back up what you say. – Tim Biegeleisen Jan 07 '19 at 15:34
  • I'm not sure this is correct. The statement is sent to the database. The database may decide to complete it even if the connection is dropped. At a minimum, the timing can mean that the connection is dropped in the UI, but the transaction still committed in the database. – Gordon Linoff Jan 07 '19 at 15:36
  • I've written that query was cancelled or not so it really depends on what happened on server. Most of the time it will be rolled back especially if the query is non-instant one. But tbh I have never came across documentation explaining underlying call - will look around – nimdil Jan 07 '19 at 15:41
  • 3
    https://stackoverflow.com/questions/15921458/how-to-kill-stop-a-long-sql-query-immediately this is actually better than documentation – nimdil Jan 07 '19 at 15:46
  • 2
    Since it took about 10 seconds to stop after running for about 5 seconds. I'm assuming the 10 seconds, was the rollback period. – John Jan 07 '19 at 15:49
  • 1
    Can't you write a query to check? – nimdil Jan 07 '19 at 16:07
  • @John There is always a transaction in SQL Server, [either explicit or implicit](https://stackoverflow.com/a/4988483/11683). Your single `update` was inside an implicit auto-committed transaction, and if you clicked the button in time it *should* have been rolled back. – GSerg Jan 07 '19 at 16:47