Temporal Tables

Question 1

When were Temporal Tables implemented in SQL Server?

Answer 1

SQL Server 2016

Question 2

What table elements are necessary to create a Temporal Table?

Answer 2

1. Primary Key Constraint
2. Two DateTime2 columns with your chosen precision to store the start and end of the validity period of the row (stored in the UTC time zone). The period is expressed as a closed-open interval, meaning that the start is inclusive and the end is exclusive.
3. The start column is marked with GENERATED ALWAYS AS ROW START.
4. The end column is marked with GENERATED ALWAYS A ROW END.
5. The start and end columns, as a pair, are marked with PERIOD FOR SYSTEM_TIME(<startcolname>,<endcolname>).</endcolname></startcolname>
6. The table option, WITH (SYSTEM_VERSIONING = ON), is set.
7. A linked history table exists, or sql will create it automatically

Question 3

Recite a table create statement for a Temporal Table

Answer 3

With a table that has a linked temporal table, the temporal table is not directly interacted with, only the primary table acted on.

CREATE TABLE dbo.Products

(

ProductID int NOT NULL CONSTRAINT PKProdID PRIMARY KEY(ProductID),

Col2…,

ValidFrom DATETIME2(3) GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,

ValidTo DATETIME2(3) GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,

PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)

)

WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ProductHistory ) ) ;

Question 4

What is the process, or series of steps, sql server goes thru when a history table is linked?

Answer 4

If the history table does not exist AND…

1. A table name has been supplied, then sql server creates the table using the given name AND.
   
   1. The new history table will have the follow characteristics:
      
      1. No Primary Key
      2. A clustered index on (<>,<>) with page compression.
      3. The period columns will not be marked with GENERATED ALWAYS AS ROW START/END nor will they be marked as HIDDEN.
      4. The period columns, (<>,<>), are not marked with PERIOD FOR SYSTEM_TIME.
      5. The history table is not marked as WITH (SYSTEM_VERSIONING = ON).
2. No table name is supplied, then a table name is generated using this convention: MSSQL_TemporalHistoryFor_<object_id>.</object_id>

If the history table does exist, then the history table name has been supplied, and sql server will check for its existence and then it’ll verify that the ValidFrom and ValidTo columns have no overlapping values. This check can be disabled using DATA_CONSISTENCY_CHECK = OFF.

Question 5

How does Management Studio show a history table is linked?

Answer 5

1. A clock icon is added to the table icon in the Table Tree.
2. The text (System-Versioned) is appended to the end of the table name.
3. The history table is added within the Table’s Folder.

Question 6

How is an existing table altered to become a temporal table?

Answer 6

BEGIN TRANSACTION

ALTER TABLE dbo.Product

ADD ValidFrom DATETIME2(3) GENERATED ALWAYS AS ROW START HIDDEN NOT NULL CONSTRAINT [DFT_Product_ValidFrom] DEFAULT (‘19000101’),

ADD ValidTo DATETIME2(3) GENERATED ALWAYS AS ROW END HIDDEN NOT NULL CONSTRAINT [DFT_Product_ValidTo] DEFAULT (‘99991231 23:59:59:999’),

PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);

ALTER TABLE dbo.Product

SET ( SYSTEM_VERSIONING = ON ( HISTORY_TABLE = dbo.ProductHistory ) );

ALTER TABLE dbo.Product

DROP CONSTRAINT

[DFT_Product_ValidFrom],

[DFT_Product_ValidTo];

END TRANSACTION;

Question 7

Recite the syntax for recalling data for a point in time.

Answer 7

SELECT

FROM dbo.Product

FOR SYSTEM_TIME AS OF ‘20161101 14:06:00:000’

SELECT

FROM dbo.Product FOR SYSTEM_TIME AS OF ‘20161101 14:06:00:000’ AS T1

JOIN dbo.Product FOR SYSTEM_TIME AS OF ‘20161102 14:06:00:000’ AS T2

ON T1.ProductID = T2.ProductID

SELECT

FROM dbo.Product

FOR SYSTEM_TIME

FROM ‘20161101 14:06:00:000’

TO ‘20161102 14:06:00:000’

SELECT

FROM dbo.Product

FOR SYSTEM_TIME

BETWEEN ‘20161101 14:06:00:000’

AND ‘20161102 14:06:00:000’

SELECT

FROM dbo.Product

FOR SYSTEM_TIME

CONTAINED IN –> The ValidFrom AND ValidTo is entirely contained within the listed dates.

( ‘20161101 14:06:00:000’, ‘20161102 14:06:00:000’ )

Question 8

Why add a Check Constaint to the ValidTo Column?

Answer 8

When using a CONTAINED IN Query the optimizer is not smart enough to know that it only need to look in the history table (ProductHistory).

ALTER TABLE dbo.Product

ADD CONSTRAINT CHK_Product_ValidTo

CHECK ( ValidTo = ‘99991231 23:59:59.000’ )

Question 9

What does the ALL subclause do?

Recite the ALL subclause syntax.

Answer 9

The ALL subclause returns all versions of the date in the primary and history tables.

SELECT

FROM dbo.Product FOR SYSTEM_TIME ALL;

Question 10

How is data presented within a desired target time zone?

Answer 10

The period columns in a History table are stored in the UTC time zone.

Use the AT TIME ZONE function to target time zones.

SELECT

ValidFrom AT TIME ZONE ‘UTC’ AT TIME ZONE ‘Pacific Standard Time’ AS ValidFrom,

CASE

WHEN ValidTo = ‘99991231 23:59:59.000’

THEN ValidTo AT TIME ZONE ‘UTC’

ELSE ValidTo AT TIME ZONE ‘UTC’ AT TIME ZONE ‘Pacific Standard Time’

END AS ValidTo

FROM dbo.Product FOR SYSTEM_TIME ALL;

AT TIME ZONE ‘UTC’ AT TIME ZONE ‘Pacific Standard Time’ switches the time from UTC to Pacific Standard Time.

The Case statement keeps the term value the same instead of switching it when the value is at term.