What I meant was lets not consider it and just focus on retention first.
You have a few options and which you use depends how you want the data to look after you do this.
Remove all revisions prior to retention range
In this use case, you would basically examine the
REVINFO table and find all entries with a timestamp value older than the intended retention range and from there iterate over all the tables removing any revisions that are less-than that revision.
You will need to iterate the
_AUD tables in such a way that should constraints between the
_AUD tables exist that you aren’t violating them so that the database operation succeeds.
What I personally find as a downside to this approach is you will eventually loose sight of when a row was actually first created. The history of a given row may be that the first insert is older than the retention range but the updates and/or removals are still relevant. If that’s of no concern for your organization, great.
Remove all update revisions prior to retention range, preserve insert/delete
In this use case, you would do precisely the same behavior as the other approach, but instead you would also examine the
REVTYPE column value as well. The only time a row could be removed is if the value of that column represents the
RevisionType.MOD value, which numerically is
1, again where the REV column’s revision number value are those with a timestamp less-than the retention range.
From an auditor perspective, its often less important to keep track of all the smaller incremental updates to a row over its lifetime, but having a clear picture when it started and when it ended can be far more valuable depending on the context of data being kept.
Typically most of the audit history maintained for rows in any system are where a row is updated, so by only focusing on those types of revision types, you can reduce the overall size of your audit data pretty substantially but maintaining a decent life-cycle history of the actual row itself.