Up to this point in the book, we’ve been dealing with databases that store facts concerning current time. To explain this, let’s take a look at how a database system modifies a row. Logically, the row is first deleted and the modified row is inserted in the table. In other words, an UPDATE statement is a combination of a DELETE statement and an INSERT statement, which are executed in that order.
As of SQL Server 2016, a special form of table called a temporal table is supported. This is the first step in enabling this database system to handle temporal data. A temporal table is a table that contains two time-variant columns specifying the start and end time of the currentness of row data. The support for temporal tables enables you to track and manage multiple versions of your data. By defining a temporal table, you are instructing the database system to automatically capture changes made to the state of your table and to save old versions of a table’s rows in a history table. (A history table is a separate table with the same structure as the table that contains current data.)
Temporal tables can be used in many application scenarios where time-variant attributes are involved. The following two cases belong to such scenarios:
- An insurance company needs to determine the terms of a client’s policy in effect at the particular point in time when the accident occurred.
- A travel agency wants to detect inconsistent travel plans of a client, such as when, for instance, hotel bookings in two different cities overlap.
In case of database systems, which do not support temporal data, such scenarios are generally implemented using triggers. On the other hand, as you will see in this section, simple SQL statements allow users to automatically maintain a history of database changes or track time-variant data, eliminating the need for programming such logic.
The system uses a closed-open approach for modeling time periods. This means that the period’s start time is included in the time period, but its end time is not.
1. Creation of Temporal Tables
Temporal tables are created using the CREATE TABLE statement, which is enhanced with several clauses concerning time-variant columns. Example 30.13 shows the creation of such a table.
Example 30.13
USE sample;
CREATE TABLE dept_temp
(dept_no CHAR(4) NOT NULL PRIMARY KEY CLUSTERED,
dept_name CHAR(25) NOT NULL,
location CHAR(30) NULL,
start_date DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN,
end_date DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN,
PERIOD FOR SYSTEM_TIME (start_date, end_date))
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Dept_History));
The dept_temp table is similar to the department table used in previous chapters but contains two additional columns: start_date and end_date. (All time-variant columns must be specified using the DATETIME2 data type.) The GENERATED ALWAYS AS ROW START option specifies the column with values identifying the start time, while GENERATED ALWAYS AS ROW END specifies the column with values identifying the end time of the corresponding time period. The additional PERIOD FOR SYSTEM_TIME clause contains the names of both time-variant columns, where the first name defines the beginning of a time period and the second the end of a time period. (This clause implicitly specifies that start_date <= end_date.)
The WITH SYSTEM_VERSIONING option must be enabled if you want to create a history table, where old versions of rows are stored. The name of the history table can be specified explicitly by the user, as in Example 30.13, or implicitly by the system, if the HISTORY_TABLE option is omitted. (I strongly recommend using the explicit definition of the history table.)
The most important property of temporal tables is that old versions of rows are preserved, but they are handled differently than rows with current values when modification operations (UPDATE and DELETE) are executed. In this case, only the rows stored in the current table are modified. Similarly, all specified constraints are valid only for the current table.
The HIDDEN option specifies the time-variant columns (the columns start_date and end_date in Example 30.13) as hidden columns. This means that values of these columns will not be displayed implicitly. In other words, the following SELECT statement,
SELECT * FROM dept_temp;
will display only nonhidden columns. To display values of all columns of the dept_temp table, you have to specify the hidden columns explicitly:
SELECT *, start_date, end_date FROM dept_temp;
2. Temporal Tables and DML Statements
As you will see in this section, the semantics of the DML statements UPDATE and DELETE in relation to temporal tables is different than the semantics of the same statements in relation to conventional tables. On the other hand, the syntax of these statements does not change.
Example 30.14 shows the insertion of three rows in the dept_temp table. (To make the examples easier to understand, all further results in this chapter show only the date, hour, and minute portions of time-variant columns.)
Example 30.14
USE sample;
INSERT INTO dept_temp (dept_no, dept_name, location)
VALUES (‘d1’, ‘Research’, ‘Dallas’ );
INSERT INTO dept_temp (dept_no, dept_name, location)
VALUES (‘d2’, ‘Accounting’, ‘Seattle’);
INSERT INTO dept_temp (dept_no, dept_name, location)
VALUES (‘d3’, ‘Marketing’, ‘Dallas’);
The content of the dept_temp table after execution of three INSERT statements in Example 30.14 is
Each INSERT statement in Example 30.14 contains the list of time-invariant columns. This is necessary, because the table’s schema contains two additional columns, and the values of these columns are inserted by the system; that is, they cannot be inserted by the user. When inserting each row into the dept_temp table, the system generates appropriate DATETIME2 values for time-variant columns. Hence, the system assigns the current time to the start_date column and the value “forever” (9999-12-31 23:59) to the end_date column. (The required information will be automatically recorded by the system.)
Example 30.15 shows how temporal tables can be modified using the UPDATE statement.
Example 30.15
USE sample;
UPDATE dept_temp
SET location = ‘Houston’
WHERE dept_no = ‘d1’;
The following result shows first the content of the current table and after that the content of the history table.
(current table)
(history table)
Let’s take a look at how the system executes the UPDATE statement. As shown in the result of Example 30.15, the system updates the value of the row in the current table. Also, it moves a copy of the “old” row to the history table. For both tables, the system determines the start and end times for these rows. In other words, the system sets the value for the end time of the row in the history table to the time of the transaction when UPDATE has been executed.
The deletion of rows is similar to their modification, as Example 30.16 demonstrates.
Example 30.16
USE sample;
DELETE FROM dept_temp
WHERE dept_no = ‘d2’;
The content of the current table after deletion is
and the content of the corresponding history table is
When you delete rows from the current table, the system removes data from that table and creates old versions of that data, which is stored in the corresponding history table. The value for the end_time column of each row stored in the history table is set by the system to the transaction start time of the DELETE statement. (As can be seen from the result of Example 30.16, the transaction start time of the DELETE statement has been set to 2019-09-20 06:50.)
NOTE Rows in a history table cannot be modified or deleted. In other words, neither DELETE nor INSERT can be applied to a history table.
3. Querying Temporal Data
In contrast to INSERT, UPDATE, and DELETE, the SELECT statement contains extensions that allow you to query time-variant data in various ways. FOR SYSTEM_TIME is a clause, which has five subclauses:
- FOR SYSTEM TIME AS OF <date_time>
- FOR SYSTEM TIME FROM <start_date_time> TO <end_date_time>
- FOR SYSTEM TIME BETWEEN <start_date_time> AND <end_date_time>
- FOR SYSTEM TIME CONTAINED IN (<start_date_time> , <end_date_time>)
- FOR SYSTEM TIME ALL
These subclauses allow you to specify different forms of time periods in your query. The first subclause, AS OF <date_time>, enables you to query data as of a certain point in time. The FROM…TO subclause enables you to query data inside the specified time interval. In this case, the specified start time is included in the time interval but the specified end time is not.
The BETWEEN subclause is similar to FROM…TO. The only difference is that rows with the specified end time are also included in the result. The CONTAINED IN subclause returns a result with the values of all old versions that were opened and closed within the specified time range defined by the two parameters of that subclause. Records that became active exactly on the lower boundary or ceased being active exactly on the upper boundary are included. The ALL clause returns all the rows from both tables (the current table and the history table).
When we discuss temporal queries, the first issue to address is what happens if you do not use the SYSTEM_TIME clause at all in your query. In that case, to display current rows, you have to reference the current table. And to display historical rows, you have to reference the corresponding history table, separately. Now, we will see what happens when you use that clause. Examples 30.17 and 30.18 show the use of the first two subclauses of SYSTEM_TIME.
NOTE You need to update the date constants in the following example to produce any results.
Example 30.17
USE sample;
SELECT * FROM dept_temp
FOR SYSTEM_TIME AS OF ‘2019-12-20 06:41:07.2902041’;
The result is
As you can see from the result, the SELECT statement in Example 30.17 returns all rows containing the specified point in time. Note that both the current table and history table are searched for the specified time. (The AS OF clause can be used to implement logic of the first application scenario listed at the beginning of the section “Temporal Tables”)
NOTE You need to update the date constants in the following example to produce any results.
Example 30.18 shows you how to use the FROM…TO subclause.
Example 30.18
USE sample;
SELECT * FROM dept_temp FOR SYSTEM_TIME
FROM ‘2019-06-19 06:41:07.2902041’ TO ‘2019-06-20 06:41:07.2902041’ ;
The result of this query is
If you compare the results of the last two queries, you will see that the row with dept_no=d3 is not selected in Example 30.18. The reason is that the FROM…TO subclause excludes the end time of the specified time period. If you want to include the third row to your result, you have to use the BETWEEN…AND subclause.
3.1. Editing Information Concerning Temporal Data
Metadata concerning temporal tables is displayed in three catalog views: sys.tables, sys.columns, and sys.periods. The first two views contain additional columns in relation to temporal tables , while sys.periods returns a row for each existing temporal table. The query in Example 30.19 displays the name and the type of all existing temporal tables in your database.
Example 30.19
USE sample;
SELECT name, type_desc FROM sys.tables
WHERE object_id IN (SELECT object_id FROM sys.periods);
If you want to determine whether the particular table is temporal, you can use the temporal_type column of the sys.tables catalog view. Example 30.20 shows this for the dept_temp table.
Example 30.20
SELECT temporal_type FROM sys.tables
WHERE object_id = OBJECT_ID(‘dbo.dept_temp’, ‘U’);
The value of the temporal_type column can be 0 (for nontemporal table), 1 (for history table), or 2 (for temporal table). So, the result of Example 30.20 is 2.
4. Converting Nontemporal Tables into Temporal Tables
Two additional statements that you can use when working with temporal tables are ALTER TABLE and DROP TABLE. The ALTER TABLE statement is used to convert an existing nontemporal table into a temporal one. (A scenario in which you might want to do such a conversion is if you have already implemented temporal logic using triggers with one of the previous versions of SQL Server and want to use features of temporal tables.)
Example 30.21 shows how a nontemporal table can be converted into a temporal one. (The department table is the well-known table of the sample database. The content of the table should be as it was when we started to use this table.)
Example 30.21
USE sample;
ALTER TABLE department ADD PRIMARY KEY (dept_no);
GO
ALTER TABLE department ADD
SysStartTime datetime2 NOT NULL DEFAULT GETUTCDATE(),
SysEndTime datetime2 NOT NULL DEFAULT CONVERT(DATETIME2,
‘9999-12-31 23:59:59.99999999’)
GO
ALTER TABLE department
ADD PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime);
GO
ALTER TABLE department
alter column SysStartTime ADD HIDDEN;
GO
ALTER TABLE department
alter column SysEndTime ADD HIDDEN;
ALTER TABLE department
SET (SYSTEM_VERSIONING = ON
(HISTORY_TABLE = dbo.department_history, DATA_CONSISTENCY_CHECK = ON));
Example 30.21 contains six statements. The first ALTER TABLE statement specifies the PRIMARY KEY clause, because each temporal table must be defined with the corresponding primary key. The second statement adds two new columns of the DATETIME2 data type, while the third one declares them as the columns, which specify system start time and system end time, respectively. The fourth and the fifth statements specify both columns as hidden columns.
Finally, the last statement transforms the nontemporal table (department) into a temporal one. Also, the corresponding history table, department_history, is created. As we already know from Example 30.13, enabling the SYSTEM_VERSIONING clause is a requirement when creating a history table. (The DATA_CONSISTENCY_CHECK clause performs a data consistency check, which ensures that existing rows do not overlap and that temporal requirements are fulfilled for every individual row.)
To drop the current table and its corresponding history table, you have to disable system versioning first. After that both tables can be dropped using the DROP TABLE statement, as shown in Example 30.22.
Example 30.22
USE sample;
ALTER TABLE department set (SYSTEM_VERSIONING = OFF);
GO
DROP TABLE dbo.department;
GO
DROP TABLE dbo.department_history;
Source: Petkovic Dusan (2020), Microsoft SQL Server 2019: A Beginner’s Guide, Seventh Edition-McGraw-Hill Education.