CREATE TABLE Employees
(
EmpID INT PRIMARY KEY,
Name NVARCHAR(100),
Salary DECIMAL(10,2),
SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
SysEndTime   DATETIME2 GENERATED ALWAYS AS ROW END   NOT NULL,
PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON);

INSERT INTO Employees (EmpID, Name, Salary)
VALUES (1, 'John', 1000);

UPDATE Employees
SET Salary = 1200
WHERE EmpID = 1;

UPDATE Employees
SET Salary = 1300
WHERE EmpID = 1;


SELECT *
FROM Employees
FOR SYSTEM_TIME AS OF '2026-02-08 18:49';

SELECT *
FROM Employees
FOR SYSTEM_TIME BETWEEN
'2026-02-07' AND '2026-02-08 17:13:54';

SELECT *
FROM Employees
FOR SYSTEM_TIME ALL
WHERE EmpID = 1;

ALTER TABLE Employees ADD Department NVARCHAR(50);

--<<
DECLARE @t datetime2(7) = '2026-02-08 18:07:03.0000000';
DECLARE @t2 datetime2(7) = DATEADD(NANOSECOND, -100, @t);  -- small step back

SELECT *
FROM Employees
FOR SYSTEM_TIME AS OF @t2
WHERE EmpID = 1;
-->>

SELECT * 
FROM Employees
WHERE EmpID = 1;
--<<
--restore data from history
DECLARE @asof datetime2(7) = '2026-02-08 18:07:03.0000000';

UPDATE e
SET e.Salary = h.Salary
FROM Employees e
JOIN Employees FOR SYSTEM_TIME AS OF @asof h
  ON e.EmpID = h.EmpID
WHERE e.EmpID = 1;
-->>

DELETE FROM Employees
WHERE EmpID = 1;

 --restore after delete
INSERT INTO Employees (EmpID, Name, Salary)
SELECT TOP (1) EmpID, Name, Salary
FROM Employees
FOR SYSTEM_TIME ALL
WHERE EmpID = 1
ORDER BY SysEndTime DESC;

--drop table
ALTER TABLE Employees
SET (SYSTEM_VERSIONING = OFF);

--Drop the main table
DROP TABLE EmployeesHistory;