Andy Mallon explains why deleting in batches is a good idea:
Yesterday, I wrote about the importance of planning your data purges. Today, let’s look at the simplest requirement for purging data: Keep data for X days after it’s created. There are tons of cases where this is the rule. Log data, telemetry data, even transactional data is often retained based on X days since it is created.
Given how common this scenario is, let’s talk in a bit more detail about I like to go about deleting it. Deleting data is easy, right?
DELETE t FROM dbo.Transactions AS t WHERE CreateDate <= DATEADD(dd,-90,GETDATE());
This part of the series covers the why; the next part will cover the how.