Most database designs can also provide a quick answer to questions of the form: “What information do I currently have right now about last week ?” However, historic questions like “What information did I have at the time last week about last week ?” are not often not as easy to answer. Using time as an explicit dimension, or temporal modeling, must be supported by the intentional design, configuration and maintenance of your database.

For Sql Server 2008 and later Change Data Capture [microsoft.com] provides a record of inserts, updates, and deletes in a relational format well suited for ETL (Extract Transfer Load) applications. But for more advanced requirements, using Valid Time tables as explored by Richard Snodgrass [arizone.edu] is a powerful technique to incorporate the temporal dimension for auditing and other historic queries directly in your data tables. As of SQL Server 2016 this technique is natively supported as ANSI SQL 2011 Temporal Tables [microsoft.com].

Valid Time tables write an entire new copy of a row including a timestamp with every add, edit insert and delete operations. This makes it extremely easy to reconstruct historic record values with Valid Time tables by adding a simple WHERE clause to your Sql queries.

Logging changes to individual field values is generally less practical for temporal designs. That is because the design makes it easy extremely difficult to reconstruct the prior state of the database. However there are reasons why logging changes to individual field values offeres a few specific engineering advantages for certain auditing requirements. Logging individual field changes does not require a selective update to “expire” the previous record after any change, only a new record to be inserted into a field change history table indicating each new field. Also large number of fields, of which only a few are ever updated at any one time, a full Valid Time audit table will result in more disk usage.