建立Temporal Tables自動保留資料變更的完整歷程，
並允許簡單的時間點分析，Temporal Tables上需要有兩個額外的datetime2時間資料行(UTC)定義有效時間，
其有效時間皆由SQL Server資料庫引擎所管理，SQL Server會使用此資料表，
在每次時態表中的資料列進行更新或刪除時，自動儲存資料列的先前版本，
讓我們可以查詢先前版本資料內容，但千萬不要以為Temporal Tables使用來取代CDC，
CDC內部運作主要是在日志檔案中擷取所有異動操作歷史記錄，
並將這些訊息寫入對應的跟蹤表，而Temporal Tables是儲存資料表實際資料變更的完整歷程(且保留時間也較長)，
這篇我們來看看如何建立Temporal Tables和一些注意事項及應用。
主要應用(更多資訊請參考Temporal Table Usage Scenarios)
1修復row-level資料錯誤:假如某一筆資料內容不小心修改錯誤、刪除甚至毀損，就可透過時態表輕易並快速恢復。
2稽核: Temporal Tables可以讓你快速又簡單找出某時段特定資料內容。
限制和考量(更多資訊請參考Temporal Table Considerations and Limitations)
不支援FILETABLE。
SYSTEM_TIME資料行必須使用datetime2並not null。
時態表支援 blob 資料類型，但會產生龐大的儲存成本(大小會影響效能)。
不支援TRUNCATE TABLE、不允許直接修改歷程記錄資料表中的資料。
時態表或歷程記錄資料表不允許使用INSTEAD OF 觸發程序，AFTER 觸發程序僅允許針對目前的資料表使用。
歷程記錄資料表必須建立於與目前資料表相同的資料庫中。 不支援針對 Linked Server 的時態查詢。
歷程記錄資料表，建議建立叢集資料列存放區索引或非叢集資料列存放區索引，藉以取得最佳儲存大小和效能。
不行針對SYSTEM_TIME資料行進行UPDATE。
無法對歷史資料表進行資料修改。
建立時態表
CREATE TABLE dbo.MyTemporal
(ID int primary key --必須定義PK
,c1 int
,c2 AS c1 * .5 --可支援計算資料行
,c3 varchar(20)
,StartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL --定義開始時間
,EndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL--定義結束時間
,PERIOD FOR SYSTEM_TIME (StartTime,EndTime))
WITH(SYSTEM_VERSIONING = ON( HISTORY_TABLE = [dbo].[MyTemporal_Archive] , DATA_CONSISTENCY_CHECK = ON )
--啟用Temporal並命名歷史紀錄表為MyTemporal_Archive(必須指定結構描述)，根據預設，歷程記錄資料表會採 PAGE 壓縮處理。
);
CREATE TABLE dbo.MyTemporalB
(ID int primary key --必須定義PK
,c1 int
,c2 AS c1 * .5 --可支援計算資料行
,c3 varchar(20)
,StartTime datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL --定義開始時間並隱藏
,EndTime datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL--定義結束時間並隱藏
,PERIOD FOR SYSTEM_TIME (StartTime,EndTime))
WITH(SYSTEM_VERSIONING = ON --啟用Temporal
);
歷程記錄資料表預設名稱很不友善，建議自行命名。
INSERT操作
insert into MyTemporal(ID,c1,c3)
select 1,10,'rico'
select * from MyTemporal
insert into MyTemporalB(ID,c1,c3)
select 1,10,'rico'
select * from MyTemporalB
Hidden預設不顯示時間欄位，如需要必須明確寫出資料欄位。
注意:Insert操作不會將資料新增至歷史資料表
select * from MyTemporal_Archive
select * from MSSQL_TemporalHistoryFor_1047674780
UPDATE操作
update MyTemporal set c3='sherry'
where id=1
select * from MyTemporal
select * from MyTemporal_Archive
可以看到前一版本rico資料被儲存在歷史資料表。
嘗試更新SYSTEM_TIME資料行會發生錯誤
UPDATE MyTemporal
set c3='sherry',
StartTime = GETUTCDATE(),
EndTime = GETUTCDATE()
WHERE Id = 1
Msg 13537, Level 16, State 1, Line 22
Cannot update GENERATED ALWAYS columns in table
'WideWorldImporters.dbo.MyTemporal'.
DELETE操作
DELETE MyTemporal WHERE Id = 1
select * from MyTemporal
select * from MyTemporal_Archive
TRUNCATE時態表
TRUNCATE TABLE MyTemporal --truncate 會失敗
Msg 13545, Level 16, State 1, Line 19
Truncate failed on table 'WideWorldImporters.dbo.MyTemporal'
because it is not supported operation on system-versioned tables.
DROP時態表
--Disabling System Versioning
alter TABLE dbo.MyTemporalB SET (SYSTEM_VERSIONING = OFF)
drop table MyTemporalB --歷史資料表也會一併刪除
Enjoy SQL Server 2016
參考
SQL 2016 – Temporal Tables – What are they and how do you set one up?
[SQL SERVER][CDC]SQL2008新特性-Change Data Capture#1簡介
