Sql-server – SQL Server 2016: Temporal table performance vs. Triggers and CDC

_{Community Wiki answer generated from question comments. Feel free to edit it to improve it.}

Anthony Faull: The Temporal Tables feature is available in all editions of SQL Server 2016 including Standard Edition, Web Edition and Express Edition. It is not limited to Enterprise Edition.

Yes, you can import old history into history tables. Here is a quick example that shows how to do this - you need to turn off system versioning temporarily.

CREATE TABLE dbo.TemporalExampleHistory
(
  TemporalID int NOT NULL,
  UserName   sysname,
  ValidFrom  datetime2 NOT NULL,
  ValidTo    datetime2 NOT NULL
);
GO

CREATE TABLE dbo.TemporalExample
(
  TemporalID int PRIMARY KEY,
  UserName   sysname,
  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 
  (
    HISTORY_TABLE = dbo.TemporalExampleHistory
  )
);

Now, insert some data:

INSERT dbo.TemporalExample(TemporalID, UserName)
  VALUES(1,N'Bob'),(2,N'Frank');

-- update a row to make some history:

UPDATE dbo.TemporalExample
  SET UserName = N'Frankie'
  WHERE TemporalID = 2;

Validate there is a system-versioned row in history table:

SELECT * FROM dbo.TemporalExampleHistory;

Now, to insert rows into history. Execute these steps one at a time; the parser will prevent the insert if these are all in a single batch.

BEGIN TRANSACTION; -- may want SERIALIZABLE here

ALTER TABLE dbo.TemporalExample SET (SYSTEM_VERSIONING = OFF);

INSERT dbo.TemporalExampleHistory(TemporalID, UserName, ValidFrom, ValidTo)
  SELECT TOP (1) TemporalID, N'Little Frankie', '19000101', ValidFrom
    FROM dbo.TemporalExampleHistory 
    WHERE TemporalID = 2
    ORDER BY ValidFrom;

ALTER TABLE dbo.TemporalExample SET
(
  SYSTEM_VERSIONING = ON 
  (
    HISTORY_TABLE = dbo.TemporalExampleHistory,
    DATA_CONSISTENCY_CHECK = ON
  )
);

COMMIT TRANSACTION;

Now validate there are now two versioned rows of id = 2 in history table:

SELECT * FROM dbo.TemporalExampleHistory;