"GENERATED ALWAYS AS ROW START/END" and "PERIOD FOR SYSTEM_TIME"

Question

I'm currently learning the new "System versioned temporal table" introduced in sql server 2016. However, I'm having a bit of a hard time getting my head around the exact meaning and use of "GENERATED ALWAYS AS ROW START/END" and "PERIOD FOR SYSTEM_TIME", both of which are required by system versioned temporal table.

Could someone please explain it to me? Thanks in advance for any help!

CREATE TABLE dbo.Employees
(
    empid INT NOT NULL CONSTRAINT PK_Employees PRIMARY KEY NONCLUSTERED,
    empname VARCHAR(25) NOT NULL,
    department VARCHAR(50) NOT NULL,
    salary NUMERIC(10, 2) NOT NULL,

    sysstart DATETIME2(0) GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,

    sysend DATETIME2(0) GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,

    PERIOD FOR SYSTEM_TIME(sysstart, sysend),
    INDEX ix_Employees CLUSTERED(empid, sysstart, sysend)
)

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

Answer 1

Temporal Tables in SQL server 2016

This is an amazing new feature in SQL Server 2016 and later.

In Quick points, What is temporal tables ?

• Introduced in 2011by ANSI/ISO for SQL
• IBM DB2 & Oracle 10g,11g and 12c were early adopters.
• Designed to keep a full history of data changes and allow easy point in time analysis.
• Every temporal table has two explicitly defined columns, each with a datetime2data type.

From MSDN

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

So The primary key for A system-versioned table (consider this as a nickname of temporal table) defined and have exactly one PERIOD FOR SYSTEM_TIME defined with two datetime2 columns for allowing easy point in time analysis or Time Travel (As Mr.Borko Novakovic said at Channel 9), declared as GENERATED ALWAYS AS ROW START / END

References:

• Temporal Tables
• Creating a System-Versioned Temporal Table
• Temporal in SQL Server 2016 - Video.