CODEWITHSUNDEEP
c#asp.netsql-server-2008
Forest Smith
Forest Smith

Reputation: 23

What do you do with your old records?

Many applications accumulate binary dust -- records that were accumulated over time and will never see the light of day. Our application keeps a chat history, and only displays the last 5 days worth of such records. Is it a good idea to run a cleanup operation after a while that moves those old records to a 'history' table. The history table will never be used, but just allows us to run internal statistics, whilst (I assume) enabling better performance on those records remaining in the main table.

Also, if we do build a history table then do we need to replicate our tables, or is there a hidden MS SQL function that will create "shadow tables" automatically.

Upvotes: 2

Views: 172

Answers (2)

Elroy Flynn
Elroy Flynn

Reputation: 3218

cyberkiwi is correct regarding "no magic" and his recommendation is fine for simple data. When the data is complex, the archiving becomes complex. You may find that you want to denormalize data when archiving, so that you can then delete or modify the data that the original rows referenced.

Upvotes: 1

RichardTheKiwi
RichardTheKiwi

Reputation: 107776

There is no MS SQL function that will create such shadow tables automagically. It comes close with partitioning, but that really splits the storage (fast/slow) while still keeping data in one table.

What you are looking for is a straight archive and purge. A simple strategy is a nightly task to

  1. update static lookup tables (that the primary table references)
  2. move records with date < getdate()-6 to history table
  3. delete records that were moved

If this table has foreign key links to other tables, you will find it unlikely that those tables need to be purged unless they too grow quickly (rare). If other tables have FKs to the primary on the other hand, that is a different matter. In that case, you would have to move all the dependent children, then the primary, then finally delete in reverse order.

Upvotes: 1

Related Questions