Retrieving information from a history table for a given point in time is a common data task. For example, using a table that contains all historical prices,
what where the prices of all products as they were 30 days ago? There are a number of ways to different methods to obtain this information from the audit table.
Sql-Server-Performance.com had a nice article in March comparing 5 ways of Retrieving Data from an Audit Table:
- Using a subquery to determine which record for each product is the correct one for 30 days ago. The subquery selects the maximum DateEntered that is less than 30 days for each product and is refered as subquery(max).
- Using a subquery to determine which record for each product is the correct one for 30 days ago. The subquery selects the top 1 DateEntered that is less than 30 days, ordering by DateEntered descending and is refered as subquery(top).
- Using cross apply. The Products table is cross applied against the most recent record of PriceHistories that is older than 30 days.
- Using string manipulation. Concatenate the DateEntered with the price and use this concatenation to determine the greatest DateEntered that is less than 30 days. Return the substring of the concatenation that represents only the price.
- Using the rownumber function in a common table expression to select the first record that older than 30 days for each product, ordered by DateEntered.
The results are quite interesting. While i subjectively was thinking that the RowNumber method would be better than the Subqueries, it isn't the case and cross apply normally seem to be the best match.