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 
( 
   ProductId INT NOT NULL CONSTRAINT PK_Product PRIMARY KEY, 
   ProductName NVARCHAR(50) NOT NULL, 
   Price MONEY NOT NULL, 
   ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL, 
   ValidTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL, 
   PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo) 
) 
WITH (SYSTEM_VERSIONING = ON); 

You can identify all four elements related to temporal table creation from the previous list: two period columns, the period, and the SYSTEM_VERSIONING attribute. Note that all elements marked bold are predefined and you must write them exactly like this; data type, nullability, and default values for both period columns are also predefined and you can only choose their names and define data type precision. The data type must be DATETIME2; you can only specify its precision. Furthermore, the period definition itself is predefined too; you must use the period column names you have chosen in the previous step.

By defining period columns and the period, you have created the infrastructure required for implementing temporal tables. However, if you create a table with them but without the SYSTEM_VERSIONING attribute, the table will not be temporal. It will contain an additional two columns with values that are maintained by the system, but the table will not be a system-versioned temporal table.

The final, fourth part is to set the SYSTEM_VERSIONING attribute to ON. When you execute the previous code, you implicitly instruct SQL Server to automatically create a history table for the dbo.Product temporal table. The table will be created in the same schema (dbo), with a name according to the following naming convention:

MSSQL_TemporalHistoryFor_<current_temporal_table_object_id>_[suffix]. The suffix is optional and it will be added only if the first part of the table name is not unique. The following screenshot shows what you will see when you open SQL Server Management Studio (SSMS) and find the dbo.Product table:

You can see that all temporal tables have a small clock icon indicating temporality. Under the table name, you can see its history table. Note that columns in both tables are identical (column names, data types, precision, nullability), but also that the history table does not have constraints (primary key).

You can also specify the name of the history table and let SQL Server create it with the same attributes as described earlier. Use the following code to create a temporal table with a user-defined history table name:

USE WideWorldImporters; 
CREATE TABLE dbo.Product2 
( 
   ProductId INT NOT NULL CONSTRAINT PK_Product2 PRIMARY KEY, 
   ProductName NVARCHAR(50) NOT NULL, 
   Price MONEY NOT NULL, 
   ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL, 
   ValidTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL, 
   PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo) 
) 
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ProductHistory2)); 

What storage type is used for the automatically created history table? By default, it is a rowstore table with a clustered index on the period columns. The table is compressed with PAGE compression, if it can be enabled for compression (has no SPARSE or (B)LOB columns). To find out the storage type of the history table, use the following code:

SELECT temporal_type_desc, p.data_compression_desc  
FROM sys.tables t 
INNER JOIN sys.partitions p ON t.object_id = p.object_id 
WHERE name = 'ProductHistory2'; 

The result of the preceding query shows that PAGE compression has been applied:

temporal_type_desc    data_compression_desc 
--------------------- --------------------- 
HISTORY_TABLE         PAGE 

You can also see that the history table has a clustered index. The following code extracts the index name and the columns used in the index:

SELECT i.name, i.type_desc, c.name, ic.index_column_id 
FROM sys.indexes i 
INNER JOIN sys.index_columns ic on ic.object_id = i.object_id 
INNER JOIN sys.columns c on c.object_id = i.object_id AND ic.column_id = c.column_id 
WHERE OBJECT_NAME(i.object_id) = 'ProductHistory2'; 

The output of this query shows that the automatically created history table has a clustered index on the period columns and the name in the following ix_<history_tablename> format:

name                  type_desc     name          index_column_id 
--------------------- ------------- -----------   ------------
ix_ProductHistory2    CLUSTERED     ValidFrom          1
ix_ProductHistory2    CLUSTERED     ValidTo            2

This index is a good choice, when you want to query the history table only by using dates as criteria. However, when you want to browse through the history of one item, that would be an inappropriate index.

If the predefined implementation of the history table (rowstore and period columns in a clustered index) does not meet your criteria for historical data, you can create your own history table. Of course, you need to respect all constraints and limitations listed at the beginning of the chapter. The following code first creates a history table, then a temporal table, and finally assigns the history table to it. Note that, in order to proceed with the code execution, you need to remove the temporal table created in the first example in this chapter:

USE WideWorldImporters; 
ALTER TABLE dbo.Product SET (SYSTEM_VERSIONING = OFF);    
ALTER TABLE dbo.Product DROP PERIOD FOR SYSTEM_TIME;    
DROP TABLE IF EXISTS dbo.Product; 
DROP TABLE IF EXISTS dbo.ProductHistory; 
GO 
CREATE TABLE dbo.ProductHistory 
( 
   ProductId INT NOT NULL, 
   ProductName NVARCHAR(50) NOT NULL, 
   Price MONEY NOT NULL, 
   ValidFrom DATETIME2 NOT NULL, 
   ValidTo DATETIME2 NOT NULL 
); 
CREATE CLUSTERED COLUMNSTORE INDEX IX_ProductHistory ON dbo.ProductHistory; 
CREATE NONCLUSTERED INDEX IX_ProductHistory_NC ON dbo.ProductHistory(ProductId, ValidFrom, ValidTo); 
GO 
CREATE TABLE dbo.Product 
( 
   ProductId INT NOT NULL CONSTRAINT PK_Product PRIMARY KEY, 
   ProductName NVARCHAR(50) NOT NULL, 
   Price MONEY NOT NULL, 
   ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL, 
   ValidTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL, 
   PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo) 
) 
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ProductHistory)); 

You will learn how to alter and drop system-versioned tables in more detail later in this chapter. Here, you should focus on the fact that you can create your own history table with a clustered columnstore index on it. The following screenshot shows what you will see when you look at SSMS and find the created temporal table:

You can see that the table created by you acts as a history table and has a clustered columnstore index.