Tables with transaction time automatically store in the table a snapshot copy of any row that is modified or deleted. This characteristic of these tables can be used to recover a prior state of the table using only SQL. This can be useful to quickly recover from a localized problem, such as if a table or set of tables have become corrupted by a user error, and need to be restored to a consistent state.
Assume a table that has transaction time needs to be restored to the state it was in at a point (time X) prior to the current time. Rows in the table can be classified based on whether they are open or closed, and on their transaction-time column period relation to time X:
Beginning Transaction Time
End Transaction Time
|1||Open||Prior to time X||UNTIL_CLOSED|
|2||Open||Equal to time X||UNTIL_CLOSED|
|3||Open||After time X||UNTIL_CLOSED|
|4||Closed||Prior to time X||Prior to time X|
|5||Closed||Prior to time X||Equal to time X|
|6||Closed||Prior to time X||After time X|
|7||Closed||Equal to time X||After time X|
|8||Closed||After time X||After time X|
These rows are represented graphically below.