建立由系統設定版本的時態表

發行項
11/19/2024

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

當您將記錄資料表的指定方式納入考量時，有三種方式可用來建立由系統設定版本的時態表：

具有匿名歷程記錄資料表的時態表︰請指定目前資料表的結構描述，讓系統以自動產生的名稱建立對應的歷程記錄資料表。
具有預設歷程記錄資料表的時態表︰請指定歷程記錄資料表結構描述名稱和表格名稱，讓系統在該結構描述中建立歷程記錄資料表。
具有事先建立之使用者定義歷程記錄資料表的時態表︰請建立最符合需求的歷程記錄資料表，然後在建立時態表期間參考該資料表。

建立具有匿名歷程記錄資料表的時態表

建立具有「匿名」歷程記錄資料表的時態表，是建立快速物件的方便選項，特別是在原型和測試環境中。這也是最簡單的時態表建立方式，因為其在 SYSTEM_VERSIONING 子句中不需要任何參數。下例會建立新的資料表，啟用了系統建立版本，但未定義歷程記錄資料表的名稱。

CREATE TABLE Department
(
    DeptID INT NOT NULL PRIMARY KEY CLUSTERED,
    DeptName VARCHAR(50) NOT NULL,
    ManagerID INT NULL,
    ParentDeptID INT 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);

備註

由系統設定版本的時態表必須定義主索引鍵，而且只能有一個使用兩個 datetime2 資料行定義的 PERIOD FOR SYSTEM_TIME，其會宣告為 GENERATED ALWAYS AS ROW START 或 GENERATED ALWAYS AS ROW END。

PERIOD 資料行一律假設不可為 Null，即使未指定可 Null 性亦同。如果將 PERIOD 資料行明確定義為可為 Null，則 CREATE TABLE 陳述式將會失敗。

歷程記錄資料表和目前資料表或時態表的結構描述，在資料行數量、資料行名稱、順序和資料類型上必須一致。

匿名的記錄資料表會自動使用與目前資料表或時態表相同的結構描述建立。

匿名歷程記錄表格名稱的格式如下：MSSQL_TemporalHistoryFor_<current_temporal_table_object_id>_<suffix>。尾碼是選填的，只有在表格名稱的前半部分不是唯一時才會加入。

記錄資料表會建立為資料列存放區資料表。如果可能，會套用 PAGE 壓縮，否則不壓縮歷程記錄資料表。例如，有些資料表設定 (例如 SPARSE 資料行) 不允許壓縮。

為歷程記錄資料表建立的預設叢集索引，會使用自動產生的名稱，格式為 IX_<history_table_name>。叢集索引包含 PERIOD 資料行 (結尾、開頭)。

在 Fabric SQL 資料庫中，建立的歷程記錄數據表不會鏡像到 Fabric OneLake。

若要將目前的資料表建立為經記憶體最佳化的資料表，請參閱＜系統版本設定時態表與經記憶體最佳化的資料表＞。

建立具有預設記錄資料表的時態表

若您想要控制命名，但仍依賴系統以預設設定建立歷程記錄資料表時，就很適合建立具有預設歷程記錄資料表的時態表。下例會建立新的資料表，啟用了系統建立版本，並有明確定義的歷程記錄資料表名稱。

CREATE TABLE Department
(
    DeptID INT NOT NULL PRIMARY KEY CLUSTERED,
    DeptName VARCHAR(50) NOT NULL,
    ManagerID INT NULL,
    ParentDeptID INT 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.DepartmentHistory));

備註

建立記錄資料表所用的規則，與建立「匿名」記錄資料表所套用的規則相同，並使用只套用在具名記錄資料表的下列規則。

結構描述名稱是 HISTORY_TABLE 參數的必要項目。
如果指定的結構描述不存在，則 CREATE TABLE 陳述式將會失敗。
如果 HISTORY_TABLE 參數指定的資料表已經存在，系統會根據新建立的時態表驗證結構描述一致性和暫存資料一致性。如果您指定無效的歷程記錄資料表，則 CREATE TABLE 陳述式會失敗。

建立具有使用者定義記錄資料表的時態表

當使用者想要以特定的儲存體選項和調整為歷史查詢的不同索引來指定歷程記錄資料表時，就很適合建立具有使用者定義歷程記錄資料表的時態表。下例會使用與要建立的時態表一致的結構描述，建立使用者定義的歷程記錄資料表。針對此使用者定義的歷程記錄資料表，會建立叢集資料行存放區索引和其他非叢集資料列存放區 (B+ 型樹狀結構) 索引以進行點查閱。這個使用者定義的歷程記錄資料表建立之後，就會建立時態表，將使用者定義的歷程記錄資料表指定為預設的歷程記錄資料表。

注意

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

CREATE TABLE DepartmentHistory
(
    DeptID INT NOT NULL,
    DeptName VARCHAR(50) NOT NULL,
    ManagerID INT NULL,
    ParentDeptID INT NULL,
    ValidFrom DATETIME2 NOT NULL,
    ValidTo DATETIME2 NOT NULL
);
GO

CREATE CLUSTERED COLUMNSTORE INDEX IX_DepartmentHistory ON DepartmentHistory;

CREATE NONCLUSTERED INDEX IX_DepartmentHistory_ID_Period_Columns
ON DepartmentHistory (ValidTo, ValidFrom, DeptID);
GO

CREATE TABLE Department
(
    DeptID int NOT NULL PRIMARY KEY CLUSTERED,
    DeptName VARCHAR(50) NOT NULL,
    ManagerID INT NULL,
    ParentDeptID INT 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.DepartmentHistory));

備註

如果您打算對採用彙總或視窗型函數的記錄資料執行分析查詢，為取得良好的資料壓縮和查詢效能，強烈建議您建立叢集資料行存放區作為主要索引。

如果預計使用時態表進行資料稽核 (也就是，從目前的資料表中搜尋單一資料列的歷史變更)，則您應建立具有叢集索引的資料列存放區歷程記錄資料表。

歷程記錄資料表不能有主索引鍵、外部索引鍵、唯一索引、資料表條件約束或觸發程序。不能將其設定為進行異動資料擷取、變更追蹤，異動複寫或合併式複寫。

在 Fabric SQL Database 和已設定網狀架構鏡像的 Azure SQL 資料庫中，當您在時態表建立期間使用現有的數據表做為歷程記錄數據表時，現有的數據表就會停止鏡像。

將非時態表變更為系統建立版本的時態表

您可以在現有的非時態表上啟用系統版本控制，例如當您想要將自訂時態性解決方案移轉至內建支援時。

例如，您可能有一組資料表，要使用觸發程序實作版本控制。使用時態性系統版本設定較不複雜，還有其他好處，包括：

不可變的記錄
適用於時間移動查詢的新語法
更好的 DML 效能
最低的維護成本

轉換現有的資料表時，請考慮使用 HIDDEN 子句來隱藏新的 PERIOD 資料行 (datetime2 資料行 ValidFrom 和 ValidTo)，以免影響未明確指定資料行名稱 (例如，沒有資料行清單的 SELECT * 或 INSERT) 的現有應用程式不是設計來處理新的資料行。

在非時態表中加入版本設定

如果您想要開始追蹤包含資料的非時態表變更，則需要加入 PERIOD 定義，並選擇性地提供 SQL Server 將為您建立之空白歷程記錄資料表的名稱：

CREATE SCHEMA History;
GO

ALTER TABLE InsurancePolicy ADD
ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN
    CONSTRAINT DF_InsurancePolicy_ValidFrom DEFAULT SYSUTCDATETIME(),
ValidTo DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN
    CONSTRAINT DF_InsurancePolicy_ValidTo DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999'),
PERIOD FOR SYSTEM_TIME(ValidFrom, ValidTo);
GO

ALTER TABLE InsurancePolicy
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = History.InsurancePolicy));
GO

重要

DATETIME2 的精確度必須符合基礎資料表的精確度。

備註

將使用預設值不可為 Null 的資料行加入現有的資料表時，其大小是所有版本的資料作業大小，(中繼資料作業的) SQL Server Enterprise Edition 除外。使用具有 SQL Server Standard Edition 資料的大型現有歷程記錄資料表，加入一個非 Null 資料行會是很昂貴的作業。

必須小心選擇時段開始和時段結束資料行的條件約束︰

開始資料行的預設值會指定您認為現有資料列有效的時間點。其不能指定為未來的日期時間點。
結束時間必須指定為所指定 datetime2 精確度的最大值，例如 9999-12-31 23:59:59 或 9999-12-31 23:59:59.9999999。

加入 PERIOD 時，系統會執行目前資料表的資料一致性檢查，以確定時段資料行的現有值為有效。

在啟用 SYSTEM_VERSIONING 時指定現有的歷程記錄資料表，系統會對目前的資料表和記錄資料表執行資料一致性檢查。如果您將 DATA_CONSISTENCY_CHECK = OFF 指定為額外的參數，即可略過此檢查。

將現有的資料表移轉至內建支援

此範例示範如何根據觸發程序，將現有的解決方案移轉至內建的時態性支援。在這個範例中，我們假設自訂方案會將目前資料和記錄資料分割成兩個不同的使用者資料表 (ProjectTaskCurrent 和 ProjectTaskHistory)。

如果現有解決方案使用單一資料表，以儲存實際資料列和歷史資料列，則您應該先將資料分割成兩個資料表，然後執行下列範例中所示的移轉步驟。首先，卸載未來時態表上的觸發程序。然後，確定 PERIOD 資料行不可為 Null。

/* Drop trigger on future temporal table */
DROP TRIGGER ProjectCurrent_OnUpdateDelete;

/* Make sure that future period columns are non-nullable */
ALTER TABLE ProjectTaskCurrent
ALTER COLUMN [ValidFrom] DATETIME2 NOT NULL;

ALTER TABLE ProjectTaskCurrent
ALTER COLUMN [ValidTo] DATETIME2 NOT NULL;

ALTER TABLE ProjectTaskHistory
ALTER COLUMN [ValidFrom] DATETIME2 NOT NULL;

ALTER TABLE ProjectTaskHistory
ALTER COLUMN [ValidTo] DATETIME2 NOT NULL;

ALTER TABLE ProjectTaskCurrent ADD PERIOD
FOR SYSTEM_TIME([ValidFrom], [ValidTo]);

ALTER TABLE ProjectTaskCurrent SET (
    SYSTEM_VERSIONING = ON (
        HISTORY_TABLE = dbo.ProjectTaskHistory,
        DATA_CONSISTENCY_CHECK = ON
    )
);

備註

參考 PERIOD 定義中的現有資料行，會隱含地將 generated_always_type 變更為那些資料行的 AS_ROW_START 和 AS_ROW_END。

加入 PERIOD 時，系統會執行目前資料表的資料一致性檢查，以確定時段資料行的現有值為有效。

強烈建議您將 SYSTEM_VERSIONING 設為 DATA_CONSISTENCY_CHECK = ON，以針對現有資料強制執行資料一致性檢查。

如果慣用隱藏的資料行，請使用命令 ALTER TABLE [tableName] ALTER COLUMN [columnName] ADD HIDDEN;。

共用方式為

建立由系統設定版本的時態表

建立具有匿名歷程記錄資料表的時態表

備註

建立具有預設記錄資料表的時態表

備註

建立具有使用者定義記錄資料表的時態表

備註

將非時態表變更為系統建立版本的時態表

在非時態表中加入版本設定

備註

將現有的資料表移轉至內建支援

備註

意見反應

其他資源

共用方式為

建立由系統設定版本的時態表

建立具有匿名歷程記錄資料表的時態表

備註

建立具有預設記錄資料表的時態表

備註

建立具有使用者定義記錄資料表的時態表

備註

將非時態表變更為系統建立版本的時態表

在非時態表中加入版本設定

備註

將現有的資料表移轉至內建支援

備註

相關內容

意見反應

其他資源