SQL Server 2016 新功能搶先看 - Temporal Tables

發行項
07/14/2015

SQL Server 2016 搶先看，這次要帶大家認識 Temporal Tables 新功能！

回顧系列專文：　

本文將帶大家了解：

說明

以往在記錄資料異動前後的內容，可以自行撰寫 DML 觸發程序（Triggers）以便在資料發生新增、修改或刪除時，能夠將資料異動的歷程記錄下來，方便後續追蹤或是進行稽核。

除此之外，您也可以利用SQL Server 2008開始支援的異動資料擷取（Change Data Capture）功能，來自動化的記錄資料表的插入、更新和刪除活動。

在最新版的 SQL Server 2016 ，資料庫引擎中內建一項符合 ASNI SQL 2011 標準的新功能—Temporal Tables，能夠有效協助洞悉資料庫中資料趨勢、資料變化以及綜觀資料演進，真正達到任何時間點（any point in time）的資料異動，都自動且忠實地留下完整軌跡。

Temporal 資料表適用於下列情境：

洞察隨著時間演變的商業趨勢。
追蹤隨著時間演變的資料變化。
稽核所有資料的異動。
決策支援系統的 Slowly Changing Dimension。
提供 DBA 在資料意外被異動時，能夠快速加以更正。

運作原理

在開始之前必須先了解什麼是 Temporal 資料表。Temporal 資料表功能要能夠運作，倚靠的是兩種特殊資料表，一個是 Current 資料表（或稱為 System-Versioned 資料表），專門用來存放資料表在執行各項 DML（Data Manipulation Languag，資料操作語言）之後資料列目前的內容，包含執行（BULK）INSERT/UPDATE/DELETE 或 MERGE 等敘述；另外一個是 History 資料表，用來存放 Current 資料表中資料列異動歷程的所有歷史紀錄。

您可以在新增資料表時啟用 Temporal 功能，或將現有的資料表修改為 Temporal 資料表，甚至規劃將現行利用 DML 觸發程序來保存資料異動的解決方案轉為 Temporal 資料表。

所有在 Temporal 資料表執行 DML 操作所造成的資料異動，每一個資料列版本都會存放在 History 資料表，接著就可以使用 SQL Server 2016 新增加的 FOR SYSTEM_TIME 子句來查詢 Temporal 資料。

建立 Temporal 資料表

建立 Temporal 資料表依然是使用過去所熟悉的 CREATE TABLE 敘述，並搭配下列的參數設定：

使用 PERIOD FOR SYSTEM_TIME 參數來定義驗證資料列的起迄時間。
宣告兩個不允許 NULL 的系統資料行（資料型態為 datetime2 ），並以 GENERATED ALWAYS AS ROW 參數，來指定記錄資料列的系統開始時間（System Start Time）與系統結束時間（System End Time）。
設定 SYSTEM_VERSIONING 等於 ON，並視需要可自行定義 History 資料表或由系統自己產生。

Temporal 資料表會根據 DML 的操作而有不同的行為，說明如下：

INSERT：新增資料時會寫入 Current 資料表，且系統開始時間設定為目前交易的 UTC 時間，系統結束時間則設定為 datetime2 的最大值（9999-12-31 23:59:59.9999999）。
UPDATE：更新資料時會將 Current 資料表更新前的資料複製到 History 資料表，並將系統結束時間設定為目前的 UTC 時間，接著更新 Current 資料表然後把系統開始時間設定為目前交易的 UTC 時間，系統結束時間則設定為 datetime2 的最大值（9999-12-31 23:59:59.9999999）。
DELETE：刪除資料時會將被刪除的資料寫入 History 資料表，並將系統結束時間設定為目前的 UTC 時間，然後將 Current 資料表中的資料刪除。
MERGE：行為與 INSERT、UPDATE 和 DELETE 相同。

有關 CREATE TABLE 與 Temporal Tables 功能相關的重點參數宣告如下：

CREATE TABLE

[ database_name . [ schema_name ] . | schema_name . ] table_name

[ AS FileTable ]

( { <column_definition>

[ PERIOD FOR SYSTEM_TIME ( system_start_time_column_name

, system_end_time_column_name ) ]

)

<column_definition> ::=

column_name <data_type>

[ GENERATED ALWAYS AS ROW { START | END } [ HIDDEN ] ]

<table_option> ::=

{

[ SYSTEM_VERSIONING = ON [ ( HISTORY_TABLE = schema_name . history_table_name

[, DATA_CONSISTENCY_CHECK = { ON | OFF } ] ) ]

下列 T-SQL 指令碼示範建立 Department 資料表，並宣告使用 StartTime 和 DueTime 資料行做為驗證資料列時的系統開始時間及系統結束時間，最後設定 SYSTEM_VERSIONING 等於 ON，即可啟用 Temporal 功能。

CREATE TABLE Department

(

DeptID int IDENTITY NOT NULL PRIMARY KEY CLUSTERED,

DeptName nvarchar(10) NOT NULL,

ManagerID int NULL,

ParentDeptID char(10) NULL,

StartTime datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,

DueTime datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,

PERIOD FOR SYSTEM_TIME (StartTime,DueTime)

)

WITH (SYSTEM_VERSIONING = ON)

執行完上述指令碼後，在物件總管可以看到如下圖的結果，其中 Department 資料表為 Current 資料表，在資料表名稱後方被加註【由系統控制版本】的字樣，展開後可以看到 History 資料表，由於上述範例未指定 History 資料表，因此會自動產生名稱為 MSSQL_TemporalHistoryFor_{Current資料表的物件編號} 的 History 資料表。

當然您也可以自行宣告 History 資料表的資料表結構，接著在建立 Current 資料表時，使用 HISTORY_TABLE 參數來指定現有的 History 資料表名稱，使用這種方式建立 Temporal 資料表，必須注意 Current 和 History 資料表的資料行名稱及資料型別必須完全相同。

下列 T-SQL 指令碼示範先建立名稱為 Department2_History 的 History 資料表，並建立 Current 資料表（Department2）時指定其 History 資料表名稱為 Department2_History。

--History資料表

CREATE TABLE Department2_History

(

DeptID int NOT NULL ,

DeptName nvarchar(10) NOT NULL,

ManagerID int NULL,

ParentDeptID int NULL,

StartTime datetime2 NOT NULL,

DueTime datetime2 NOT NULL,

)

--Current資料表

CREATE TABLE Department2

(

DeptID int IDENTITY NOT NULL PRIMARY KEY CLUSTERED,

DeptName nvarchar(10) NOT NULL,

ManagerID int NULL,

ParentDeptID int NULL,

StartTime datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,

DueTime datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,

PERIOD FOR SYSTEM_TIME (StartTime,DueTime)

)

WITH

(

SYSTEM_VERSIONING = ON

(HISTORY_TABLE = dbo.Department2_History, DATA_CONSISTENCY_CHECK = ON )

)

執行結果如下圖所示：

還有另外一種方式可以指定 History 資料表的名稱，又不需在建立 Current 資料表前先行建立 History 資料表，例如下列的 T-SQL 指令碼：

CREATE TABLE Department3

(

DeptID int IDENTITY NOT NULL PRIMARY KEY CLUSTERED,

DeptName nvarchar(10) NOT NULL,

ManagerID int NULL,

ParentDeptID int NULL,

StartTime datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,

DueTime datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,

PERIOD FOR SYSTEM_TIME (StartTime,DueTime)

)

WITH

(

SYSTEM_VERSIONING = ON

(HISTORY_TABLE = dbo.Department3_History)

)

執行結果如下圖所示：

將資料表轉換成 Temporal 資料表

若您想要將現有的資料表轉換成 Temporal 資料表（例如 Department4 ），可以使用 ALTER TABLE 敘述來加入 PERIOD 的定義（相關必要條件與建立新的 Temporal 資料表相同），接著設定 SYSTEM_VERSIONING 等於 ON 即可，例如下列的指令碼。

CREATE TABLE Department4

(

DeptID int IDENTITY NOT NULL PRIMARY KEY CLUSTERED,

DeptName nvarchar(10) NOT NULL,

ManagerID int NULL,

ParentDeptID int NULL,

)

--加入PERIOD宣告

ALTER TABLE Department4

ADD PERIOD FOR SYSTEM_TIME (StartTime, DueTime),

StartTime datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL DEFAULT GETUTCDATE(),

DueTime datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL DEFAULT CONVERT(DATETIME2, '9999.12.31')

--啟用SYSTEM_VERSIONING

ALTER TABLE Department4

SET (

SYSTEM_VERSIONING = ON

(HISTORY_TABLE = dbo.Department4_History)

)

修改或刪除 Temporal 資料表

一旦資料表啟用 Temporal 功能，就沒辦法透過圖形化介面或 T-SQL 修改結構描述或刪除資料表。以下圖為例，當您在 Temporal 資料表上按滑鼠右鍵時，看不到【設計】與【刪除】的選項，亦即您無法在設定 SYSTEM_VERSIONING 等於 ON 時，對 Temporal 資料表做異動。

圖形化介面不能異動 Temporal 資料表，嘗試以 T-SQL 直接刪除 Temporal 資料表呢？一樣會發生作業失敗的錯誤。

正確修改Temporal資料表結構描述，應依照下列步驟進行：

停用 SYSTEM_VERSIONING。
修改 Current 資料表的結構描述。
修改 History 資料表的結構描述。
啟用 SYSTEM_VERSIONING 並將 HISTORY_TABLE 資料表名稱設定為修改後的 History 資料表。

下列 T-SQL 指令碼示範於資料表名稱為 Department4 的 Temporal 資料表，加入 Descriptions 資料行。

--停用SYSTEM_VERSIONING

ALTER TABLE [Department4]

SET (SYSTEM_VERSIONING = OFF)

--修改Current資料表的結構描述

ALTER TABLE [Department4]

ADD Descriptions nvarchar(50) NOT NULL DEFAULT (N'未輸入')

--修改History資料表的結構描述

ALTER TABLE [Department4_History]

ADD Descriptions nvarchar(50) NOT NULL DEFAULT (N'未輸入')

--啟用SYSTEM_VERSIONING並將HISTORY_TABLE資料表名稱設定為修改後的History資料表

ALTER TABLE [Department4]

SET

(

SYSTEM_VERSIONING = ON

(HISTORY_TABLE = dbo.Department4_History)

)

刪除 Temporal 資料表，應依照下列步驟進行：

若您不知道要刪除的 Temporal 資料表所使用的 History 資料表名稱為何，可以查詢 sys.tables 系統檢視，經由 history_table_id 可以得知 History 資料表的物件編號。

停用 SYSTEM_VERSIONING。
移除 PERIOD 定義。
移除 Current 資料表。
移除 History 資料表。

下列 T-SQL 示範刪除資料表名稱為 Department3 的 Temporal 資料表的完整步驟。

SELECT name,object_id,temporal_type,temporal_type_desc,history_table_id

FROM sys.tables

WHERE name like 'Department3%'

--必須先停用SYSTEM_VERSIONING才能移除資料表

ALTER TABLE Department3

SET (SYSTEM_VERSIONING = OFF)

--移除PEROID

ALTER TABLE Department3

DROP PERIOD FOR SYSTEM_TIME

--移除Current資料表及History資料表

DROP TABLE Department3

DROP TABLE Department3_History

測試 Temporal 資料表

當您開始對 Temporal 資料表進行各項 DML 的操作時，Current 和 History 資料表會有什麼樣的變化，在這一節我們用幾個簡單的範例來帶您了解 Temporal 資料表如何達到記錄任何時間點的資料變化。

假設您已經依照前面所述建立 Temporal 資料表，並執行下列 T-SQL 來新增一筆資料。

--新增資料

INSERT INTO Department2(DeptName,ManagerID,ParentDeptID)

VALUES (N'資訊部',1,1)

--查看結果

SELECT [DeptID],[DeptName],[ManagerID],[ParentDeptID],[StartTime],[DueTime]

FROM [dbo].[Department2]

SELECT [DeptID],[DeptName],[ManagerID],[ParentDeptID],[StartTime],[DueTime]

FROM [dbo].[Department2_History]

由於前面的步驟，我們將 StartTime 和 DueTime 資料行設定為 HIDDEN，因此直接 SELECT * FROM [Current資料表 | History 資料表] 會自動隱藏被設定為HIDDEN的資料行，若要查看 Temporal 資料表如何保存每一筆資料列的歷程，則必須明確指定 PERIOD 所宣告的 SYSTEM_TIME 資料行。

由下圖可見，Current 資料表（Department2）目前只有一筆資料，其中 StartTime 資料行紀錄的正是新增資料時的 UTC 時間，而 DueTime 為 9999-12-31 23:59:59.9999999，則是代表該筆資料尚未被刪除所以仍為有效的狀態。

而一開始尚未對 Current 資料表進行任何更新或刪除，所以 History 資料表不存在任何資料。

接著以下列 T-SQL 指令碼更新 DeptID 等於 1 的資料列，將 DeptName 資料行由資訊部改為電腦資訊部。

--更新資料

UPDATE [dbo].[Department2]

SET DeptName = N'電腦資訊部'

WHERE DeptID = 1

--查看結果

SELECT [DeptID],[DeptName],[ManagerID],[ParentDeptID],[StartTime],[DueTime]

FROM [dbo].[Department2]

SELECT [DeptID],[DeptName],[ManagerID],[ParentDeptID],[StartTime],[DueTime]

FROM [dbo].[Department2_History]

由下圖可見，Current 資料表已經確實依照 UPDATE 敘述將 DeptName 資料行改為電腦資訊部，其中 StartTime 資料行依然記錄著更新資料當時的 UTC 時間，而由於該筆資料仍有效並未被刪除，因此 DueTime 資料行保持 dateime2 的最大值。

而現在，原本空白的 History 資料表多了一筆資料列（ DeptID 等於 1 ），其中 DeptName 資料行為 UPDATE 前的值（也就是資訊部），並且 StartTime 資料行存放的時間是該筆資料列最初被新增到 Current 資料表的時間，而 DueTime 資料行則是存放 DeptID 資料列等於 1 資料列被更新的時間。

當您以下列 T-SQL 指令碼刪除 DeptID 等於 1 的資料列，Temporal 資料表會有什麼樣的變化呢？

--刪除資料

DELETE FROM [dbo].[Department2]

WHERE DeptID = 1

--查看結果

SELECT [DeptID],[DeptName],[ManagerID],[ParentDeptID],[StartTime],[DueTime]

FROM [dbo].[Department2]

SELECT [DeptID],[DeptName],[ManagerID],[ParentDeptID],[StartTime],[DueTime]

FROM [dbo].[Department2_History]

由下圖可見，由於資料列被刪除，原本存在於 Current 資料表的資料列被搬移到History資料表，其中 StartTime 資料行的值保持不變， DueTime 資料行的值會因為資料被刪除而註記為刪除當時的 UTC 時間。

查詢 Temporal 資料

SQL Server 2016 在 FROM 子句新增多項可以用來查詢 Temporal 資料的 FOR SYSTEM_TIME 子句，針對不同情境可以選擇 AS OF <date_time>、FROM <start_date_time> TO <end_date_time>、BETWEEN <start_date_time> AND <end_date_time> 與 CONTAINED IN(<start_date_time>,<end_date_time> 等運算式來同時查詢 Current 和 History 資料表。

假設目前 Current 資料表內有有 1 筆資料，History 資料表有 2 筆資料（如下圖），接下來就用幾個實際的範例來示範該如何 FOR SYSTEM_TIME 子句來查詢 Temporal 資料。

AS OF <date_time>運算式

用來查詢系統起始時間小於等於 date_time 參數且系統結束時間大於 date_time 參數之特定時間的 Temporal 資料。

下列 T-SQL 指令碼示範由 Temporal 資料表查詢時間點 2015-07-08 14:48:10 的 Temporal 資料。

SELECT [DeptID] ,[DeptName] ,[ManagerID] ,[ParentDeptID] ,[StartTime] ,[DueTime]

FROM [TemporalDB].[dbo].[Department2]

FOR SYSTEM_TIME AS OF '2015-07-08 14:48:10'

由於只有 History 資料表符合 AS OF 運算式的篩選條件，因此回傳下圖藍色方框所標示的 Temporal 資料（如下圖）。

FROM <start_date_time> TO <end_date_time> 運算式

用來查詢系統起始時間小於 end_date_time 參數且系統結束時間大於 start_date_time 參數的 Temporal 資料。

下列 T-SQL 指令碼示範由 Temporal 資料表查詢介於 2015-07-08 至 2015-07-09 之間的 Temporal 資料。

SELECT [DeptID] ,[DeptName] ,[ManagerID] ,[ParentDeptID] ,[StartTime] ,[DueTime]

FROM [TemporalDB].[dbo].[Department2]

FOR SYSTEM_TIME FROM '2015-07-08' TO '2015-07-09'

由於 History 資料表的兩筆資料的系統起始時間都小於 2015-07-09 且系統結束時間都大於 2015-07-08 ，因此透過 FROM…TO 運算式可以得到如下圖藍色方框處的結果集。

BETWEEN <start_date_time> AND <end_date_time> 運算式

用來查詢所有系統起始時間小於等於 end_date_time 且系統結束時間大於 start_date_time 的 Temporal 資料。

下列 T-SQL 指令碼示範由 Temporal 資料表查詢介於 2015-07-08 與 2015-07-10 之間的資料。

SELECT [DeptID] ,[DeptName] ,[ManagerID] ,[ParentDeptID] ,[StartTime] ,[DueTime]

FROM [TemporalDB].[dbo].[Department2]

FOR SYSTEM_TIME BETWEEN '2015-07-08' AND '2015-07-10'

由於 Current 與 History 資料表的所有資料的系統起始時間都是小於等於 2015-07-10 ，且系統結束時間都是大於 2015-07-08 ，因此透過 BETWEEN…AND 運算式可以得到如下圖藍色方框處的結果集。

CONTAINED IN (<start_date_time> , <end_date_time>) 運算式

用來查詢所有系統起始時間大於等於 start_date_time 參數且系統結束時間小於等於 end_date_time 參數的 Temporal 資料。

下列 T-SQL 指令碼示範由 Temporal 資料表查詢介於 2015-07-09 00:00:00 與 2015-07-09 23:59:59.9999999 之間的資料。

SELECT [DeptID] ,[DeptName] ,[ManagerID] ,[ParentDeptID] ,[StartTime] ,[DueTime]

FROM [TemporalDB].[dbo].[Department2]

FOR SYSTEM_TIME CONTAINED IN ('2015-07-09 00:00:00', '2015-07-09 23:59:59.9999999')

由於 Temporal 資料表未有資料符合查詢的日期區間，因此查不到任何資料（如下圖所示）。

Comments

Anonymous
July 27, 2015
The comment has been removed
Anonymous
August 07, 2015
The comment has been removed
Anonymous
September 24, 2015
The comment has been removed
Anonymous
September 29, 2015
The comment has been removed
Anonymous
October 04, 2015
SQL Server 2016 搶先看系列第十彈！

本系列文已經介紹 SQL Server 2016 在安全性、混合雲應用、效能調校及開發等方面所推出的嶄新功能或改良， SQL Server

共用方式為

SQL Server 2016 新功能搶先看 - Temporal Tables

Comments

其他資源