We are implementing a new feature in our system that will cause about a million records (each record is tiny, basically a GUID, a date, and four smallint fields) to be purged from a table every night. Basically it's a caching table, and once the data is 7 days old we do:

DELETE FROM scheduleCache WHERE schDateCreated < '2013-08-26

This will run every night at 1am, and will purge about a million records every time it runs.

Is there anything I should be noting or doing for a table like this? Any properties I should put on the table, or any routines I should run regularly to "clean up"? I've never dealt with a table like this before.

The table has a single clustered index (GUID + one of the smallint fields), and we have a weekly index rebuild that runs Sunday mornings.


2 Answers 2


The problem of deleting large portions of a table is far from a trivial problem. The best approach, by far, is partitioning. A daily partition scheme with a sliding window is really a magic bullet for this problem, see How to Implement an Automatic Sliding Window in a Partitioned Table.

If you cannot afford partitioning (eg. non-enterprise license on site) then I would recommend clustering by schDateCreated. If you need primary key on the GUID+smallint then move it to non-clustered. Delete in batches (eg. TOP 10000), in a loop, to reduce pressure on the log. Consider updating stats after the operation.

  • Unfortunately we can't afford partitioning, nor can we request our customer-site installations to pay a that size of upgrade just to keep this function working smoothly. I will start it with the clustered index like you suggest and see how we go from there. Commented Aug 27, 2013 at 3:01
  • Just as followup, I made a clustered index on the date field, did a DELETE TOP 10000 WHILE COUNT() > 0 and it's been working great for several months. Commented Jun 10, 2014 at 8:13

One solution would be to store the data in one table per day, dropping the tables as they age out of the cache. The tables would have names like cache_(julian date).

Another solution would be to have a set of tables cache_0 to cache_n, truncating each table before you use it. You would use table cache_((julian date) modulo (cycle period)) for each day.

You would need to handle year end carefully, as you'll either go from 365 or 366 to 0 for either solution.

  • Indeed, these two things should work, however I'd like to avoid having multiple tables for a) execution plan caching and b) I know some customers do not give the application create table permission Commented Aug 26, 2013 at 4:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.