Temporal Tables

SQL Server 2016 以資料庫功能的形式引入了對版本由系統控制的臨時表的支持，其附帶的內置支持可以提供表中存儲的數據在任意時間點的相關信息，而不僅僅是數據在當前時刻正確的信息。 臨時表是 ANSI SQL 2011 中引入的資料庫功能。

這個臨時表（Temporal Tables）和SQL Server之前版本就有的臨時表(Temporary table)不同。

基本介紹

• 中文名：臨時表
• 外文名：Temporal Tables

什麼是版本由系統控制的臨時表？

版本由系統控制的臨時表是用戶表的一種類型，旨在保留完整的數據更改歷史記錄，並實現輕鬆的時間點分析。這種類型的臨時表之所以稱為版本由系統控制的臨時表，是因為每一行的有效期由系統（即資料庫引擎）管理。

每個臨時表有兩個顯式定義的列，其中每個列都有一個datetime2數據類型。這些列稱為期限列。每當修改了某行後，系統將以獨占方式使用這些期限列來記錄每行的有效期。

除了這些期限列以外，臨時表還包含對使用鏡像架構的另一個表的引用。每當更新或刪除了臨時表中的某行後，系統將使用此表來自動存儲該行的先前版本。此附加表稱為歷史記錄表，而存儲當前（實際）行版本的主表稱為當前表，或直接稱為臨時表。在創建臨時表期間，用戶可以指定現有的歷史記錄表（必須與架構相符），或者讓系統創建默認的歷史記錄表。

為何使用臨時表？

實際的數據源是動態的，業務決策多半依賴於分析師從數據演變中獲得的見解。臨時表的用例包括：

• 在必要時審核所有數據變更並執行數據取證
• 重構數據在過去任意時間之前的狀態
• 計算各時間段的趨勢
• 為決策支持應用程式保持一個慢速變化的維度
• 在發生意外的數據更改和應用程式錯誤後進行恢復

臨時表的工作原理是什麼？

表的系統版本控制是以一對表（當前表和歷史記錄表）的形式實現的。在其中每個表中，以下兩個附加datetime2列用於定義每行的有效期：

• 期限開始列：系統在此列（通常表示為SysStartTime列）中記錄行的開始時間。
• 期限結束列：系統在此列（通常表示為SysEndTime列）中記錄行的結束時間。
  當前表包含每個行的當前值。歷史記錄表包含每個行的每個先前值（如果有），以及該行生效的開始時間和結束時間。

以下簡單示例演示了在虛構 HR 資料庫中包含員工信息的方案：

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));

INSERTS：對於INSERT，系統基於系統時鐘將SysStartTime列的值設定為當前事務的開始時間（位於 UTC 時區），並將SysEndTime列的值指定為最大值 9999-12-31。這會將行標記為已打開。

UPDATES：對於UPDATE，系統將行的先前值存儲在歷史記錄表中，並基於系統時鐘將SysEndTime列的值設定為當前事務的開始時間（位於 UTC 時區）。這會將行標記為已關閉，並記錄該行有效的期限。在當前表中，將使用新值更新行，同時，系統會基於系統時鐘將SysStartTime列的值設定為事務的開始時間（位於 UTC 時區）。在當前表中，SysEndTime列的更新行值將保留最大值 9999-12-31。

DELETES：對於DELETE，系統將行的先前值存儲在歷史記錄表中，並基於系統時鐘將SysEndTime列的值設定為當前事務的開始時間（位於 UTC 時區）。這會將行標記為已關閉，並記錄前一行有效的期限。在當前表中，該行將被刪除。對當前表的查詢不會返回此行。處理歷史記錄數據的查詢將返回已關閉的行的數據。

MERGE：對於MERGE，根據MERGE語句中被指定為操作的內容，該操作將執行多達三個語句（INSERT、UPDATE和/或DELETE）。

重要：系統 datetime2 列中記錄的時間基於事務本身的開始時間。例如，在單個事務中插入的所有行具有對應於SYSTEM_TIME的開始時間段列中記錄的相同 UTC 時間。

如何查詢臨時數據？

SELECT語句FROM<table>子句提供新的FOR SYSTEM_TIME子句和五個特定於時態表的從屬子句，用於跨當前表和歷史記錄表查詢數據。支持對通過多個聯接傳播的，以及通過多個臨時表頂層的視圖傳播的單個表直接使用這種新的SELECT語句語法。

以下查詢將搜尋 EmployeeID = 1000 且在 2014 年 1 月 1 日至 2015 年 1 月 1 日的某段時間（包括上限）內保持活動狀態的員工行的行版本：

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;

臨時表注意事項和限制

由於系統版本控制的特性，在使用臨時表時，有一些應考慮的注意事項和限制。

使用臨時表時，請考慮以下事項。

• 臨時表必須定義主鍵，以便將當前表的記錄和歷史記錄表的記錄關聯起來，並且歷史記錄表不能定義主鍵。
• 用於記錄SysStartTime和SysEndTime值的 SYSTEM_TIME 時間段列必須使用數據類型 datetime2 進行定義。
• 如果歷史記錄表的名稱在歷史記錄表創建期間指定，則必須指定架構和表的名稱。
• 默認情況下，歷史記錄表是經過PAGE壓縮的。
• 如果當前表已分區，則歷史記錄表在默認檔案組上創建，因為不會自動將分區配置從當前表複製到歷史記錄表。
• 由於FILETABLE和FILESTREAM允許在外部進行數據操作，所以臨時表和歷史記錄表不能為FILETABLE，且可以包含FILESTREAMSQL Server以外的任何受支持的數據類型的列，因此系統版本控制不能得到保證。
• 儘管臨時表支持 Blob 數據類型，如(n)varchar(max)、varbinary(max)、(n)text和image，但由於其大小，會導致產生巨大的存儲成本，並可能對性能產生影響。因此在設計系統的過程中，應慎重使用這些數據類型。
• 必須在與當前表相同的資料庫中創建歷史記錄表。不支持對Linked Server的臨時查詢。
• 歷史記錄表不能有約束（主鍵、外鍵、表或列約束）。
• 臨時查詢（使用FOR SYSTEM_TIME子句的查詢）的頂部不支持索引視圖
• 對於系統版本控制的臨時表，Online 選項 (WITH (ONLINE = ON) 對ALTER TABLE ALTER COLUMN不起任何作用。無論為 ONLINE 選項指定的值是什麼，都不會 在線上執行 ALTER 列。
• INSERT和UPDATE語句無法引用 SYSTEM_TIME 時間段列。將阻止將值直接插入這些列的嘗試。
• SYSTEM_VERSIONING為ON時，不支持TRUNCATE TABLE
• 不允許直接修改歷史記錄表中的數據。
• 當前表上不允許ON DELETE CASCADE和ON UPDATE CASCADE。換言之，當臨時表引用外鍵關係中的表時（對應於 sys.foreign_keys 中的parent_object_id），將不允許 CASCADE 選項。若要解除此限制，請使用應用程式邏輯或 after 觸發器，以在主鍵表中進行刪除時保持一致性（對應於 sys.foreign_keys 中的referenced_object_id）。如果主鍵表是時態表而引用表為非時態表，則不存在此類限制。
  注意：此限制僅適用於 SQL Server 2016。SQL Database和 SQL Server 2017（從 CTP 2.0 開始）中支持 CASCADE 選項。
• 在當前表或歷史記錄表上均不允許使用INSTEAD OF觸發器，以避免導致 DML 邏輯失效。僅在當前表上允許AFTER觸發器。這些觸發器在歷史記錄表上會被阻止，以避免導致 DML 邏輯失效。
• 複製技術的使用受到限制。
• 始終啟用：完全支持
• 更改數據捕獲和更改數據跟蹤：僅當前表支持
• 快照和事務複製：僅支持未啟用臨時的單個發布伺服器和啟用了臨時的一個訂閱伺服器。在這種情況下，發布伺服器用於 OLTP 工作負載，而訂閱伺服器用於卸載報表（包括“AS OF”查詢）。
  不支持使用多個訂閱伺服器，因為這種方案可能導致臨時數據不一致，原因是每個伺服器都依賴於本地系統時鐘。
• 合併複製：不支持臨時表

定期查詢僅影響當前表中的數據。若要查詢歷史記錄表中的數據，必須使用臨時查詢。稍後將在本文檔中“查詢臨時數據”部分討論相關內容。

最佳索引策略將包括當前表上的聚集列存儲索引和/或 B 樹行存儲索引，以及歷史記錄表上的聚集列存儲索引，旨在最佳化存儲大小和性能。如果你創建/使用自己的歷史記錄表，我們強烈建議你創建此類型的索引，它包含以時間段列的結尾為開頭的時間段列，以加快臨時查詢，同時加快作為數據一致性檢查的一部分的查詢。默認歷史記錄表具有基於時間段列（結束、開始）創建的聚集行存儲索引。建議至少應使用非聚集行存儲索引。

創建歷史記錄表後，不會將下列對象/屬性從當前表複製到歷史記錄表

• 時間段定義
• 標識定義
• 索引
• 統計信息
• 檢查約束
• 觸發器
• 分區配置
• Permissions
• 行級別安全性謂詞

在歷史記錄表鏈中，無法將歷史記錄表配置為當前表。

執行架構操作

當 SYSTEM_VERSIONING 設定為 ON 時，架構修改操作將受限制。

禁止的 ALTER 架構操作

運算	當前表	歷史記錄表
DROP TABLE	已禁止	已禁止
ALTER TABLE...SWITCH PARTITION	僅 SWITCH IN（請參閱臨時表分區）	僅 SWITCH OUT（請參閱臨時表分區）
ALTER TABLE…DROP PERIOD	已禁止	-
ALTER TABLE…ADD PERIOD	-	已禁止

允許的 ALTER TABLE 操作

運算	當前	歷史記錄
ALTER TABLE...REBUILD	已允許（獨立）	已允許（獨立）
CREATE INDEX	已允許（獨立）	已允許（獨立）
CREATE STATISTICS	已允許（獨立）	已允許（獨立）