時態表使用案例

發行項
08/01/2024

適用於：SQL Server

系統版本設定時態表在需要追蹤資料變更歷程記錄的案例中很有用。建議您在下列使用案例中考慮使用時態表，以獲得顯著的生產力優勢。

資料稽核

在儲存重要資訊，並隨時需要追蹤變更內容和變更時間，以及執行資料鑑識調查的資料表上，使用暫時性系統版本設定。

時態表可讓您在開發週期的早期階段針對資料稽核案例進行計畫，或視需要將資料稽核新增到現有應用程式或解決方案。

下圖顯示「Employee」資料表，其中的資料範例包括目前 (以藍色標示) 和歷史的資料列版本 (以灰色標示)。

圖表的右邊以視覺化的方式在時間軸上呈現資料列版本，以及透過時態表上不同的查詢類型 (不管包含或不包含 SYSTEM_TIME 子句) 來顯示您所選取的資料列。

在新的資料表上針對資料稽核啟用系統版本設定

如果您已識別出需要進行資料稽核的資訊，請將資料庫資料表建立為系統版本設定時態表。下列範例說明假設性 HR 資料庫中名為 Employee 的資料表案例：

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

如需建立系統版本設定的時態表的各種選項，請參閱＜建立由系統設定版本的時態表＞中的說明。

在現有的資料表上針對資料稽核啟用系統版本設定

如果您需要在現有資料庫中執行資料稽核，請使用 ALTER TABLE 來延伸非時態表以使其成為已系統版本設定。如＜建立由系統設定版本的時態表＞中所述，為了避免應用程式中的中斷性變更，請將期間資料行新增為 HIDDEN。

下列範例說明在假設性的 HR 資料庫中於現有「Employee」資料表上啟用系統版本設定。其會在兩個步驟中啟用 Employee 資料表中的系統版本設定。首先，會將新的期間資料行新增為 HIDDEN。然後，其會建立預設的歷程記錄資料表。

ALTER TABLE Employee ADD
ValidFrom DATETIME2(2) GENERATED ALWAYS AS ROW START HIDDEN
    CONSTRAINT DF_ValidFrom DEFAULT DATEADD (SECOND, -1, SYSUTCDATETIME()),
ValidTo DATETIME2(2) GENERATED ALWAYS AS ROW END HIDDEN
    CONSTRAINT DF_ValidTo DEFAULT '9999.12.31 23:59:59.99',
PERIOD FOR SYSTEM_TIME(ValidFrom, ValidTo);

ALTER TABLE Employee
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Employee_History));

重要

datetime2 資料類型的有效位數，在來源資料表和系統版本設定的資料表中必須是相同的。

在執行上述指令碼之後，將會在歷程記錄資料表中明確收集所有資料變更。在一般的資料稽核案例中，您會對在感興趣時間週期內套用到個別資料列的所有資料變更進行查詢。預設的歷程記錄資料表是以叢集資料列存放區 B 型樹狀結構建立，以有效地處理這個使用案例。

注意

文件通常會使用「B 型樹狀結構」一詞來指稱索引。在資料列存放區索引中，資料庫引擎會實作 B 型樹狀結構。這不適用於資料行存放區索引或經記憶體最佳化的資料表。如需詳細資訊，請參閱 SQL Server 和 Azure SQL 索引架構和設計指南。

執行資料分析

使用上述的任何一個方式啟用系統版本設定之後，您只需再執行一次查詢，便能開始資料稽核。下列查詢會搜尋「Employee」資料表中記錄的資料列版本，搜尋條件為 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 BETWEEN...AND 取代為 FOR SYSTEM_TIME ALL 以分析該特定員工的整個資料變更歷程記錄：

SELECT * FROM Employee
FOR SYSTEM_TIME ALL
WHERE EmployeeID = 1000
ORDER BY ValidFrom;

若要搜尋僅在特定期間內 (且沒有在該期間之外) 作用的資料列版本，請使用 CONTAINED IN。這個查詢非常有效率，因為只會查詢記錄資料表：

SELECT * FROM Employee
FOR SYSTEM_TIME CONTAINED IN (
    '2021-01-01 00:00:00.0000000', '2022-01-01 00:00:00.0000000'
)
WHERE EmployeeID = 1000
ORDER BY ValidFrom;

最後，在某些稽核案例，您可能會想要查看整體資料表在過去任何一個時間點的模樣：

SELECT * FROM Employee
FOR SYSTEM_TIME AS OF '2021-01-01 00:00:00.0000000';

系統版本設定的時態表會以 UTC 時區來存放期間資料行的值，但是您可能會發現在篩選資料和顯示結果時，使用本地時區總是會比較方便。下列程式碼範例顯示如何套用在本地時區中指定，並使用 SQL Server 2016 (13.x) 所導入 AT TIME ZONE 轉換成 UTC 的篩選條件：

/* Add offset of the local time zone to current time*/
DECLARE @asOf DATETIMEOFFSET = GETDATE() AT TIME ZONE 'Pacific Standard Time';

/* Convert AS OF filter to UTC*/
SET @asOf = DATEADD(HOUR, - 9, @asOf) AT TIME ZONE 'UTC';

SELECT EmployeeID,
    [Name],
    Position,
    Department,
    [Address],
    [AnnualSalary],
    ValidFrom AT TIME ZONE 'Pacific Standard Time' AS ValidFromPT,
    ValidTo AT TIME ZONE 'Pacific Standard Time' AS ValidToPT
FROM Employee
FOR SYSTEM_TIME AS OF @asOf
WHERE EmployeeId = 1000;

對於所有其他使用已系統版本設定資料表的案例，使用 AT TIME ZONE 會很有幫助。

以 FOR SYSTEM_TIME 在時態性子句中指定的篩選條件是 SARG-able。 SARG 代表「搜尋引數」，而 SARG-able 表示 SQL Server 可以使用基礎叢集索引來執行搜尋 (而不是掃描作業)。如需詳細資訊，請參閱＜SQL Server 索引架構和設計指南＞。

如果您直接查詢歷程記錄資料表，請確定篩選條件也是 SARG-able，方式是指定形式為 <period column> { < | > | =, ... } date_condition AT TIME ZONE 'UTC' 的篩選條件。

如果您將 AT TIME ZONE 套用到期間資料行，SQL Server 將會執行資料表或索引掃描，這可能會耗費相當多的資源。請在查詢中避免這類條件：

<period column> AT TIME ZONE '<your time zone>' > {< | > | =, ...} date_condition.

如需詳細資訊，請參閱＜查詢系統建立版本時態表中的資料＞。

時間點分析 (時間移動)

時間旅行案例不會專注於個別記錄的變更，而是顯示整個資料集在一段時間內的變更方式。時間移動有時候會包含數個您想要分析的相關時態表，每個時態表皆會以獨立的方式進行變更：

歷程記錄和目前資料中之重要指標的趨勢
「位於」過去任何時間點 (昨天、一個月前等) 之整體資料的確切快照集
感興趣的兩個時間點之間的差異 (例如，一個月前和三個月前的比較)

有許多實際的案例都會需要進行時間移動分析。為了說明此使用方式情節，我們將參考具有自動產生歷程記錄的 OLTP。

具有自動產生資料歷程記錄的 OLTP

在交易處理系統中，您可以分析重要計量在經過一段時間之後的變更。在理想情況下，分析歷程記錄並不應該影響 OLTP 應用程式的效能，因為該應用程式必須在最低延遲和最少資料鎖定的情況下存取資料的最新狀態。您可以使用已系統版本設定的時態表明確地保存完整的變更歷程記錄，來和目前的資料分開並於稍後進行分析，以將對主要 OLTP 工作負載所造成的影響降到最低。

針對大量交易處理的工作負載，我們建議您使用系統版本設定時態表與經記憶體最佳化的資料表，這能允許您以符合成本效益的方式，將目前的資料儲存在記憶體中，並將完整的變更記錄儲存在磁碟。

針對記錄資料表，我們建議您使用叢集資料行存放區索引，原因如下：

典型的趨勢分析受惠於叢集資料行存放區索引所提供的查詢效能。
搭配記憶體最佳化資料表的資料清除工作，在記錄資料表擁有叢集資料行存放區索引時，於 OLTP 工作負載較重之下的表現最佳。
叢集資料行存放區索引能提供絕佳的壓縮，特別是在並非所有資料行皆同時受到變更的情況下。

搭配記憶體內部 OLTP 使用時態表，能減少將整個資料集保持在記憶體內部的需求，並能讓您輕鬆地分辨熱門和冷門資料。

幾個符合此類別的實際案例範例，為庫存管理或貨幣交易。

下圖顯示用於庫存管理的簡化資料模型：

下列程式碼範例將 ProductInventory 建立為記憶體內系統版本設定時態表，並在歷程記錄資料表上具有叢集資料行存放區索引 (根據預設，這會取代資料列存放區索引)：

注意

請確定您的資料庫允許建立記憶體最佳化資料表。請參閱建立記憶體最佳化資料表和原生編譯的預存程序。

USE TemporalProductInventory;
GO

BEGIN
    --If table is system-versioned, SYSTEM_VERSIONING must be set to OFF first
    IF ((SELECT temporal_type
        FROM SYS.TABLES
        WHERE object_id = OBJECT_ID('dbo.ProductInventory', 'U')) = 2)
    BEGIN
        ALTER TABLE [dbo].[ProductInventory]
        SET (SYSTEM_VERSIONING = OFF);
    END

    DROP TABLE IF EXISTS [dbo].[ProductInventory];
    DROP TABLE IF EXISTS [dbo].[ProductInventoryHistory];
END
GO

CREATE TABLE [dbo].[ProductInventory] (
    ProductId INT NOT NULL,
    LocationID INT NOT NULL,
    Quantity INT NOT NULL CHECK (Quantity >= 0),
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
    ValidTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME(ValidFrom, ValidTo),
    --Primary key definition
    CONSTRAINT PK_ProductInventory PRIMARY KEY NONCLUSTERED (
        ProductId,
        LocationId
    )
)
WITH (
    MEMORY_OPTIMIZED = ON,
    SYSTEM_VERSIONING = ON (
        HISTORY_TABLE = [dbo].[ProductInventoryHistory],
        DATA_CONSISTENCY_CHECK = ON
    )
);

CREATE CLUSTERED COLUMNSTORE INDEX IX_ProductInventoryHistory
ON [ProductInventoryHistory] WITH (DROP_EXISTING = ON);

針對上述模型，以下是維護庫存之程序可能的模樣：

CREATE PROCEDURE [dbo].[spUpdateInventory]
    @productId INT,
    @locationId INT,
    @quantityIncrement INT
    WITH NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC
    WITH (
        TRANSACTION ISOLATION LEVEL = SNAPSHOT,
        LANGUAGE = N'English'
    )

    UPDATE dbo.ProductInventory
    SET Quantity = Quantity + @quantityIncrement
    WHERE ProductId = @productId
        AND LocationId = @locationId

    -- If zero rows were updated then this is an insert
    -- of the new product for a given location

    IF @@rowcount = 0
    BEGIN
        IF @quantityIncrement < 0
            SET @quantityIncrement = 0

        INSERT INTO [dbo].[ProductInventory] (
            [ProductId], [LocationID], [Quantity]
        )
        VALUES (@productId, @locationId, @quantityIncrement)
    END
END;

spUpdateInventory 預存程序可能會在庫存中插入新產品，或更新特定位置的產品數量。商務邏輯很簡單，且專注在持續維持最新狀態的準確度之上。會透過資料表更新對 Quantity 欄位進行遞增/遞減，同時系統版本設定的資料表會明確地將歷程記錄的維度新增到資料中，如下列圖表所示。

現在，對最新狀態的查詢工作將可以從原生編譯的模組上有效地執行：

CREATE PROCEDURE [dbo].[spQueryInventoryLatestState]
    WITH NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC
    WITH (
        TRANSACTION ISOLATION LEVEL = SNAPSHOT,
        LANGUAGE = N'English'
    )

    SELECT ProductId, LocationID, Quantity, ValidFrom
    FROM dbo.ProductInventory
    ORDER BY ProductId, LocationId
END;
GO

EXEC [dbo].[spQueryInventoryLatestState];

對經過一段時間之資料變更所進行的分析，在使用 FOR SYSTEM_TIME ALL 子句的情況下會變得很簡單，如下列範例所示：

DROP VIEW IF EXISTS vw_GetProductInventoryHistory;
GO

CREATE VIEW vw_GetProductInventoryHistory
AS
SELECT ProductId,
    LocationId,
    Quantity,
    ValidFrom,
    ValidTo
FROM [dbo].[ProductInventory]
FOR SYSTEM_TIME ALL;
GO

SELECT * FROM vw_GetProductInventoryHistory
WHERE ProductId = 2;

下圖顯示單一產品的資料歷程記錄，這可以透過在 Power Query、Power BI 或類似的商業智慧工具中匯入上述檢視，來輕鬆地進行轉譯：

時態表可以在此案例中用來執行其他類型的時間移動分析，例如重新建構「AS OF」過去任何時間點的庫存狀態，或比較屬於不同時間的快照集。

針對這個使用方式情節，您也可以延伸「產品」和「位置」資料表來使其成為時態表，以便稍後進行 UnitPrice 和 NumberOfEmployee 的變更歷程記錄分析。

ALTER TABLE Product ADD
ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN
    CONSTRAINT DF_ValidFrom DEFAULT DATEADD (SECOND, -1, SYSUTCDATETIME()),
ValidTo DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN
    CONSTRAINT DF_ValidTo DEFAULT '9999.12.31 23:59:59.99',
PERIOD FOR SYSTEM_TIME(ValidFrom, ValidTo);

ALTER TABLE Product
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ProductHistory));

ALTER TABLE [Location] ADD
ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN
    CONSTRAINT DFValidFrom DEFAULT DATEADD (SECOND, -1, SYSUTCDATETIME()),
ValidTo DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN
    CONSTRAINT DFValidTo DEFAULT '9999.12.31 23:59:59.99',
PERIOD FOR SYSTEM_TIME(ValidFrom, ValidTo);

ALTER TABLE [Location]
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.LocationHistory));

由於資料模組現已牽涉到多個時態表，AS OF 分析的最佳做法為建立可從相關資料表擷取必要資料的檢視，並將 FOR SYSTEM_TIME AS OF 套用到檢視上，這能大幅簡化對整體檔案模組狀態的重新建構：

DROP VIEW IF EXISTS vw_ProductInventoryDetails;
GO

CREATE VIEW vw_ProductInventoryDetails
AS
SELECT PrInv.ProductId,
    PrInv.LocationId,
    P.ProductName,
    L.LocationName,
    PrInv.Quantity,
    P.UnitPrice,
    L.NumberOfEmployees,
    P.ValidFrom AS ProductStartTime,
    P.ValidTo AS ProductEndTime,
    L.ValidFrom AS LocationStartTime,
    L.ValidTo AS LocationEndTime,
    PrInv.ValidFrom AS InventoryStartTime,
    PrInv.ValidTo AS InventoryEndTime
FROM dbo.ProductInventory AS PrInv
INNER JOIN dbo.Product AS P
    ON PrInv.ProductId = P.ProductID
INNER JOIN dbo.Location AS L
    ON PrInv.LocationId = L.LocationID;
GO

SELECT * FROM vw_ProductInventoryDetails
FOR SYSTEM_TIME AS OF '2022-01-01';

以下螢幕擷取畫面顯示針對 SELECT 查詢產生的執行計畫。下列說明資料庫引擎會負責在處理時態性關係時的所有複雜度：

使用下列程式碼來比較兩個時間點 (一天前和一個月前) 的產品庫存狀況：

DECLARE @dayAgo DATETIME2 = DATEADD (DAY, -1, SYSUTCDATETIME());
DECLARE @monthAgo DATETIME2 = DATEADD (MONTH, -1, SYSUTCDATETIME());

SELECT inventoryDayAgo.ProductId,
    inventoryDayAgo.ProductName,
    inventoryDayAgo.LocationName,
    inventoryDayAgo.Quantity AS QuantityDayAgo,
    inventoryMonthAgo.Quantity AS QuantityMonthAgo,
    inventoryDayAgo.UnitPrice AS UnitPriceDayAgo,
    inventoryMonthAgo.UnitPrice AS UnitPriceMonthAgo
FROM vw_ProductInventoryDetails
FOR SYSTEM_TIME AS OF @dayAgo AS inventoryDayAgo
INNER JOIN vw_ProductInventoryDetails
FOR SYSTEM_TIME AS OF @monthAgo AS inventoryMonthAgo
    ON inventoryDayAgo.ProductId = inventoryMonthAgo.ProductId
        AND inventoryDayAgo.LocationId = inventoryMonthAgo.LocationID;

異常偵測

異常偵測 (或極端值偵測) 是項目的識別，這些項目不符合預期模式或資料集中的其他項目。您可以使用已系統版本設定的時態表來偵測定期發生或不規則發生的異常，因為您可以利用 Temporal 查詢來快速找出特定模式。異常的定義，取決於您所收集的資料類型，以及您的商務邏輯。

下列範例顯示偵測銷售數字「峰值」的簡化邏輯。我們假設您是使用收集已購買產品之歷程記錄的時態表：

CREATE TABLE [dbo].[Product] (
    [ProdID] [int] NOT NULL PRIMARY KEY CLUSTERED,
    [ProductName] [varchar](100) NOT NULL,
    [DailySales] INT NOT NULL,
    [ValidFrom] [datetime2] GENERATED ALWAYS AS ROW START NOT NULL,
    [ValidTo] [datetime2] GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME([ValidFrom], [ValidTo])
)
WITH (
    SYSTEM_VERSIONING = ON (
        HISTORY_TABLE = [dbo].[ProductHistory],
        DATA_CONSISTENCY_CHECK = ON
    )
);

下圖顯示經過一段時間的購買情況：

假設平日的已購買產品數目擁有較小的差異，下列查詢將會識別單一的極端值：這些樣本與其最近的樣本之間有著顯著差異 (2 倍)，同時位於周圍的樣本差異並不顯著 (低於 20%)：

WITH CTE (
    ProdId,
    PrevValue,
    CurrentValue,
    NextValue,
    ValidFrom,
    ValidTo
    )
AS (
    SELECT ProdId,
        LAG(DailySales, 1, 1) OVER (PARTITION BY ProdId ORDER BY ValidFrom) AS PrevValue,
        DailySales,
        LEAD(DailySales, 1, 1) OVER (PARTITION BY ProdId ORDER BY ValidFrom) AS NextValue,
        ValidFrom,
        ValidTo
    FROM Product
    FOR SYSTEM_TIME ALL
    )
SELECT ProdId,
    PrevValue,
    CurrentValue,
    NextValue,
    ValidFrom,
    ValidTo,
    ABS(PrevValue - NextValue) / convert(FLOAT, (
            CASE
                WHEN NextValue > PrevValue THEN PrevValue
                ELSE NextValue
            END)) AS PrevToNextDiff,
    ABS(CurrentValue - PrevValue) / convert(FLOAT, (
            CASE
                WHEN CurrentValue > PrevValue THEN PrevValue
                ELSE CurrentValue
            END)) AS CurrentToPrevDiff,
    ABS(CurrentValue - NextValue) / convert(FLOAT, (
            CASE
                WHEN CurrentValue > NextValue THEN NextValue
                ELSE CurrentValue
            END)) AS CurrentToNextDiff
FROM CTE
WHERE ABS(PrevValue - NextValue) / (
        CASE
            WHEN NextValue > PrevValue THEN PrevValue
            ELSE NextValue
        END) < 0.2
    AND ABS(CurrentValue - PrevValue) / (
        CASE
            WHEN CurrentValue > PrevValue THEN PrevValue
            ELSE CurrentValue
        END) > 2
    AND ABS(CurrentValue - NextValue) / (
        CASE
            WHEN CurrentValue > NextValue THEN NextValue
            ELSE CurrentValue
        END) > 2;

注意

這個範例已刻意簡化。在生產案例中，您很有可能會使用進階的統計方法來識別沒有遵循一般模式的樣本。

緩時變維度

資料倉儲中的維度通常會包含關於實體的相對靜態資料，例如地理位置、客戶或產品。不過，某些案例也會需要您追蹤維度資料表中的資料變更。由於維度中發生修改的機率較低、發生方式較無法預期，且不會發生在適用於事實資料表的定期更新排程之內，使此類維度資料表被稱為緩時變維度 (SCD)。

根據變更歷程記錄保留方式的不同，緩時變維度有數個不同的類別：

維度類型	詳細資料
類型 0	不保留歷程記錄。維度屬性會反映原始值。
類型 1	維度屬性會反映最新的值 (先前的值會被覆寫)
類型 2	維度成員的每個版本都會在資料表中以個別資料列呈現，並通常以資料行代表有效期間
類型 3	透過在相同的資料列中使用額外的資料行，來針對選取屬性保留有限的歷程記錄
類型 4	在個別的資料表中保留歷程記錄，同時在原始的維度資料表上保留最新 (目前) 的維度成員版本

當您選擇 SCD 策略時，ETL 層 (擷取-轉換-載入) 將負責維持維度資料表的準確性，這通常需要更複雜的程式碼和額外維護工作。

已系統版本設定的時態表可以用來大幅降低程式碼的複雜度，因為資料歷程記錄會自動保留。由於時態表是使用兩個資料表進行實作，因此和類別 4 SCD 最為相似。不過，由於時態性查詢允許您僅參照目前的資料表，您也可以在計畫使用類別 2 SCD 的環境中考慮使用時態表。

若要將一般維度轉換成 SCD，您可以建立新的資料表，或將現有資料表更改為系統建立版本的時態表。如果現有維度資料表內含歷程記錄資料，請建立個別資料表並將歷程記錄資料移至該處，並將目前 (實際) 的維度版本保留在原始的維度資料表中。然後使用 ALTER TABLE 語法，將維度資料表轉換成具有預先定義歷程記錄資料表的系統版本設定時態表。

下列範例將說明該程序，並假設 DimLocation 維度資料表已擁有 ValidFrom 和 ValidTo 作為 datetime2 不可為 Null 資料行，並由 ETL 程序所填入：

/* Move "closed" row versions into newly created history table*/
SELECT * INTO DimLocationHistory
FROM DimLocation
WHERE ValidTo < '9999-12-31 23:59:59.99';
GO

/* Create clustered columnstore index which is a very good choice in DW scenarios*/
CREATE CLUSTERED COLUMNSTORE INDEX IX_DimLocationHistory ON DimLocationHistory;

/* Delete previous versions from DimLocation which will become current table in temporal-system-versioning configuration*/
DELETE FROM DimLocation
WHERE ValidTo < '9999-12-31 23:59:59.99';

/* Add period definition*/
ALTER TABLE DimLocation
ADD PERIOD FOR SYSTEM_TIME(ValidFrom, ValidTo);

/* Enable system-versioning and bind history table to the DimLocation*/
ALTER TABLE DimLocation
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DimLocationHistory));

當您建立 SCD 之後，在資料倉儲載入程序期間，維持 SCD 並不需要額外的程式碼。

下列說明顯示在涉及兩個 SCD (DimLocation 和 DimProduct) 與單一事實資料表的簡單案例中使用時態表的方式。

若要在報表中使用上述 SCD，您必須有效地調整查詢。例如，您可能會想要計算過去六個月中的總銷售量，以及每個資本的平均銷售產品數目。上述兩個計量都需要關聯來自事實資料表和維度的資料，這些資料可能會變更其對於分析相當重要的屬性 (DimLocation.NumOfCustomers、DimProduct.UnitPrice)。下列查詢將能正確地計算所需的計量：

DECLARE @now DATETIME2 = SYSUTCDATETIME();
DECLARE @sixMonthsAgo DATETIME2;

SET @sixMonthsAgo = DATEADD(month, - 12, SYSUTCDATETIME());

SELECT DimProduct_History.ProductId,
    DimLocation_History.LocationId,
    SUM(F.Quantity * DimProduct_History.UnitPrice) AS TotalAmount,
    AVG(F.Quantity / DimLocation_History.NumOfCustomers) AS AverageProductsPerCapita
FROM FactProductSales F
/* find corresponding record in SCD history in last 6 months, based on matching fact */
INNER JOIN DimLocation
FOR SYSTEM_TIME BETWEEN @sixMonthsAgo AND @now AS DimLocation_History
    ON DimLocation_History.LocationId = F.LocationId
    AND F.FactDate BETWEEN DimLocation_History.ValidFrom AND DimLocation_History.ValidTo
/* find corresponding record in SCD history in last 6 months, based on matching fact */
INNER JOIN DimProduct
FOR SYSTEM_TIME BETWEEN @sixMonthsAgo AND @now AS DimProduct_History
    ON DimProduct_History.ProductId = F.ProductId
    AND F.FactDate BETWEEN DimProduct_History.ValidFrom AND DimProduct_History.ValidTo
WHERE F.FactDate BETWEEN @sixMonthsAgo AND @now
GROUP BY DimProduct_History.ProductId, DimLocation_History.LocationId;

考量

如果根據資料庫交易時間所計算的有效期限適用於您的商務邏輯，便可以針對 SCD 使用系統建立版本的時態表。如果您載入資料有明顯的延遲，則交易時間可能無法使用。

根據預設，系統版本設定的時態表並不允許於載入後變更歷史資料 (您可以在將 SYSTEM_VERSIONING 設定為 OFF 之後修改歷程記錄)。針對時常發生歷程記錄資料變更的案例，這可能會是個限制。

已 Temporal 系統版本設定的資料表會針對任何資料行變更產生資料列版本。如果您想要針對特定資料行變更隱藏新版本，便需要將該限制併入 ETL 邏輯之中。

如果您預期 SCD 資料表中會有大量的歷程記錄資料列，請考慮使用叢集資料行存放區索引做為歷程記錄資料表的主要存放區選項。使用資料行存放區索引可降低歷程記錄資料表使用量，並加快分析查詢的速度。

修復資料列層級資料損毀

您可以依靠系統建立版本的時態表中的歷程記錄資料，來快速將個別資料列修復為先前所擷取的任何狀態。時態表的這個屬性，在您可以找出受影響資料列和/或在您知道發生不想要變更之時間的情況下相當有用。知道這點可讓您有效地執行修復，而不必處理備份。

這種方法有數個優點：

您可以精準地控制修復的範圍。沒有受到影響的記錄必須保持在最新的狀態，這通常是相當重要的需求。
作業很有效率，且資料庫能針對所有使用該資料的工作負載持續保持連線。
修復作業本身已進行版本設定。您擁有修復作業本身的稽核線索，因此如有必要，您可以於稍後針對發生的情況進行分析。

您可以相對輕鬆地自動化修復動作。接下來的程式碼範例顯示針對於資料稽核案例中所使用 Employee 資料表執行資料修復的預存程序。

DROP PROCEDURE IF EXISTS sp_RepairEmployeeRecord;
GO

CREATE PROCEDURE sp_RepairEmployeeRecord
    @EmployeeID INT,
    @versionNumber INT = 1
AS
WITH History
AS (
    /* Order historical rows by their age in DESC order*/
    SELECT
        ROW_NUMBER() OVER (PARTITION BY EmployeeID
            ORDER BY [ValidTo] DESC) AS RN,
        *
    FROM Employee FOR SYSTEM_TIME ALL
    WHERE YEAR(ValidTo) < 9999 AND Employee.EmployeeID = @EmployeeID
)
/* Update current row by using N-th row version from history (default is 1 - i.e. last version) */
UPDATE Employee
SET [Position] = H.[Position],
    [Department] = H.Department,
    [Address] = H.[Address],
    AnnualSalary = H.AnnualSalary
FROM Employee E
INNER JOIN History H ON E.EmployeeID = H.EmployeeID AND RN = @versionNumber
WHERE E.EmployeeID = @EmployeeID;

此預存程序會採用 @EmployeeID 和 @versionNumber 作為輸入參數。這個程序根據預設會將資料列狀態還原到歷程記錄的最新版本 (@versionNumber = 1)。

下圖顯示程序引動過程之前和之後的資料列狀態。紅色矩形標示目前不正確的資料列版本，而綠色矩形則從歷程記錄標示正確的版本。

EXEC sp_RepairEmployeeRecord @EmployeeID = 1, @versionNumber = 1;

這個修復預存程序可以定義為接受確切時間戳記，而非資料列版本。其會把資料列還原為於提供之時間點作用的任何版本 (也就是 AS OF 時間點)。

DROP PROCEDURE IF EXISTS sp_RepairEmployeeRecordAsOf;
GO

CREATE PROCEDURE sp_RepairEmployeeRecordAsOf
    @EmployeeID INT,
    @asOf DATETIME2
AS
/* Update current row to the state that was actual AS OF provided date*/
UPDATE Employee
SET [Position] = History.[Position],
    [Department] = History.Department,
    [Address] = History.[Address],
    AnnualSalary = History.AnnualSalary
FROM Employee AS E
INNER JOIN Employee FOR SYSTEM_TIME AS OF @asOf AS History
    ON E.EmployeeID = History.EmployeeID
WHERE E.EmployeeID = @EmployeeID;

針對相同的資料範例，下圖說明具有時間條件的修復案例。醒目提示的項目為 @asOf 參數、在歷程記錄中於所提供時間點作用的選取資料列，以及修復作業後在目前資料表中的新資料列版本：

資料更正可以成為資料倉儲和報告系統中自動資料載入的一部分。如果剛更新的值並不正確，在許多情況下，從歷程記錄還原為先前的版本已是可接受的移轉。下圖顯示對此程序進行自動化的方式：

顯示如何將流程自動化的圖表。

共用方式為

時態表使用案例

資料稽核

在新的資料表上針對資料稽核啟用系統版本設定

在現有的資料表上針對資料稽核啟用系統版本設定

執行資料分析

時間點分析 (時間移動)

具有自動產生資料歷程記錄的 OLTP

異常偵測

緩時變維度

考量

修復資料列層級資料損毀

意見反應

其他資源

共用方式為

時態表使用案例

資料稽核

在新的資料表上針對資料稽核啟用系統版本設定

在現有的資料表上針對資料稽核啟用系統版本設定

執行資料分析

時間點分析 (時間移動)

具有自動產生資料歷程記錄的 OLTP

異常偵測

緩時變維度

考量

修復資料列層級資料損毀

相關內容

意見反應

其他資源