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.

| improve this answer | |

Your Answer

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