Sql-server – create a process where anything older than six months we would write to an archive table and delete the records from the inventory table

archivedatabase-designMySQLsql serversql-server-2012

I need to create a process where anything older than six months we would write to an archive table and delete the records from the inventory table. Our inv table is huge btw lets use (x) as the number. So i need query to select anything that has an end date that is six months or older and write the SQL. Does this make sense?

Where End_Date <= dateadd(month, -6, getdate())

Since we want to go back 6 months we add -6 to today's date, which is the last parameter in the formula (getdate() get's today's date from the server). This will yield an answer of 10/8/2020 So any end date on the table that is less than or equal to 10/8/2020 will be selected to the results .

If this doesn't make sense or you need more info let me know.

Best Answer

If you have the ability to change the data model and the structure of this table, partitioning by date is the best way to handle maintenance like this. A partitioned table is a just a special type of table that is divided into segments, called partitions, that make it easier to manage individual segments. If you map each partition to a date, then dropping or moving data for a specific data is as easy as a truncate or a partition switch.

There is some documentation here, and some examples that could be helpful.

If you're stuck with this table structure and still need to do this operation, this can become difficult if the number of rows being deleted is substantial, so a few comments:

  • A DELETE is a fully logged operation so if that's touching many rows, you could end up filling up SQL's transaction log. In those cases, batching the deletes to handle the top X rows at a time can help with committing smaller transactions.
  • If your database is in the SIMPLE recovery model, it's sometimes more performant to just insert the rows you want to keep into a new table and use that as means to delete rows. That insert can be minimally logged using the WITH (TABLOCK) hint and can be more performant than a fully logged DELETE depending on the volume of data.
  • It's worth mentioning you won't encounter these issues if your table is partitioned and can truncate individual partitions.
Related Question