Temporal in SQL Server 2016 - Creating/Querying Tables

Below are two example on how to create Temporal tables in SQL Server 2016
They will both store histroic information on data that gets into the table

-- A History table is not specified
CREATE TABLE dbo.Customer
(
    CustomerID        int NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    CustomerName      varchar(50) 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 );
GO


-- A History table is specified
CREATE TABLE dbo.Customer
(
    CustomerID        int NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    CustomerName      varchar(50) 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.CustomerHistory) );
GO

To query Temporal tables, you can use the code below. The code below will automatically look in the CustomerHistory table if its not in the Customer table.

SELECT * FROM dbo.Customer FOR SYSTEM_TIME AS OF '2015-1-1'

Temporal Tables Limitations

* Usage of Replication technologies is limited.
* INSERT and UPDATE statements cannot reference the SYSTEM_TIME period columns.
* Direct modification of the data in a history table is not permitted.
* Temporal querying over Linked Server is not supported.
* History table cannot have constraints (PK, FK, Table or Column constraints).
* TRUNCATE TABLE is not supported while SYSTEM_VERSIONING is ON
* INSTEAD OF triggers are not permitted on either the tables.