SQL Temporal Tables Include Current State

Question

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));

Answer 1

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.

• Ed Avis: From a quick check, it appears that for system_time generates a similar query plan to doing a manual union, but it's certainly more readable.