時態表使用案例

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

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

資料稽核

您可以對儲存重要資訊的資料表使用系統版本控制，以追蹤變更了哪些內容及其變更時間，並在任何時間點執行資料鑑識。

在開發週期的早期階段，利用時序表規劃資料稽核情境。你需要時，可以為現有應用程式或解決方案加入資料稽核。

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

圖表右側以時間軸呈現資料列版本，以及在時態資料表上使用不同查詢類型時，無論是否搭配 SYSTEM_TIME 子句所選取的資料列。

為新資料表啟用系統版本控制，以用於資料稽核

如果您已識別出需要進行資料稽核的資訊，請將資料庫資料表建立為系統版本設定時態表。以下範例說明一個在假設的人資資料庫中，包含名為 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));

Important

datetime2 資料類型在來源資料表中的精確度，必須與系統版本控制的歷程記錄資料表中的精確度相同。

執行前一個腳本後，歷史表會透明地收集所有資料變更。在典型的資料稽核情境中，你會查詢在所關注的時間範圍內套用至單一資料列的所有資料變更。預設的歷程記錄資料表是以叢集資料列存放區 B 型樹狀結構建立，以有效地處理這個使用案例。

Note

文件通常會使用「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 時區來存放期間資料行的值，但是您可能會發現在篩選資料和顯示結果時，使用本地時區總是會比較方便。以下程式碼範例展示了如何套用一個過濾條件，該條件在當地時區指定，然後透過以下方式 AT TIME ZONE轉換為UTC：

/* Add offset of the local time zone to current time*/
DECLARE @asOf AS 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 子句中指定的過濾條件是 SARGable。

Note

在關聯式資料庫中，SARGable 一詞是指可使用索引來加快查詢執行速度的可搜尋引數的述詞。如需詳細資訊，請參閱 SQL Server 和 Azure SQL 索引架構和設計指南。

如果你直接查詢歷史資料表，請確保你的篩選條件也符合 SARGable，並以 <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 工作負載所造成的影響降到最低。

對於 SQL Server 和 Azure SQL 受控執行個體中高交易處理工作負載，我們建議使用系統版本化的時序表搭配記憶體優化的資料表，這樣你可以以經濟實惠的方式將目前資料存於記憶體中，並將完整的變更歷史存於磁碟上。

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

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

使用時序表搭配記憶體內 OLTP，減少了將整個資料集保留在記憶體中的需求，並能輕鬆區分熱資料與冷資料。

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

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

以下程式碼範例會建立名為 ProductInventory 的記憶體內部系統版本控制時態資料表，並在其歷程記錄資料表上建立叢集資料行存放索引（此索引會取代預設建立的資料列存放索引）：

Note

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

USE TemporalProductInventory;
GO

BEGIN

    --If the table is system-versioned, set SYSTEM_VERSIONING 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,
    --Primary key definition
    CONSTRAINT PK_ProductInventory PRIMARY KEY NONCLUSTERED (ProductId, LocationId),
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
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
        BEGIN
            SET @quantityIncrement = 0;
        END

        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

EXECUTE [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 狀態，或比較屬於不同時間點的快照。

在此使用情境中，你也可以將 Product 和 NumberOfEmployee 資料表延伸為時態資料表，以便後續分析 UnitPrice 和 Location 的變更歷程。

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 AS DATETIME2 = DATEADD(DAY, -1, SYSUTCDATETIME());
DECLARE @monthAgo AS 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;

異常偵測

異常偵測，或異常值偵測，是指識別不符合預期模式或與資料集中其他項目不同的項目。你可以使用系統版本化的時間表，透過時間查詢快速定位特定模式，來偵測週期性或不規則發生的異常現象。什麼算是異常，取決於你收集的資料類型和你的商業邏輯。

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

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;

Note

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

緩慢變化的維度

資料倉儲中的維度通常會包含關於實體的相對靜態資料，例如地理位置、客戶或產品。不過，某些案例也會需要您追蹤維度資料表中的資料變更。由於維度的變動發生頻率較低，且發生在不可預測且超出事實表的常規更新時程，這類維度表稱為緩慢變化維度（Slowly changing dimensions，SCD）。

根據變化歷史的保存方式，有幾種緩慢變化的維度類別：

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

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

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

要將你的一般維度轉換成 SCD，你可以建立一個新的維度，或修改現有的維度，使其成為系統版本的時序表。如果你現有的維度表包含歷史資料，請建立一個獨立的表格，並將歷史資料移到那裡，並將目前（實際）的維度版本保留在原本的維度資料表中。然後使用 ALTER TABLE 語法，將維度資料表轉換成具有預先定義歷程記錄資料表的系統版本設定時態表。

以下範例說明此過程，並假設 DimLocation 維度表已有 ValidFrom 和 ValidTo 為 datetime2 不可空欄位，ETL 程序會填充這些欄位：

將封閉的列版本移入新的歷史表：

SELECT *
INTO DimLocationHistory
FROM DimLocation
WHERE ValidTo < '9999-12-31 23:59:59.99';
GO

建立一個叢集欄位儲存索引，這是資料倉儲情境中的一個不錯選擇：
```
CREATE CLUSTERED COLUMNSTORE INDEX IX_DimLocationHistory
    ON DimLocationHistory;
```
從在時態系統版本控制組態中成為目前資料表的 DimLocation 刪除先前版本：
```
DELETE FROM DimLocation
WHERE ValidTo < '9999-12-31 23:59:59.99';
```

新增期間定義：

ALTER TABLE DimLocation
    ADD PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);

啟用系統版本控制並將歷史表綁定至：DimLocation

ALTER TABLE DimLocation
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DimLocationHistory));

在建立 SCD 後，在資料倉儲載入過程中，你不需要額外程式碼來維護它。

以下圖示展示了如何在涉及兩個 SCD（DimLocation 和 DimProduct）與一個事實表的基本情境中使用時間表。

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

下列查詢將能正確地計算所需的計量：

DECLARE @now AS DATETIME2 = SYSUTCDATETIME();
DECLARE @sixMonthsAgo AS 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 AS 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;

Considerations

如果根據資料庫交易時間計算的有效期符合你的商業邏輯，使用系統版本化的時間表來處理 SCD 是可以接受的。如果你載入資料時延遲很長，交易時間可能無法被接受。

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

時序系統版本化資料表會在任何欄位變更時產生列版本。如果你想在某個欄位變更時抑制新版本，就必須將這個限制納入 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 using N-th row version from history
(default is 1, that is, the last version) */
UPDATE Employee
    SET [Position] = h.[Position],
        [Department] = h.Department,
        [Address] = h.[Address],
        AnnualSalary = h.AnnualSalary
FROM Employee AS e
     INNER JOIN History AS h
         ON e.EmployeeID = h.EmployeeID
        AND RN = @versionNumber
WHERE e.EmployeeID = @EmployeeID;

此預存程序會採用 @EmployeeID 和 @versionNumber 作為輸入參數。它預設會將列狀態恢復到歷史記錄@versionNumber = 1中的最後版本（）。

下圖顯示程序呼叫前後該列的狀態。紅色矩形標示當前錯誤的行列版本，綠色矩形則標示歷史中正確的版本。

EXECUTE 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 參數、歷程記錄中在所提供時間點實際有效的選取資料列，以及修復作業後目前資料表中的新資料列版本：

資料更正可以成為資料倉儲和報告系統中自動資料載入的一部分。如果新近更新的值不正確，那麼在許多情況下，從歷程記錄還原先前版本，通常已足以作為緩解措施。下圖顯示對此程序進行自動化的方式：

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

意見反應

此頁面對您有幫助嗎？

Last updated on 2026-08-18

時態表使用案例

資料稽核

為新資料表啟用系統版本控制，以用於資料稽核

對現有資料表啟用系統版本控制，以用於資料稽核

執行資料分析

時間點分析（時光回溯）

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

異常偵測

緩慢變化的維度

Considerations

修復資料列層級的資料損毀

相關內容

意見反應

其他資源