If the PERIOD columns are explicitly defined as nullable, the CREATE TABLE statement will fail. The PERIOD columns are always assumed to be non-nullable, even if nullability isn't specified.A system-versioned temporal table must have a primary key defined and have exactly one PERIOD FOR SYSTEM_TIME defined with two datetime2 columns, declared as GENERATED ALWAYS AS ROW START / END., PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo) , ValidTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL

, ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL CREATE TABLE DepartmentĭeptID INT NOT NULL PRIMARY KEY CLUSTERED In the example below, a new table is created with system-versioning enabled without defining the name of the history table. It's also the simplest way to create a temporal table since it doesn't require any parameter in SYSTEM_VERSIONING clause.

Temporal table with a user-defined history table created beforehand: you create a history table that fits best your needs and then reference that table during temporal table creation.Ĭreate a temporal table with an anonymous history tableĬreating a temporal table with an 'anonymous' history table is a convenient option for quick object creation, especially in prototypes and test environments.

Temporal table with a default history table: you specify the history table schema name and table name and let the system create a history table in that schema.

Temporal table with an anonymous history table: you specify the schema of the current table and let the system create a corresponding history table with auto-generated name.There are three ways to create a system-versioned temporal table when considering how the history table is specified:

See user history in filemaker pro 16