如何通过保留数据将现有表更改为SQL历史表？

Question

我有很多带数据的表格，我想将它们转换为Microsoft Temporal表格，但当我想要转换成时间表时会导致数据丢失。我的代码如下：

Alter TABLE dbo.Employee   
(    
  [EmployeeID] int NOT NULL PRIMARY KEY CLUSTERED   
  , [Name] nvarchar(100) NOT NULL  
  , [Position] varchar(100) NOT NULL   
  , [Department] varchar(100) NOT NULL  
  , [Address] nvarchar(1024) NOT NULL  
  , [AnnualSalary] decimal (10,2) NOT NULL  
  , [ValidFrom] datetime2 (2) GENERATED ALWAYS AS ROW START  
  , [ValidTo] datetime2 (2) GENERATED ALWAYS AS ROW END  
  , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)  
 )    
 WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory)); 

如何将现有表格改为 Sql 时间表格并保留数据？

- Hamed Roshangar

Answer 1

两步打开Employee表的系统版本控制

1. add new period columns (HIDDEN)

2. create default history table

   ALTER TABLE Employee   
   ADD   
   ValidFrom datetime2 (2) GENERATED ALWAYS AS ROW START HIDDEN    
       constraint DF_ValidFrom DEFAULT DATEADD(second, -1, SYSUTCDATETIME())  
   , ValidTo datetime2 (2)  GENERATED ALWAYS AS ROW END HIDDEN     
       constraint DF_ValidTo DEFAULT '9999.12.31 23:59:59.99'  
   , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);   
   
   ALTER TABLE Employee    
   SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Employee_History));  
   

执行上述脚本后，所有数据更改都将透明地收集到历史表中。在典型的数据审计场景中，您需要查询感兴趣时间段内应用于单个行的所有数据更改。默认的历史表使用聚集行存储B-Tree来有效地处理此类用例。

- mansi

Answer 2

1. 创建你的临时表。

2. 将原始数据插入到临时表中。

3. 删除原始表。

- sagi

---

我有很多表格，可能有100个左右，但我认为每次都使用这种方式不太可行。谢谢您的回答。 - Hamed Roshangar

Answer 3

首先，您应该向任何表添加两个系统时间段列。

就像这样：

CREATE TABLE DepartmentHistory   
(    
     DeptID int NOT NULL  
   , DeptName varchar(50) NOT NULL  
   , SysStartTime datetime2 NOT NULL  
   , SysEndTime datetime2 NOT NULL   
);   
GO   
CREATE CLUSTERED COLUMNSTORE INDEX IX_DepartmentHistory   
   ON DepartmentHistory;   
CREATE NONCLUSTERED INDEX IX_DepartmentHistory_ID_PERIOD_COLUMNS   
   ON DepartmentHistory (SysEndTime, SysStartTime, DeptID);   
GO   
CREATE TABLE Department   
(    
    DeptID int NOT NULL PRIMARY KEY CLUSTERED  
   , DeptName varchar(50) NOT NULL  
   , SysStartTime datetime2  NOT NULL  
   , SysEndTime datetime2 NOT NULL           
) ;

在这些代码中，SysStartTime和SysEndTime是系统时间周期列。

然后你可以轻松地将它们转换为时间表：

ALTER TABLE dbo.Department   
   ADD PERIOD FOR SYSTEM_TIME ([SysStartTime], [SysEndTime])   
ALTER TABLE dbo.Department      
   SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DepartmentHistory, DATA_CONSISTENCY_CHECK = ON));  

如果您有临时表，并且想要编辑表的模式，那么您可以使用以下代码：

ALTER TABLE Test.dbo.Department   
   SET (SYSTEM_VERSIONING = OFF) 

- Behzad