How is data modified in a temporal table?
Data in a system-versioned temporal table is modified using regular DML statements with one important difference: period column data cannot be directly modified. When data is updated, it is versioned, with the previous version of each updated row is inserted into the history table.
How does the insert operation on the temporal table work?
When we perform the INSERT operation on the temporal table, the system sets the value of the SysStartTime column to the start time of the current transaction and mark the row as open. Let’s insert some rows in the ‘ Products ’ table and review how data is stored in this table.
What happens when you delete a record from the temporal table?
This row version is considered as closed. When we delete a record from the temporal table, the system will store the current version of the row in the history table and set the current transaction time as EndTime and delete the record from the current table. Let’s delete the record of ‘Headset’.
How to audit data changes using temporal table?
To audit the data changes for a specific table, we should perform the time-based analysis of temporal tables. To do that, we must use the ‘ FOR SYSTEM_TIME ’ clause with below temporal-specific sub-clauses to the query data across the current and history tables.
How to change alter column in temporal table?
Online option (WITH (ONLINE = ON) has no effect on ALTER TABLE ALTER COLUMN in case of system-versioned temporal table. ALTER column is not performed as online regardless of which value was specified for ONLINE option. You can use ALTER COLUMN to change IsHidden property for period columns.
Are there any limitations to using temporal tables?
There are some considerations and limitations to be aware of when working with temporal tables, due to the nature of system-versioning. Consider the following when working with temporal tables:
How to create a temporal table in SQL Server?
This ensures that temporal consistency checks are performed during the data insert into a staging table or when SYSTEM_TIME period is added to a pre-populated staging table. /*Create staging table with period definition for SWITCH IN temporal table*/ CREATE TABLE [dbo].
How to delete rows from a temporal table?
Set SYSTEM_VERSIONING = OFF and delete rows from current and history table but keep in mind that way system will not preserve history of changes. TRUNCATE, SWITCH PARTITION OUT of current table and SWITCH PARTITION IN history table are not supported while SYSTEM_VERSIONING = ON.
When to use partition switching in a temporal table?
When you insert new data, you need to account for the PERIOD columns if they are not HIDDEN. You can also use partition switching with system-versioned temporal tables. You can construct your INSERT statement when you have visible PERIOD columns as follows to account for the new PERIOD columns: