SQL Server 2016 – Temporal Tables Include Current State

data-warehousedatabase-designsql serversql-server-2016temporal-tables

We want to implement temporal tables in SQL Server 2016. We are creating a data warehouse and developing type 2 slowly changing dimension tables.

We noticed that history tables only include history. We want to create a table which has history and current state (with EndDate being Null or 12/31/9999).
Is there any way to perform this, without creating a view to UNION current and history tables?

If I have to go with a UNION view, did SQL Server optimize internals, so there would not be any performance issue, for 50 million rows? I have to give the datawarehouse to customers and executive management, there should not be any noticeable difference.

 CREATE TABLE dbo.Department
    (
        DeptID INT NOT NULL PRIMARY KEY CLUSTERED,
        DeptName VARCHAR(50) NOT NULL,
        ManagerID INT NULL,
        ParentDeptID INT NULL,
        SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START
          CONSTRAINT DF_Department_SysStartTime DEFAULT SYSUTCDATETIME() NOT NULL,
        SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END
          CONSTRAINT DF_Department_SysEndTime 
        DEFAULT CONVERT( DATETIME2, '9999-12-31 23:59:59' ) NOT NULL,
        PERIOD FOR SYSTEM_TIME(SysStartTime, SysEndTime)
    )
    WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DepartmentHistory));

Best Answer

Temporal tables support the FOR SYSTEM_TIME syntax. One of its options effectively does the UNION for you. From the documentation:

SELECT * FROM my_table
FOR SYSTEM_TIME ALL;

ALL Returns the union of rows that belong to the current and the history table.

Related Solutions

Sql-server – Using SQL Server’s 2016 Temporal Tables to Record Prior History

Currently temporal supports system time, which means the date/time values placed in those start/end time columns are populated by the system.

In a future version, temporal will also support application time, which will provide ways to have the application dictate the time and, I expect, to update existing data with real start/end times.

The only way to override this really would be to archive data from the history table into your own table (which you may end up doing over time to keep the history table from growing forever anyway), change the times there, and use that table as part of your queries.

Sql-server – SQL Server 2016 Temporal Tables

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;

Best Answer

Related Solutions

Sql-server – Using SQL Server’s 2016 Temporal Tables to Record Prior History

Sql-server – SQL Server 2016 Temporal Tables

Related Question