Temporal table restrictions

System-period temporal tables are subject to a number of restrictions.

Following are the restrictions for system-period temporal tables:

  • Both the system-period temporal table and its history table must be in the same schema.
  • To update or delete data from a versioned system-period temporal table, both the temporal table and its history file must be journaled.
  • ALTER or CREATE OR REPLACE TABLE SQL statements that cause a potential loss of data are not supported on system-period temporal tables.
  • The following operations are not allowed for system-period temporal tables:
    • ALTER TABLE DROP COLUMN
    • ALTER TABLE ADD GENERATED COLUMN
  • The TRUNCATE statement is not supported against a system-period temporal table.
  • A distributed table cannot also be a system-period temporal table.
  • The following query operations are not allowed against a system-period temporal table:
    • A period specification cannot be specified within a query that references a distributed table, a table with a read trigger, uses ICU 2.6.1 sort sequence, or has more than 1000 table references.
    • A period specification cannot be specified for a native logical file.
    • A period specification cannot be specified for a table or view that references a column in a CONTAINS or SCORE function.
    • A period specification cannot be specified for a view where the view definition includes an external function that is not NO SQL or an SQL function that is not inline capable.