History Tables

[jeme]

Simple support for history by creating a secondary table, while we could just keep pushing into the same table, history is often of less importance and so for performance reasons it seems like it would make sense to simply move the old record.

This obviously creates a bit of overhead on updates.

We might be able to use OUTPUT to build a single statement for this however:

• http://blogs.msdn.com/b/sqltips/archive/2005/06/13/output-clause.aspx
• http://technet.microsoft.com/en-us/library/ms177564.aspx

However we should think carefully about the process. In some cases its desired that when an item moves from live storage to history, it is possible to run transformations on it, removing fields that should not be part of the history (e.g. sensitive data).

• Move Records to History
• Transform Records on input
• Transform Records on predicate