Consequences of Timeout/Error on a Delete statement

Hello Guys,

Lately, one of my friend asked me a Question, “If Delete is in progress and query get erred out or Timeout, will already deleted data will be rolled back, considering we do not have any transaction? “

My previous Answer was NO, After I executed the below statements.

I will create two tables,

PkTable – Having only one primary column.

FkTable – Have one column acting as Foreign key.


CASE 1 – If Query Get erred out

So the idea is, If I try to delete an id from PkTable which is also present in FkTable. Then SQL Statement will erred out. I will check if SQL Deletes all the rows other than the row having Foreign key relationship.

Let us first insert some data in my table.

Insert Records in Pkey Table

Now only one id in my FKTable, to have at-least one Foreign key relationship.

Insert Records in Fkey Table

We are ready to execute delete statement.

Delete Records in Pkey Table

From the above snapshot it is clear that our delete statement was completed with error.(Which was FKey constraint error). So let us check what we have remaining in our PKTable.

Select Records in Pkey Table After Delete

From above image, it is clear that DELETE will remove all the other records except the one for which SQL throws an exception.

CASE 2 – If Query Get Time out

What will happen if query does not get error out but got Time out due to huge number of records or some other reason.

Before i continue, I have to make some configurations. So that i can deliberately make my query to get timeout.

Choose QueryOptions as shown below.


Configure the highlighted option. I have changed it to 5 seconds.


Let’s make a quick check if our configuration is working correct or not.

Check For Query Timeout


I have repopulated my table. Now, let us do a delete.

Delete Records in Pkey Table With Timeout

Select Records in Pkey Table After Delete with timeout

As expected, all the records before timeout has been deleted.

But here is the Catch. The above Statements may seems to be correct but does not hold true. If you notice I am not deleting a batch of rows. It is just one row at a single time. It was a blunder at my side that I missed to explain (Thanks to Vikas and Chris for pointing this out).

So what I did wrong?

While statement actually making the each DELETE statement as an individual statement. So it is actually a 10000 Delete rather than one single DELETE.

Now, if we run the Delete as a batch it actually Rollback all the deleted rows if an error is encountered. Please see below snapshot.

Total number of rows before delete.


Deleting in a batch.


Let us check if we have actually deleted something?


No change. This implies Deletion does not happened if it encounters any error.

(For other T-SQL, XACT_ABORT should also be considered. Check below link for details

4 thoughts on “Consequences of Timeout/Error on a Delete statement

  1. ChrisM@Work says:

    That’s not a delete, it’s 10,000 deletes, each one of them within an implicit transaction. Of course they’re not rolled back, they’re done and finished. For your argument to hold any water, you must interrupt a *single* DELETE operation and show that rows are not rolled back.
    Very very misleading article.

    Liked by 1 person

  2. lokesh says:

    Yes, Chris I agree with you. It was my mistake that I considered this as a batch DELETE statement. This is not a Batch statement it is an individual delete. I will update this article


  3. Pulkit says:

    I think Chris’s comment hold true for Case 1 as well. You are deleting one row at a time due to which you are seeing one row remaining because we hit error only during that one row delete. I think If we do delete in batch then it will rollback all rows.


    • lokesh says:

      Yes Pulkit, And I have already mentioned the same at very last.
      The only reason I have not removed the incorrect part is because that others do not commit the same mistake which I did.


Shoot Your Comments!

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s