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).
My enterprise also uses a trigger based approach for both auditing and history. In addition to the core table in the enterprise warehouse, every significant table has an audit table in a separate database. This audit table has 1 row for every transaction. Some of our tables also have a historical version in a third database. The Audit database is purely for after-the-fact trouble shooting and non repudiation -- but it is difficult and non-performant to query for data analysis. Our history database is optimised to answer point-in-time queries very efficiently. All of this is 100% scripted by a .net tool I wrote, so when we alter the schema or add new tables to history, we just re-script the affected triggers.