Temporal Tables

Question 1

What is the syntax for creating a Temporal Table?

Answer 1

CREATE TABLE dbo.TableName
(
PrimKeyField int PRIMARY KEY
, [ValidFrom] datetime2 GENERATED ALWAYS AS ROW START
, [ValidTo] datetime2 GENERATED ALWAYS AS ROW END
, PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.TableHistory));

Question 2

What are the necessary elements for a Temporal Table?

Answer 2

Primary Key
valid from column (datetime2) with GENERATED ALWAYS AS ROW START
valid to column (datetime2) with GENERATED ALWAYS AS ROW END
PERIOD FOR SYSTEM_TIME([VALIDFROMCOLUMN],[VALIDTOCOLUMN])

WITH (SYSTEM_VERSIONING = ON(HISTORY_TABLE=[HISTORYTABLE]));

Question 3

How do you drop a Temporal Table?

Answer 3

ALTER TABLE [TABLENAME] SET (SYSTEM_VERSIONING=OFF)
GO

DROP TABLE [TABLENAME]
GO

DROP TABLE [TABLEHISTORYNAME]
GO

Question 4

What is the query syntax for selecting data from a temporal table?

Answer 4

FOR SYSTEM TIME 
[AS OF ]
[FROM  TO ]
[BETWEEN  AND ]
[CONTAINED IN ( , )]
[ALL]