Bad ideas for cheating with temporal tables

BadIdeaJeans_TemporalTableEdition.sql

USE tempdb

IF EXISTS (SELECT * FROM sys.schemas AS S INNER JOIN sys.tables AS T ON T.schema_id = S.schema_id WHERE S.name = 'dbo' AND T.name = 'Employee')
BEGIN
	BEGIN TRY
		ALTER TABLE dbo.Employee
		SET (SYSTEM_VERSIONING = OFF);
	END TRY
	BEGIN CATCH
		-- I don't care if system versioning is on
	END CATCH
END


DROP TABLE IF EXISTS dbo.Employee;
DROP TABLE IF EXISTS dbo.EmployeeHistory;

GO

/*
-- Can we make populate the main table
-- No, No we cannot
CREATE TABLE dbo.EmployeeHistory
(
	[EmployeeID] int NOT NULL PRIMARY KEY CLUSTERED
  , [Name] nvarchar(100) NOT NULL
  , [ValidFrom] datetime2 NOT NULL
  , [ValidTo] datetime2 NOT NULL
 )


CREATE TABLE dbo.Employee
(
	[EmployeeID] int NOT NULL PRIMARY KEY CLUSTERED
  , [Name] nvarchar(100) NOT NULL
  --, [ValidFrom] datetime2 GENERATED ALWAYS AS ROW START
  --, [ValidTo] datetime2 GENERATED ALWAYS AS ROW END
  , [ValidFrom] datetime2 NOT NULL
  , [ValidTo] datetime2 NOT NULL
  --, PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
 )
--WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));
*/


CREATE TABLE dbo.Employee
(
	[EmployeeID] int NOT NULL PRIMARY KEY CLUSTERED
  , [Name] nvarchar(100) NOT NULL
  , [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.EmployeeHistory));

ALTER TABLE dbo.Employee
SET (SYSTEM_VERSIONING = OFF);

/*
-- Load live data
-- Except, 
-- Cannot insert an explicit value into a GENERATED ALWAYS column in table 'tempdb.dbo.Employee'. Use INSERT with a column list to exclude the GENERATED ALWAYS column, or insert a DEFAULT into GENERATED ALWAYS column.

INSERT INTO
	dbo.Employee
	(
	    EmployeeID
	,   Name
	,	ValidFrom
	,	ValidTo
	)
	VALUES
	(
	    0 -- EmployeeID - int
	,   N'Beep' -- Name - nvarchar(100)
	,	'2021-08-18 22:25:29.2777412'
	,	'9999-12-31 23:59:59.9999999'
	)
*/


INSERT INTO
	dbo.Employee
	(
	    EmployeeID
	,   Name
	)
	VALUES
	(
	    0 -- EmployeeID - int
	,   N'Current' -- Name - nvarchar(100)
	)



-- Good luck on getting the dates here to align with primary's dates

-- load historical
INSERT INTO
	dbo.EmployeeHistory
	(
	    EmployeeID
	,   Name
	,   ValidFrom
	,   ValidTo
	)
	VALUES
	(
	    0 -- EmployeeID - int
	,   N'Oldest History' -- Name - nvarchar(100)
	,   '2021-07-01'
	,   '2021-08-17 22:25:29.2777412'
	)
,
	(
	    0 -- EmployeeID - int
	,   N'Current History' -- Name - nvarchar(100)
	,   '2021-08-17 22:25:29.2777412'
	,   '2021-08-18 22:25:29.2777412'
	)

/*
-- No dice here either
-- Column 'ValidFrom' in table 'tempdb.dbo.Employee' cannot be specified as 'GENERATED ALWAYS' in ALTER COLUMN statement.
ALTER TABLE
	dbo.Employee
ALTER COLUMN
  [ValidFrom] datetime2 GENERATED ALWAYS AS ROW START

ALTER TABLE
	dbo.Employee
ALTER COLUMN
	[ValidTo] datetime2 GENERATED ALWAYS AS ROW START

ALTER TABLE 
	dbo.Employee
ADD
  PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
*/

-- Fix the gap between loaded and the system value that was auto populated
UPDATE EH
SET
	EH.ValidTo = E.ValidFrom
FROM dbo.EmployeeHistory AS EH	
INNER JOIN 
	dbo.Employee AS E
	ON E.EmployeeID = EH.EmployeeID
WHERE
	EH.ValidTo = (
	-- Segmentation
	SELECT MAX(EHI.ValidTo) FROM dbo.EmployeeHistory AS EHI WHERE EHI.EmployeeID = EH.EmployeeId
	)


ALTER TABLE dbo.Employee
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));


SELECT * FROM dbo.Employee FOR SYSTEM_TIME ALL AS E ORDER BY E.EmployeeID, E.ValidFrom DESC;
SELECT * FROM dbo.EmployeeHistory AS EH ORDER BY EH.EmployeeID, EH.ValidFrom DESC;


UPDATE dbo.Employee
SET Name = 'New Current'
WHERE EmployeeID = 0;


SELECT * FROM dbo.Employee FOR SYSTEM_TIME ALL AS E ORDER BY E.EmployeeID, E.ValidFrom DESC;