1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy