How to Work with SQL:2011 System-Versioned Tables
System-versioned tables have a different purpose than application-time period tables in SQL, and consequently work differently. Application-time period tables enable you to define periods of time and operate on the data that falls within those periods. In contrast, system-versioned tables are designed to create an auditable record of exactly when a data item was added to, changed within, or deleted from a database.
For example, it is important for a bank to know exactly when a deposit or withdrawal was made, and this information must be kept for a period of time designated by law. Similarly, stock brokers need to track exactly when a purchase transaction was made. There are a number of similar cases, where knowing when a particular event occurred, down to a fraction of a second, is important.
Applications such as the bank application or the stock broker application have strict requirements:
Any update or delete operation must preserve the original state of the row before performing the update or delete operation.
The system, rather than the user, maintains the start and end times of the periods of the rows.
Original rows that have been subjected to an update or delete operation remain in the table and are referred to as historical rows. Users can’t modify the contents of historical rows or the periods associated with the rows. Only the system may update the periods of rows in a system-versioned table. This is done by updating the non-period columns of the table or as a result of row deletions.
These constraints guarantee that the history of data changes is immune to tampering, thus meeting audit standards and complying with government regulations.
System-versioned tables are distinguished from application-time period tables by a couple of differences in the CREATE statements that create them:
Whereas in an application-time period table the user can give any name to the period, in a system-versioned table, the period name must be SYSTEM_TIME.
The CREATE statement must include the keywords WITH SYSTEM VERSIONING. Although SQL:2011 allows the data type for the period start and period end to be either DATE type or one of the timestamp types, you will almost always want to use one of the timestamp types. Of course, whatever type you choose for the start column must also be used for the end column.
To illustrate the use of system-versioned tables, consider tables created for employees and departments. You can create a system-versioned table with the following code:
CREATE TABLE employee_sys ( EmpID INTEGER, Sys_Start TIMESTAMP(12) GENERATED ALWAYS AS ROW START, Sys_End TIMESTAMP(12) GENERATED ALWAYS AS ROW END, EmpName VARCHAR(30), PERIOD FOR SYSTEM_TIME (SysStart, SysEnd) ) WITH SYSTEM VERSIONING;
A row in a system-versioned table is considered to be a current system row if the current time is contained in the system-time period. Otherwise it is considered to be a historical system row.
System-versioned tables are similar to application-time period tables in many respects, but there are also differences. Here are a few:
Users may not assign or change the values in the Sys_Start and Sys_End columns. These values are assigned and changed automatically by the DBMS. This situation is mandated by the keywords GENERATED ALWAYS.
When you use the INSERT operation to add something into a system-versioned table, the value in the Sys_Start column is automatically set to the transaction timestamp, which is associated with every transaction. The value assigned to the Sys_End column is the highest value of that column’s data type.
In system-versioned tables, the UPDATE and DELETE operations operate only on current system rows. Users may not update or delete historical system rows.
Users may not modify the system-time period start or end time of either current or historical system rows.
Whenever you either use the UPDATE or DELETE operation on a current-system row, a historical system row is automatically inserted.
An update statement on a system-versioned table first inserts a copy of the old row, with its system end time set to the transaction timestamp. This indicates that the row ceased to be current at that timestamp. Next, the DBMS performs the update, simultaneously changing the system-period start time to the transaction timestamp.
Now the updated row is the current system row as of the transaction timestamp. UPDATE triggers for the rows in question will fire, but INSERT triggers will not fire even though historical rows are being inserted as a part of this operation.
A DELETE operation on a system-versioned table doesn’t delete the specified rows. Instead it changes the system-time period end time of those rows to the system timestamp. This indicates that those rows ceased to be current as of the transaction timestamp. Now those rows are part of the historical system rather than the current system. When you perform a DELETE operation, any DELETE triggers for the affected rows will fire.