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.