title: Recommended way of querying multiple Versioned tables
body: <p>Have a win 2003 box with MSSQL 2005 running on it. There is a database which is populated every morning with new/modified SalesOrder made the previous day. The database has several tables: SalesOrder, SalesOrderItem, SalesOrderItemBom. Each with a corresponding Version table (i.e. SalesOrderVersion, SalesOrderItemVersion, SalesOrderItemBomVersion) which has the exact same fields but with 2 addition columns VersionStartDate, VersionEndDate. The non-Versioned tables have the latest data. </p>
  
  <p>Also the VersionStartDate is part of the PK for the version tables, so for example:- SalesOrder has OrderID as PK and SalesOrderItem has VersionStartDate, OrderID as the PK. </p>
  
  <p>An simplified example of how the version table works:</p>
  
  <p><strong>SalesOrder</strong></p>
  
  <p>OrderID, Amount
  1, 100
  2, 200</p>
  
  <p><strong>SalesOrderVersion</strong></p>
  
  <p>VersionStartDate, OrderID, VersionEndDate, Amount
  20090101 13:00:00, 1, 20090103 08:00:00, 50
  20090103 08:00:00, 1, 99991231 00:00:00, 100
  20090101 09:00:00, 2, 20090105 15:00:00, 300
  20090105 15:00:00, 2, 99991231 00:00:00, 200</p>
  
  <p>whenever the a row in SalesOrder is changed, the current row VersionEndDate in SalesOrderVersion is updated and a new row is inserted into SalesOrderVerion with VersionEndDate 99991231</p>
  
  <p>Notes: If a record in SalesOrderItem was changed, it does not necessarily result in a change in "parent" record in SalesOrder</p>
  
  <p>Have been requested to do a report showing trend and daily increments in sales. Off the top of my head, I was thinking of creating 3 snapshot tables for SalesOrder, SalesOrderItem, SalesOrderItemBom, which capture the "latest data" as of the current day and thus build incremental snapshots to show trends. Besides requiring more disk space, is there any draw back to this method compared to doing a stored procedure which joins the Version tables, cos it seems to be a long and expensive query.</p>
  
  <p>Any thoughts or recommendations?</p>