Dropping System Versioning and System-Time - Analytics Database - Teradata Vantage

ANSI Temporal Table Support

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-10-30
dita:mapPath
jqu1628112571823.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esa1472244798285
lifecycle
latest
Product Category
Teradata Vantageā„¢

System-versioned system-time tables are typically used for regulatory and compliance purposes, and for keeping a table-resident history of database operations on the table data. Consequently, most types of ALTER TABLE changes to these tables are not allowed. However, ALTER TABLE can be used to remove system versioning. After system versioning has been removed from a temporal table, the table becomes a regular nontemporal table, and all normal ALTER TABLE operations are permitted.

Use the following ALTER TABLE syntax to remove system versioning from a system-time table:

ALTER TABLE  your_system_time_table  DROP SYSTEM VERSIONING;

Where your_system_time_table is the name of a system-versioned system-time table.

Dropping the SYSTEM VERSIONING from a system-time table deletes all closed rows from the table, and makes the table a nontemporal table.

To drop the system-time columns, including the derived period column and its component beginning and ending bound TIMESTAMP columns, use the following ALTER TABLE syntax:

ALTER TABLE  your_system_time_table  DROP PERIOD FOR SYSTEM_TIME;

Where, again, your_system_time_table is the name of a system-versioned system-time table.

Dropping the system-time derived period column will automatically drop the two component columns.

You must drop the SYSTEM VERSIONING from a system-time table before you can drop the SYSTEM_TIME derived period column and component columns.