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