Restoring a Prior Table State | Temporal Table Support | Teradata Vantage - 17.10 - Restoring a Prior Table State - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Temporal Table Support

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1182-171K
Language
English (United States)

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:

Row Row State BEGIN(TT)

Beginning Transaction Time

END(TT)

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.


Graphical representation of open or closed rows