Temporal tables were included in SQL Server 2016 as a new feature. These temporal tables are also known as system-versioned tables allow SQL Server for keeping the history of data in the table automatically. A temporal table can be defined as a table for which PERIOD definition exists comprising of system columns. These columns are available with data-type of datetme2 where the period of validity is recorded by the system. It also has a history table associated with it where all the system records of previous versions are recorded. So the most significant function of Temporal Table is that it allows storing data in table at any point in time. Usually, what happens when you execute any delete statement on table, the old data will be overwritten and you will view latest data (after deletion) but with temporal table, you can view latest data and also the old data (without deletion). This is because temporal table value of all the entries or records is maintained and at any point can be determined.
Significance of Temporal Tables
ANSI SQL 2011 standard initially introduced the Temporal Tables, which later was released in SQL Server 2016. As mentioned above, this temporal tables or system-versioned tables let users to query updated or deleted data. On the other hand, a normal table would only return the current data from the table. This practical explanation may help: Suppose a column value is updated from 10 to 20, a normal table will retrieve the current value i.e. 20 only. However, temporal table will allow retrieving old value 10 also. This is done by keeping a history table with all the records, which were entered or updated.
Use Cases for Temporal Table:
- Indulgence of changing business trends
- Recording data changes done timely
- Reviewing changes done to the table
- Recover accidental data changes
- Recovering application errors
- Slowly changing dimensions
- Repairing record-level corruption
Temporal tables or system-versioned tables is actually an assertion table, which means that it records the complete details and entries like update or deletion made to the databases based on physical dates. Versioning is not supported by temporal tables, which means the versioning of records because of logical dates. This conduces that the time must be taken into consideration if you want to maintain the logical change like time.
How To Create Temporal (System-versioned) Table?
Pre-requisites & Limitations:
- A Primary Key has to be defined.
- Two columns for recording start and end date should be defined with data type of datetime2. These columns are referred as SYSTEM_TIME period columns.
- AFTER triggers are allowed but INSTEAD OF triggers are not allowed.
- In-memory OLTP cannot be used.
- Temporal and history table cannot be FILETABLE.
- Statements like INSERT and UPDATE cannot reference the SYSTEM_TIME period columns.
- Data available in history table cannot be changed.
Below mentioned script can be used to create a simple system-versioned table:
When you run this command as query, it will create two tables. One parent Temporal/System-versioned Table, and History table. Here the table with the name dbo.TestTemporal will be created. History table is nested under the parent table and if the name of history table is not specified, SQL Server automatically generates it as dbo.MSSQL_TemporalHistoryFor_objectidoftable.
>
Closer look at result:
History table possesses an identical set of columns but the constraints are removed. It carries its own set of indexes and statistics. In order to improve the performance, your own indexes can be created.
Conclusion
Temporal tables are significant additional feature of SQL Server 2016. As it will track the history of the records entered in the temporal tables. As the history records are maintained well, it is easy to recover the data at any point instead of only current data recovery. Possible use cases for temporal table can be type 2 dimensions in data warehouses, protection for accidental deletes or updates, auditing, etc.
EmoticonEmoticon