SQL: How to DELETE or UPDATE large data in chunks to increase the performance with no locking on table

Let's say you want to update or delete large data think of millions and millions of records, you write a single update or delete statement. It will downgrade your query performance or unnecessarily it will hold the lock on the table which will cause the applications which are using that table will be in stuck. 

So to avoid such type of situations, we can delete or update the data in chunks which will help in better performance than the previous case. 

Let's say you have a table like below, here the data is less I have taken this for demo purpose. 

And we want to delete in the chunks of 2 which means at a time only 2 records should get updated or deleted.

Below is the sample query using while loop, first you declare a variable @rowcount = 1, and in the update statement, you are specifying the count in which amount of chunks it needs to be updated this can vary based on your requirement. and in the end, you will update @rowcount with the @@ROWCOUNT. 

You can see the result, how the records got updated.