Mastering SQL Server 2017

Creating temporal tables

To support the creation of temporal tables, the CREATE TABLE and ALTER TABLE Transact-SQL statements have been extended.

To create a temporal table, you need to perform the following steps:

1. Define a column of DATETIME2 data type for holding the info since when the row has been valid from a system point of view

2. Define a column of DATETIME2 data type for holding the info until the row is valid from the same point of view
3. Define a period for system time by using previously defined and described columns
4. Set the newly added SYSTEM_VERSIONING table attribute to ON

The following code creates a new temporal table named Product in the dbo schema in the WideWorldImporters database:

USE WideWorldImporters; CREATE TABLE dbo.Product ...