Temporal Tables

Question 1

What elements are required in the table definition for a table to be capable of being a temporal table?

Answer 1

When you create a system-versioned temporal table, you need to make sure the table definition has all the following elements:

Image A primary key

Image Two columns defined as DATETIME2 with any precision, which are non-nullable and represent the start and end of the row’s validity period in the UTC time zone

Image A start column that should be marked with the option GENERATED ALWAYS AS ROW START

Image An end column that should be marked with the option GENERATED ALWAYS AS ROW END

Image A designation of the period columns with the option PERIOD FOR SYSTEM_TIME (, )

Image The table option SYSTEM_VERSIONING, which should be set to ON

Image A linked history table (which SQL Server can create for you) to hold the past states of modified rows

Optionally, you can mark the period columns as hidden so that when you’re querying the table with SELECT * they won’t be returned and when you’re inserting data they’ll be ignored.

Question 2

Describe how the differences between the temporal table and its associated history table.

Answer 2

The history table is created with a mirrored schema, but has the following differences:

No primary key

a clustered index on (, ), with page compression if possible

Period columns that are not marked with any special options, like GENEREATED ALWAYS AS ROW START/END or HIDDEN

The history table is not marked with the option SYSTEM_VERSIONING

Question 3

Describe the statement to enable versioning on a table.

Answer 3

SET (SYSTEM_VERSIONING = ON ( HISTORY_TABLE = ) );

Question 4

Why is this statement necessary ALTER TABLE dbo.Employees ADD PERIOD FOR SYSTEM_TIME (sysstart, sysend);

Answer 4

This statement designates the columns in (, ) as period columns, which are a requirement for a table to be able to SYSTEM_VERSIONING to ON.

Question 5

IF you want to see a past state of the data, correct to a certain point or period of time, you will query the current table followed by what statement?

Answer 5

SELECT … FROM FOR SYSTEM_TIME AS ;

Question 6

List the sub-clauses which are used in the FOR SYSTEM_TIME clause

Answer 6

FOR SYSTEM_TIME:
   AS OF  AS 
   FROM  TO  AS 
   BETWEEN  AND 
   CONTAINED IN ( , )
   ALL