First of all, great article and very insightful so thanks for taking the time to write it. I do have one question about the approach you explained for deleting large quantities of data from tables with clustered index -

Assuming I understood correctly, you are saying that on a daily basis (or some periodicity), move the data you would like deleted into a look-up table. Then, once a week, join the look-up table to the table that data needs to be deleted from and perform delete. And subsequently truncate the lookup table or something. So my question is - in your implementation of this, have you encountered any blocks on that big table that the data needs to be deleted from during the join? What if that big table is frequently being used by other processes? I was planning to try this out and I am somewhat of a novice so I thought I'd check before breaking anything 🙂

Also, out of curiosity, I noticed that the look-up table that you created for deleting data from a clustered-index table itself had no clustered index, simply a non-clustered index on the column "UnixDateToDelete". Was this done for a specific reason?

Thanks again for the article!