CODEWITHSUNDEEP
sql-server
HelpASisterOut
HelpASisterOut

Reputation: 3185

Cancelling a Delete Query

I executed this query that deletes 16 000 000 rows :

delete from [table_name]

3 minutes after execution and no results, I cancelled the query.

Took a while to cancel, but in the end it said "Query Cancelled"

Does that mean that any of the 16 000 000 are deleted? Or are they still all there?

This is not the real query, I just used it for the example.

Upvotes: 5

Views: 15342

Answers (2)

Sumit Chaudhary
Sumit Chaudhary

Reputation: 118

The rows should be there. Delete is rolled back if you cancel the statement. Once it completes, the changes are committed though.

Upvotes: 9

drew_w
drew_w

Reputation: 10430

So, first off - if you want to be sure you can cancel a query successfully, you need to use transactions. IF you were using transactions and that transaction was rolled back, none of the rows would be deleted. Management studio will only use implicit transactions if you have that flag set (see here).

That said, I was really intrigued because I figured that a delete operation should be an "atomic" operation so I wrote the following code to test that:

create database test_database
use test_database
go

create table sample (
  ID BIGINT IDENTITY(1,1) PRIMARY KEY,
  value VARCHAR(25)
)

declare @id int 
select @id = 1
-- up 90000 if this doesn't have enough rows
while @id >=1 and @id <= 90000
begin
    insert into sample (value) values ('abcdefg');
    select @id = @id + 1
end

-- run, but cancel in the middle
delete from sample

-- check, are there 90000 records now?
select count(id) from sample

-- clean up
drop table sample

Turns out, if you click cancel it does treat this as an "atomic" statement, which means all the rows are still there. Your environment might be different so test it out!

Upvotes: 1

Related Questions