Home Forums SQL Server 2008 T-SQL (SS2K8) Why is this code deleting only 1 row at a time instead of using the condition and deleting many rows? RE: Why is this code deleting only 1 row at a time instead of using the condition and deleting many rows?

  • July 18, 2014 at 4:57 pm

    #1730799

    Evil Kraig F (7/18/2014)

    Lynn, curiousity, why wouldn't you set that to 4,500 rows instead of 10k to avoid table lock escalation?

    That is really just a suggestion. The OP can set it to whatever value desired. I picked 10,000 because it was a number. It also depends on how long it takes to delete a batch. I could also have put a WAITFOR statement in the while loop to slow down the deletes some more to allow other processes to access the table as well. In addition, I have no idea how many rows of data there are in the table nor how many are added a day. Plus, if run during a period of low activity it may not even matter if there is a table lock or not.

    So, setting it to a specific value really is a "it depends" kind of thing.