Batch Delete – “One spoon at a time” the smart DELETE

BATCH DELETE

Delete in SQL server is not as simple as it looks.

Yes it has simple line of statement.

delete BatchDeleteDemo_Tab where name='Aadarshini'

Then what is the issue, there are several problem like

• transaction log
• disk space
• time consuming
• locking

It depends how many rows are affected by the DELETE statement above, but generally, it is almost never a good idea to run a ‘straight forward’ deletes.

SQL Server is working in a transactional manner, the rows which are to be deleted have to be logged in the transaction log, and if there is not enough disk space for the log, then we have a real problem – the log is full, no other transactions can be run… you get the idea.

When you delete rows from a table, the rollback transaction information is always written fully to the log file, and it will grow until the transaction is committed or rolled back.

So what is the option left for us:

while (1=1)

begin

      delete top 3 BatchDeleteDemo_Tab where name='Aadarshini'

      If @@rowcount = 0

            BREAK

      ELSE

            Continue

End

The while loop will delete 3 record at a time and then check if there are more rows to delete, if there are not then the loop gets terminated

CONCLUSION: This method is better and more efficient for obvious reasons: the log does not grow by too much and the log space is reused; it is easy to terminate the delete quickly and only the transaction of the last batch will be reverted; the lock escalations do not have a huge impact on the entire system since it is for few rows at a time.  

Post Reference: Vikram Aristocratic Elfin Share