0

Temporal Tables

Eden Ravenwood 2 weeks ago 0

Is it possible to flag a table as a Temporal Table as defined in MS SQL Server 2016 and later?

Temporal tables are declared with the following syntax:

CREATE TABLE Department
(
DeptID INT NOT NULL PRIMARY KEY CLUSTERED,
DeptName VARCHAR(50) NOT NULL,
ManagerID INT NULL,
ParentDeptID INT 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)
;

The bold elements define the table as a Temporal table (Temporal tables - SQL Server | Microsoft Learn)

I can get the WITH clause using the 'WITH options' under additional properties for the table, and I can create 'ValidFrom' and 'ValidTo' columns of type datetime2, but I can't see a way to set the properties of those columns to correctly apply GENERATED ALWAYS AS ROW START and GENERATED ALWAYS AS ROW END, or to apply the PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo) property.  I can probably use a text macro in notepad++ on the generated SQL to add this but it would be nice if it was supported in Vertabelo?