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.

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

We are ready to execute delete statement.

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.

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.

Great!.

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

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  https://msdn.microsoft.com/en-us/library/ms188792.aspx)