[SQL Server] 如何追蹤資料異動記錄 (2) : System Versioned Temporal Table

前言

接續前一篇對 Change Data Caputre 的採討, 本篇會著重在 SQL Server 2016 開始提供的 System-Versioned Temporal Table.

不知各位有沒有注意到, 在 SSMS 物件總管視窗裡 SQL Server 2016 的範例資料庫 (WideWorldImporters) 的剖份 table, 會被標註為 (由系統控制版本), 這個到底是什麼呢? 您可以試著把它點開, 它下面又掛了一個 table, 標註為 (記錄), 如下圖的 Cities 與 Cities_Archive. 其實, 後者 Cities_Archive 就是一個 System-Versioned Temporal Table, 綁定在前者 Cities 身上.

茲分為以下幾個部份及實作步驟進行說明:

1.. 何謂 System-Versioned Temporal Table?

2.. 建立測試資料庫

3.. 針對現有的資料表, 加上 Temporal Table 的功能

4.. 建立新的資料表, 同時加上 Temporal Table 的功能

5.. 新增 2 筆資料

6.. 修改 1 筆資料

7.. 刪除 1 筆資料

8.. 查 Temporal Table 的一些語法

9.. 適用情境

10.. 結論

11.. 參考文件

1.. 何謂 System-Versioned Temporal Table?

Temporal (時態性) 是 ANSI SQL 2011 納入的資料庫功能.

Temporal Table 是 SQL Server 實作的方式, 它會保留資料變更的所有歷史異動記錄. 可以拿來作資料異動追蹤, 或資料異動趨勢分析. 至於為何是 System-Versioned, 主要是因為每個資料列的有效期間是由系統 (也就是資料庫引擎) 所管理. 什麼是資料列有效期間呢? 我想, 我們看範例會比較容易了解. 為了避免文章過長, 以下的內容, 會統一簡稱為 Temporal Table.

2.. 建立測試資料庫

DROP DATABASE IF EXISTS TemporalSample;
GO
CREATE DATABASE TemporalSample;
GO

3.. 針對現有的資料表, 加上 Temporal Table 的功能

(1) 建立不含 Temporal Table 的資料表, 同時新增 2 筆資料

USE TemporalSample;
GO
DROP TABLE IF EXISTS [dbo].[Department]
GO
CREATE TABLE [dbo].[Department]
( [ID]     INT           NOT NULL
, [Name]   NVARCHAR(20)  NOT NULL
CONSTRAINT PK_Department PRIMARY KEY CLUSTERED ( [ID] )
);
GO
INSERT  INTO [dbo].[Department]
VALUES  (1, 'MIS')
  ,  (2, 'HR');
GO

SELECT *
FROM [dbo].[Department];
GO

(2) 為 [dbo].[Department] 加上 Temporal Table 的功能

A.. 加入了 ValidFrom 及 ValidateTo 這 2 個欄位. 資料型態是 DATETIME2(2), 且存放的是 UTC 的時間.

B.. ValidFrom:

* GENERATED ALWAYS AS ROW START --> 資料列的起始效期

* HIDDEN --> SELECT * 看不到, 必須要 SELECT 個別欄位, 且指定 ValidFrom 才能看到

* CONSTRAINT DEFAULT --> 給予預設值 (for 現行資料及後續的新增資料)

C.. ValidTo:

* GENERATED ALWAYS AS ROW END --> 資料列的結束效期

* HIDDEN --> SELECT * 看不到, 必須要 SELECT 個別欄位, 且指定 ValidTo 才能看到

* CONSTRAINT DEFAULT --> 給予預設值 (for 現行資料及後續的新增資料) (預設為最大的時間值)

D.. PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo) : 代表要以 ValidFrom 與 ValidTo 這 2 個欄位, 作為效期的起始及終止

E.. SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DepartmentHistory)) : 啟用 Temporal Table, 其歷史資料檔為 DepartmentHistory.

/* 
Turn ON system versioning in Department table in two steps 
(1) add new period columns (HIDDEN) 
(2) create default history table 
*/ 
ALTER TABLE [dbo].[Department] 
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); 
GO
ALTER TABLE [dbo].[Department]   
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DepartmentHistory));
GO

並截圖如下, 以標示重點.

直接 SELECT *, 是看不到 ValidFrom 及 ValidTo 這 2 個欄位的

SELECT *
FROM [dbo].[Department];
GO

要特別指定才能看到

SELECT ID, Name, ValidFrom, ValidTo
FROM [dbo].[Department];
GO

並截圖如下, 以標示重點.

看一下 SSMS 物件總管視窗, 可以發現它的圖示有變, 並加上對應的標註

4.. 建立新的資料表, 同時加上 Temporal Table 的功能

(1) 建立 Temporal Table 的語法, 大致與前面相同, 不再贅述

(2) 關於移除 Temporal Table 功能的部份, 可以參以下的 ---- Code Block 1 ---- 的區塊

USE TemporalSample;
GO
---- Code Block 1 ---- (BEGIN)
IF OBJECT_ID('dbo.Employee') IS NOT NULL
   ALTER TABLE [dbo].[Employee] SET ( SYSTEM_VERSIONING = OFF) ;
GO
DROP TABLE IF EXISTS [dbo].[Employee]
GO
DROP TABLE IF EXISTS [dbo].[EmployeeHistory]
GO
---- Code Block 1 ---- (END)

CREATE TABLE Employee 
(  
   [ID]         INT NOT NULL  PRIMARY KEY CLUSTERED 
,  [Name]       NVARCHAR(50)  NOT NULL
,  [Position]   NVARCHAR(50)  NOT NULL 
,  [DeptID]     INT           NOT NULL
,  [Address]    NVARCHAR(100) 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));
GO

看一下 SSMS 物件總管視窗, 可以發現它的圖示有變, 並加上對應的標註

試一下移除 Temporal Table 功能的程式段

IF OBJECT_ID('dbo.Employee') IS NOT NULL
   ALTER TABLE [dbo].[Employee] SET ( SYSTEM_VERSIONING = OFF) ;
GO

看一下 SSMS 物件總管視窗, Employee 與 EmployeeHistory 均成為一般的 table

為了後續的測試, 還是先回復一下 Temporal Table 的功能

ALTER TABLE [dbo].[Employee]  
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));
GO

5.. 新增 2 筆資料

USE [TemporalSample]
GO
INSERT INTO [dbo].[Employee]
   ([ID], [Name], [Position], [DeptID], [Address], [AnnualSalary] )
VALUES
   (1, N'jasper', N'engineer', 1, N'taipei', 1500)
,  (2, N'joseph', N'manager', 1, N'taipei',  3000)
GO

查一下資料, Employee 有 2 筆資料, 其 ValidTo 的內容為 DATETIME2 的最大值. 但 EmployeeHistory 是沒有資料的, 因為 EmployeeHistory 是存放修改前的資料, 但 INSERT 的動作, 並沒有修改前的資料

SELECT *
FROM [dbo].[Employee]
GO
SELECT *
FROM [dbo].[EmployeeHistory]
GO

6.. 修改 1 筆資料

USE TemporalSample;
GO
UPDATE A
SET A.[AnnualSalary] = 4000
FROM [dbo].[Employee] A
WHERE A.[ID] = 2;
GO

可以發現, EmployeeHistory 保留了修改前的資料內容, 最新的資料, 還是在 Employee 資料表.

7.. 刪除 1 筆資料

USE TemporalSample;
GO
DELETE A
FROM [dbo].[Employee] A
WHERE A.[ID] = 2;
GO

可以發現, EmployeeHistory 保留了被刪除的資料內容, 該筆的 ValidTo 欄位, 代表的是刪除的時間點.

8.. 查 Temporal Table 的一些語法

當然可以直接查 EmployeeHistory 資料表, 但 SQL Server 也提供了由 Employee 查詢的方式, 可以細讀 "參考文件 8.."

USE [TemporalSample]
GO
SELECT [ID]
      ,[Name]
      ,[Position]
      ,[DeptID]
      ,[Address]
      ,[AnnualSalary]
      ,[ValidFrom]
      ,[ValidTo]
  FROM [dbo].[Employee]
--(1)
FOR SYSTEM_TIME ALL ORDER BY [ValidFrom]; 
--(2)
--FOR SYSTEM_TIME BETWEEN '2017-12-07 T06:35:00' AND '2017-12-08 T00:00:00';
--FOR SYSTEM_TIME FROM '2017-12-07 T06:35:00' TO '2017-12-08 T00:00:00';
--(3).A
--FOR SYSTEM_TIME CONTAINED IN ('2017-12-07 T06:35:00','2017-12-08 T00:00:00');
--(3).B
--FOR SYSTEM_TIME CONTAINED IN ('2017-12-07 T00:00:00','2017-12-08 T00:00:00');
--(4)
--FOR SYSTEM_TIME AS OF '2017-12-07 06:33:00.00';
GO

(1) ALL : 查出全部

<for clause> 語法範例:
FOR SYSTEM_TIME ALL ORDER BY [ValidFrom];