分享方式：

暫存資料表

  • 文章

適用於：SQL Server 2016 (13.x) 和更新版本 Azure SQL 資料庫 Azure SQL 受控執行個體

時態表 (亦稱為系統版本設定時態表) 這項資料庫功能包含內建的支援，可提供任一時間點儲存在資料表中之資料的相關資訊，而不光只是目前時間點上的正確資料。

開始使用系統建立版本的時態表，以及檢閱時態表使用案例。

什麼是系統建立版本的時態表？

系統建立版本的時態表是一種專為保留資料變更之完整歷程記錄而設計的使用者資料表，可以迅速執行時間點分析。 由於系統會管理每個資料列的有效期間 (也就是資料庫引擎)，因此這種類型的時態表會稱為系統設定版本的時態表。

每個時態表皆有兩個明確定義的資料行，分別各具 datetime2 資料類型。 這些資料行稱為期間資料行。 這些期間資料專供系統使用，記錄每一資料列經修改後的有效期間。 主資料表儲存的最新資料稱為「目前的資料表」，或簡單稱為「時態表」。

除了這些期間資料行，時態表也包含對其他內含鏡像結構描述 (稱為「記錄資料表」) 之資料表的參考。 系統使用記錄資料表，在時態表中的資料列有所更新或刪除時，自動儲存上一版的資料列。 您可以在建立時態表時，指定現有的歷程記錄資料表 (必須和結構描述相容)，也可以交由系統建立預設的歷程記錄資料表。

為何選擇時態表？

實際資料來源為動態，且商務決策通常依賴分析師可從資料演進獲得的見解。 時態表的使用案例包括︰

  • 稽核所有資料變更並視需求執行資料鑑識
  • 重新建構過去任何時間的資料狀態
  • 計算一段時間的趨勢
  • 維護決策支援應用程式的緩時變更維度
  • 從資料意外變更與應用程式錯誤中復原

時態表如何運作？

資料表的系統版本設定會實作為一對資料表、目前的資料表和歷程記錄資料表。 無論哪一種資料表，都會使用兩個額外的 datetime2 資料行，以定義每個資料列的有效期間︰

  • 期間開始資料行：系統會記錄此資料行中資料列的開始時間，通常表示為 ValidFrom 資料行。

  • 期間結束資料行：系統會記錄此資料行中資料列的結束時間，通常表示為 ValidTo 資料行。

目前的資料表包含每個資料列「目前的值」。 記錄資料表包含每一資料列前次的值 (「舊版」)(如有此值)，以及該值有效期間的開始時間和結束時間。

顯示時態表如何運作的圖表。

下列指令碼說明員工資訊的案例：

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 GENERATED ALWAYS AS ROW START,
    [ValidTo] DATETIME2 GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME(ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));

如需詳細資訊，請參閱建立系統建立版本的時態表。

  • 插入：系統會根據系統時鐘，將 ValidFrom 資料行的值設定為目前交易的開始時間 (UTC 時區)，並將 ValidTo 資料行的值指派給最大值 9999-12-31。 這會將資料列標示為開啟。

  • 更新：系統會將前一個資料列的值儲存在歷程記錄資料表中，並根據系統時鐘，將 ValidTo 資料行的值設定為目前交易的開始時間 (UTC 時區)。 這會將資料列標示為關閉，並記錄資料列有效的期間。 在目前資料表中，系統會以資料列的新值更新記錄，並根據系統時鐘將 ValidFrom 資料行的值設定為目前交易的開始時間 (以 UTC 時區為準)。 ValidTo 資料行目前資料表中更新資料列的值保持為 9999-12-31 的最大值。

  • 刪除：系統會將前一個資料列的值儲存在歷程記錄資料表中，並根據系統時鐘，將 ValidTo 資料行的值設定為目前交易的開始時間 (UTC 時區)。 這會將資料列標示為關閉，並記錄先前資料列有效的期間。 在目前資料表中，資料列受到移除。 目前的資料表的查詢不會傳回此資料列。 只有處理歷程記錄資料的查詢會傳回資料列已關閉的資料。

  • 合併：此作業猶如最多執行三個陳述式 (INSERT、UPDATE 和/或 DELETE)，視 MERGE 陳述式中指定的動作而定。

系統 datetime2 資料行記錄的時間，以交易本身的開始時間為準。 例如，在單一交易內插入的所有資料列，都會在對應到 SYSTEM_TIME 期間開頭的資料行中記錄相同的 UTC 時間。

當您在時態表上執行任何資料修改查詢時，即使沒有任何資料行值變更，資料庫引擎仍會將資料列新增至歷程記錄資料表。

如何查詢時態表？

SELECT ... FROM <table> 陳述式具有新的子句 FOR SYSTEM_TIME 並附帶五個時態特定的次子句，以在目前和歷程記錄資料表之間查詢資料。 這個新 SELECT 陳述式語法直接受到單一資料表支援，透過多個聯結以及在多個時態表上檢視進行傳播。

當您使用 FOR SYSTEM_TIME 子句加上五個次子句之一執行查詢時，將會包含時態表中的歷程記錄資料，如下圖所示。

顯示時態性查詢運作方式的圖表。

下列查詢使用篩選條件 WHERE EmployeeID = 1000，搜尋某員工的資料版本。該條件至少會搜尋期間中 2021 年 1 月 1 日到 2022 年 1 月 1 日(包含最新日期) 的這段時間：

SELECT * FROM Employee
    FOR SYSTEM_TIME
        BETWEEN '2021-01-01 00:00:00.0000000' AND '2022-01-01 00:00:00.0000000'
            WHERE EmployeeID = 1000 ORDER BY ValidFrom;

FOR SYSTEM_TIME 會篩選出具有零持續時間有效期間的資料列 (ValidFrom = ValidTo)。

如果您在相同交易內的相同主索引鍵上執行多個更新，就會產生這些資料列。 此時，時態查詢只會傳回文易前的資料列版本，以及交易後最新的資料列。

如果您需要在分析中加入這些資料列，請直接查詢記錄資料表。

在下表中，符合條件的資料列資料行中的 ValidFrom，代表所查詢之資料表中 ValidFrom 資料行中的值，ValidTo 則代表所查詢之資料表中 ValidTo 資料行中的值。 如需完整的語法和範例，請參閱＜FROM 子句與 JOIN、APPLY、PIVOT＞及＜查詢系統建立版本時態表中的資料＞。

運算式 查詢資料列 注意
AS OF date_time ValidFrom <= date_time AND ValidTo > 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 ValidFrom < end_date_time AND ValidTo > start_date_time 傳回資料表，其中包含指定時間範圍期間有效之所有資料列版本的值 (無論其生效時間在 FROM 引數的 start_date_time 參數值之前，或於 TO 引數的 end_date_time 參數值之後失效)。 就內部而言，時態表和其歷程記錄資料表之間會執行等位。 系統會篩選結果，以傳回所有資料列版本的值，該值在指定的時間範圍任何時間點內皆為作用中。 不包含恰好在 FROM 端點所定義的之最舊時間失效的資料列，也不包含恰好在 TO 端點所定義之最新時間才生效的資料列。
BETWEEN start_date_time AND end_date_time ValidFrom <= end_date_time AND ValidTo > 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) ValidFrom >= start_date_time AND ValidTo <= end_date_time 傳回資料表，其中包含在 CONTAINED IN 引數兩個期間值所定義之指定時間範圍內開啟和關閉之所有資料列版本的值。 包含恰好在範圍下限變為作用中的資料列，或是恰好在範圍上限就不在作用中的資料列。
ALL 所有資料列 傳回屬於目前和歷程記錄資料表的資料列聯集。

隱藏期間資料行

您可以選擇隱藏期間資料行，如此一來，未直接參考這些期間資料行的查詢，就不會傳回這些資料行 (例如執行 SELECT * FROM <table> 時)。

若要傳回隱藏的資料行，只須在查詢中直接參考隱藏資料行即可。 同樣地，INSERT 和 BULK INSERT 陳述式會無視這些新期間資料行而繼續執行 (而資料行值也會自動填入)。

如需如何使用 HIDDEN 子句的詳細資料，請參閱 CREATE TABLE 及 ALTER TABLE。

範例

相關內容