SQL Server 2016新特性：Temporal Table

標籤：cluster   有效期間   ons   color   server   過濾   prim   key   create   

什麼是系統版本的Temporal Table系統版本的Temporal Table是可以儲存曆史修改資料並且可以簡單的指定時間分析的使用者表。 這個Temporal Table就是系統版本的Temporal Table因為每行的有效期間由系統託管的。每個Temporal Table有2個顯示定義的列，類型是datetime2。這些用來表示有效期間。這個列用來標記這個行是不是在期間內可用。除了上面的period列，l暫存資料表也包含了引用到其他表，系統使用這個表來儲存行修改刪除前的行版本。這個附加表可以認為是history表，主表包含了當前的行版本為當前表。在Temporal Table建立的時候可以指定一個history表或者讓系統建立一個預設的history表。  暫存資料表的工作原理系統版本的表是有一對錶，當前表和曆史表。這些表都包含2個額外的datetime2欄位用來定義每個行的可用期限：

• 期限開始列：系統把行的開始時間記錄在這個列上，稱為SysStartTime
• 期限結束列：系統把行的結束時間記錄在這個列上，稱為SysEndTime

當前表包含了每個行的當前值。曆史表包含每個行的之前的只，starttime，endtime表示行的可用期限。以下是一個例子：CREATE 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));可以刪除括弧中的HISTORY_TABLE系統會自動建立history表。 INSERT：對於一個insert，系統會設定SysStartTime列為當前事務的開始時間，SysEndTime為最大的值9999-12-31UPDATE：對於update，系統會報之前的行儲存到曆史表並且設定SysEndTime為當前事務的啟動時間。行被關閉，這個期限就是這個行的可用期限。這個行在當前表上的值被修改，那麼SysStartTime被設定為當前事務的開始時間。SysEndTime被設定為最大時間。DELETE：對於刪除，系統把之前的行儲存到history表，並且設定SysEndtime為事務的開始時間。標記行關閉，期限記錄表示行的可用期限。當前表中行被刪除。當前的查詢不會被查到當前行。只有帶時間的查詢，或者直接查詢曆史表才能查到這個行。MERGE：對於MERGE涉及到3個操作INSERT,UPDATE,DELETE，根據操作的不同做不同的記錄。  臨時資料查詢可以使用select from的for system_time子句來查詢當前表和曆史表的資料。 以下是查詢的例子：SELECT * FROM Employee      FOR SYSTEM_TIME           BETWEEN ‘2014-01-01 00:00:00.0000000‘ AND ‘2015-01-01 00:00:00.0000000‘              WHERE EmployeeID = 1000 ORDER BY ValidFrom;注意：FOR SYSTEM_TIME會過濾掉SysStartTime=SysEndTime的資料。這些行在同一個事務裡面操作了同一行兒產生。只能通過查詢曆史表才能返回 關於SYSTEM_TIME過濾

運算式	合格行	Description
AS OF<date_time>	SysStartTime <= date_time AND SysEndTime > date_time	返回一個表，其行中包含過去指定時間點的實際（當前）值。 在內部，暫存資料表及其記錄表之間將進行聯合，然後篩選結果以返回在 <date_time> 參數指定的時間點有效行中的值。 如果 system_start_time_column_name 值小於或等於 <date_time> 參數值，並且 system_end_time_column_name 值大於 <date_time> 參數值，則此行的值視為有效。
FROM<start_date_time>TO<end_date_time>	SysStartTime < end_date_time AND SysEndTime > start_date_time	返回一個表，其中包含在指定的時間範圍內保持活動狀態的所有行版本的值，不管這些版本是在 FROM 自變數的 <start_date_time> 參數之前開始活動，還是在 TO 自變數的 <end_date_time> 參數值之後停止活動。 在內部，將在暫存資料表及其記錄表之間進行聯合，然後篩選結果，以返回在指定時間範圍內任意時間保持活動狀態的所有行版本的值。 正好在 FROM 終結點定義的下限時間停止活動的行將被排除，正好在 TO 終結點定義的上限時間開始活動的記錄也將被排除。
BETWEEN<start_date_time>AND<end_date_time>	SysStartTime <= end_date_time AND SysEndTime > start_date_time	與上面的 FOR SYSTEM_TIME FROM <start_date_time>TO<end_date_time> 描述相同，不過，返回的行表包括在 <end_date_time> 終結點定義的上限時間啟用的行。
CONTAINED IN (<start_date_time> , <end_date_time>)	SysStartTime >= start_date_time AND SysEndTime <= end_date_time	返回一個表，其中包含在 CONTAINED IN 參數的兩個日期時間值定義的時間範圍內開啟和關閉的所有行版本的值。 正好在下限時間啟用的記錄，或者在上限時間停止活動的行將包括在內。
ALL	所有行	返回屬於當前表和記錄表的行的聯合。

注意：可以通過Hidden隱藏期限列，刪除表需要先關閉系統版本 ALTER TABLE Employee SET (SYSTEM_VERSIONING =off )之後才能刪除表

SQL Server 2016新特性：Temporal Table