Working with Temporal Table
The "Working with Temporal Table" module provides you with the instructions and devices to develop your hands-on skills in the following topic: Working with system-versioned temporal tables, Querying data in system-versioned temporal tables.
The Working with Temporal Table module provides you with the instructions and devices to develop your hands-on skills in the following topic.
- Working with system-versioned temporal tables
- Querying data in system-versioned temporal tables
Lab time: It will take approximately 40 minutes to complete this lab.
The following exam objectives are covered in this lab:
- Create a system-versioned temporal table
- with a user-defined history table
- with an anonymous history table
- with a default history table
- Query data in a temporal table using AS OF, FROM TO, BETWEEN, CONTAINED IN and ALL sub-clauses
Exercise 1 - Working with System-Versioned Temporal Tables
The temporal table is a new feature introduced in SQL Server 2016. It is also called the system-versioned table as the validity period of the rows in this table is managed by the system. Each system-versioned temporal table creates a corresponding history table assigned to it. The system-versioned temporal table consists of current or latest values for each record. The history table maintains all the versions of the records along with the period of validity. The current system-versioned and history tables have two fields with datetime2 data type also referred to as period columns. The two-period columns are as follows:
- SysStartTime: It records the start date-time of the record in a table
- SysEndTime: It records the end date-time for the record in a table
The system-versioned temporal table helps record and monitor the period of validity of the table records and the modifications done to the records.
Some of the advantages of using system-versioned temporal tables are as follows:
- It helps in auditing and monitoring the data changes
- It facilitates in extracting analysis and trends over time
- It helps in recovering accidental data changes
There are three ways to create system-versioned temporal tables. These are as follows:
- Temporal table with anonymous history table: In this method, the system creates a history table with default system-generated name and configuration. The configuration of the history table includes field definitions and additional index creation.
- Temporal table with default history table: In this method, you refer to the schema name and provide a history table name in the query. The system creates history table in the given schema with the system-generated configuration.
- Temporal table with user-defined history table: In this method, you create and configure the history table and refer to the already created history table while creating the system-versioned temporal table.
You can use the SYSTEM_VERSIONING clause to provide a reference to the history table.
In this exercise, you will learn to use all the three methods to create a system-versioned temporal table.
Exercise 2 - Querying Data in System-Versioned Temporal Tables
You can retrieve the actual state of the table records with the help of temporal-specific sub-clauses that can be used with SELECT statements. The temporal-specific sub-clauses along with the FOR SYSTEM_TIME clause help in performing the time-based analysis of the table records. You can query the current and history tables using the temporal-specific sub-clauses. The temporal-specific sub-clauses are as follows:
- AS OF <date_time>: It helps in retrieving the version of the records as on a specific date and time in the past.
- BETWEEN <start_date_time> AND <end_date_time>: It retrieves the version of the records between the given time frame.
- FROM <start_date_time> TO <end_date_time>: It retrieves the version of the records from the given start date-time till the given end date-time.
- CONTAINED IN (<start_date_time>, <end_date_time>): It retrieves the version of the records that completely fall within the specified time frame.
- ALL: It retrieves all the versions of the records. The ALL sub-clause does not bind the result set with any date and time restrictions.
In this exercise, you will learn to use all the temporal-specific sub-clauses to query the system-versioned temporal tables.
IT & Cybersecurity certification hands on practice labs and practice exams for certifications and skill development.
See the full benefits of our immersive learning experience with interactive courses and guided career paths.