- In system-time tables, a row can be either “open” or “closed.”
- Open rows have system-time ending bound values of UNTIL_CLOSED, the maximum possible system timestamp value, indicating they are still active in the database and able to participate in normal SQL operations.
- Closed rows have system-time ending bound values that are anything less than (prior to) UNTIL_CLOSED. Closed rows cannot be changed. The system-time period end value for these rows indicates when they became inactive in the database. These are rows that have been logically deleted from the table, but remain physically there for record-keeping and history purposes.
Closed rows include rows that have been explicitly deleted from the table and rows with values that have been superseded by row modifications since the original row was added to the table. Such modifications close the original row, and create a new row in the table to hold the changed information. For more information on how row modifications generate new table rows in temporal tables, see Modifying Temporal Tables.
In valid-time tables, a row can be either current, future, or history, depending on how the time period during which the row information is valid relates to the current time.
- Current rows have valid-time periods that overlap the current time. The data in these rows is currently in effect.
- Future rows have a valid time that commences after the current time. The data in these rows is not yet in effect, but will be when their valid-time period includes the current time unless the information in the row changes before it becomes current. An example would be information describing terms of a contract that does not begin until next month.
- History rows have valid-time periods that have ended before the current time. The data in these rows was true at some point, but is no longer valid. The information may have become invalid because the original valid-time period has been exceeded, such as for an expired contract, or because of a change to the row data that made the original data no longer untrue, such as a change in terms of an existing valid contract.
- Rows in valid-time tables can also be discussed as being either valid or invalid at any point in time, depending on whether the valid-time period includes the point in time.
- In bitemporal tables the two temporal dimensions are distinct from each other, so a row is simultaneously open or closed in the system-time dimension, and current, future, or history in the valid-time dimension.
In a sense, the system-time dimension takes precedence. If the valid-time period of a row in a bitemporal table is in the future, but the row is closed in the system-time dimension — indicating the row has been deleted from the table — the row is not considered to be a future row, because it is no longer active in the database. The row has been logically deleted from the database, and serves only to show the state of the row at the time the row was deleted or modified.
Rows in temporal tables can be broadly classified according to their temporal time periods.