Question: We are in the early stages of building a large C# MVC2 app (we also employ Sharp architecture and Nhibernate as part of the ecosystem) on SQL 2008 R2, and one of the requirements is that all database row versions are accessible for a given period of history.

We have toyed with the idea of a layout similar to:

id (PK)  

recordId  

versionId

and having each edit to a record result in a new record being created with the same recordId and an incremented versionId. Record display would then be done with something along the lines of SELECT ... WHERE recordId = X AND versionId = MAX(versionId)

Snapshotting on every transaction would not work (too many? and not accessible from within the application easily).

But we are curious as to what other implementations have been tried with success, or potential problems with our proposal.

Answer: 
You seem to be alluding to a temporal table. Three approaches:

**Valid-state table**: appending two 'timestamp' columns (e.g. of type `DATETIME`), one specifying when the row became valid and one specifying when the row stopped being valid, the intervening time is being the period of validity of the row

**Transaction-time state table**: associates with each row the period of time that row was present in the monitored table, thereby allowing the state of the monitored table at any previous point in time to be reconstructed.

**Bitemporal table**: capturing both valid time and transaction time, simultaneously records the history of the enterprise, while also capturing the sequence of changes to the record of that history.

Source: [Developing Time-Oriented Database Applications in SQL (Richard T Snodgrass)](http://www.cs.arizona.edu/people/rts/tdbbook.pdf).
We have a system developed by our DBAs that works as a trigger on update/delete. There is a secondary table that nearly mirrors the table being audited (aside from some other details like transaction time, login used to do the update, server, etc.). Anytime someone makes a change, it is logged in the audit version of the table via the trigger. It's kind of annoying to have to keep the audit trigger up to date anytime the schema changes, but c'est la vie.

The good thing about this is that applications do not need to be concerned with this auditing *at all* ... so it keeps the concept count for application code low(er).

This is in production and works on tables where transactions number in the tens of thousands per day. Your mileage may of course vary based on a number of things like the size of your server, and the nature of your data, but it works for us :-)