In this article, we are going to learn How to Recover Data from a SQL Server Temporal Table. Temporal is a SQL database feature introduced in ANSI SQL 2011, and full support for Temporal Tables was started in the year 2016.
Temporal or system-versioned Tables provide information about the data which is available at any point in time rather than at the current moment in time. As the Temporal Table is used to understand the current and the history table, an additional ‘Datetime2’ columns or period column is used to record the period of validity for each row whenever a row is modified. It also helps decide the validity period of each row.
In addition to the period columns, the temporal table contains a reference to another table having a mirrored schema. The system uses this table to store the previous version of the row, as and when there are any changes such as deletion or updates in the row. As the additional table stores previous row entry data, it is also referred to as History Table and the main table or current table constitutes a Temporal Table.
Depending on the requirement, there is an option to:
- Create new system-versioned Temporal Table
- Modify the existing database by adding temporal table attributes to the table schema.
A typical workflow with system versioned Temporal Table can be depicted as:
The above image provides an insight into the following:
- Create the Temporal Table on a system for a set time and
- Alter the Table-entries with the help of Temporal Table
System versioning is implemented as a pair – one for the current and another for the history table. The following datetime2 columns help define the validity period for each row:
- SysStartTime column: is also known as the period start column. It is used by the system to record the start-time in a row in a particular column
- SysEndTime: is also known as the period end column. It is used by the system to record the end-time in a row in a particular column
Let’s explain this with a hypothetical example: We have considered employee information available in an HR-database.
This is an example of the Create Table. MS SQL administrators can insert, update, delete and merge the Table entries with the help of proper Temporal Tables attributes.
INSERT: Feed the value of SysStartTime (with a maximum value 9999-12-31) to initiate the time of the current transaction, as prescribed in the system clock. Next, assign the value for SysEndTime, where the maximum value remains the same.
UPDATE: System stores previous row value in the history table and sets the value for the SysEndTime column to the start time of the present transaction on the basis of the system clock.
The row is updated with a new value and the system starts the value for the SysStartTime column to the current table for the SysEndTime
DELETE: In Delete, all other functions are the same as Update, except that the row is closed and the period is recorded for which the previous row was valid.
MERGE: The system understands that all three functions – an Insert, an Update and a Delete are executed, depending on the specifications in the Merge – statement.
All these functions are backed with creating a historical version of rows with the help of Temporal Tables and that too without putting any extra effort.
Limitations of Temporal Table:
It can be observed that for every entry – insert, update, delete or merge, historical data is created by the system-versioned Tables. When the database is not so big and there are not many row changes, the historical data remains within the size-limits and MS SQL Database is accessible. With an increase in the size of the database, the number of queries is increased. Sometimes, the same queries are updated n-number of times and for each query, a historical system-versioned Table is also created.
Such instances ultimately lead to unnecessary data-piling, and extreme cases may even result in database inaccessibility. In some extreme cases, the MS SQL database may also get corrupt. Such corrupt databases can be repaired with the help of MS SQL database repair software. The database repair software can repair .mdf and .ndf files and addresses all types of SQL database repair needs. The software supports recent as well as older versions of MS SQL.
Temporal tables must have a primary key defined to correlate records between the current and the history table. As the history table does not have a primary key defined, such tables are created unnecessarily.
Insert and update functions do not reference the system_time period columns. Attempts to insert the values are blocked.
Conclusion
The temporal table, introduced in SQL Server 2016 and elaborated in SQL Server 2017, is an exciting feature that tracks history for the data in a separate history table. Though it is a useful feature, the problem is ‘history table doesn’t contain primary index’. Absence of primary index may not necessarily help in search criteria and may end up piling up redundant data.
Such data piling-up is undesired, as large-sized MS SQL databases have high chances of database corruption.
Sometimes, there may be an unavoidable need for the temporal table which is bound to result in database accumulation. This may further result in corruption. For this eventuality, it is best to keep an MS SQL database recovery software in easy reach to resolve corruption in the MS SQL database.
That’s all, In this article, we have explained How to Recover Data from a SQL Server Temporal Table. I hope you enjoy this article. If you like this article, then just share it. If you have any questions about this article, please comment.