Working with Temporal Table

Practice Labs Module
Time
38 minutes
Difficulty
Intermediate

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.

Join over 3 million cybersecurity professionals advancing their career
Sign up with
Required fields are marked with an *
or

Already have an account? Sign In »

Overview

Introduction

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.

Exam Objectives

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.

Learning Partner
Comprehensive Learning

See the full benefits of our immersive learning experience with interactive courses and guided career paths.