建立由系統設定版本的時態表
適用於：
SQL Server 2016 (13.x) 和更新版本 Azure SQL DatabaseAzure SQL 受控執行個體
當您將記錄資料表的指定方式納入考量時，有三種方式可用來建立由系統設定版本的時態表：
具有匿名記錄資料表的時態表︰請指定目前資料表的結構描述，讓系統以自動產生的名稱建立對應的記錄資料表。
具有預設記錄資料表的時態表︰請指定記錄資料表結構描述名稱和資料表名稱，讓系統在該結構描述中建立記錄資料表。
具有事先建立之使用者定義記錄資料表的時態表︰請建立最符合需求的記錄資料表，然後在建立時態表期間參考該資料表。
建立具有匿名記錄資料表的時態表
建立具有「匿名」記錄資料表的時態表，是建立快速物件的方便選項，特別是在原型和測試環境中。 這也是建立時態表最簡單的方式，因為它在 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]。 Suffix 是選用的，只有在資料表名稱的前半部分不是唯一時才會加入。
記錄資料表會建立為資料列存放區資料表。 如果可能，會套用 PAGE 壓縮，否則不壓縮歷程記錄資料表。 例如，有些資料表設定 (例如 SPARSE 資料行) 不允許壓縮。
為記錄資料表建立的預設叢集索引，會使用自動產生的名稱，格式為 IX_<history_table_name>。 叢集索引包含
PERIOD資料行 (結尾、開頭)。若要將目前的資料表建立為記憶體最佳化資料表，請參閱 系統版本設定時態表與記憶體最佳化資料表。
建立具有預設記錄資料表的時態表
若您想要控制命名，但仍依賴系統以預設設定建立記錄資料表時，就很適合建立具有預設記錄資料表的時態表。 下例會建立新的資料表，啟用了系統建立版本，並有明確定義的歷程記錄資料表名稱。
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+ 型樹狀結構) 索引以進行點查閱。 這個使用者定義的記錄資料表建立之後，就會建立系統建立版本的時態表，將使用者定義的記錄資料表指定為預設的記錄資料表。
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));
備註
- 如果您打算對採用彙總或視窗型函數的記錄資料執行分析查詢，為取得良好的資料壓縮和查詢效能，強烈建議您建立叢集資料行存放區作為主要索引。
- 如果主要使用案例是資料稽核 (也就是，從目前的資料表中搜尋單一資料列的歷程記錄變更)，則有個不錯的選擇是建立具有叢集索引的資料列存放區記錄資料表
- 記錄資料表不能有主索引鍵、外部索引鍵、唯一索引、資料表條件約束或觸發程序。 它不能設定來進行異動資料擷取、變更追蹤，異動複寫或合併式複寫。
將非時態表變更為系統建立版本的時態表
您可以在現有的非時態表上啟用系統版本控制，例如當您想要將自訂時態性解決方案移轉至內建支援時。 例如，您可能有一組資料表，要使用觸發程序實作版本控制。 使用時態性系統版本設定較不複雜，還有其他好處，包括：
- 不可變的記錄
- 適用於時間移動查詢的新語法
- 更好的 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;。
下一步
意見反應
即將登場：在 2024 年，我們將逐步淘汰 GitHub 問題作為內容的意見反應機制，並將它取代為新的意見反應系統。 如需詳細資訊，請參閱：https://aka.ms/ContentUserFeedback。
提交並檢視相關的意見反應