SQL Server Temporal Table 時態表


  1. 說明
    1. 建立資料表
    2. 加入資料
    3. 檢視時態表
    4. 還原資料
    5. 停止時態表
  2. 參考資料
  3. 相關連結

說明如何使用 SQL Server 時態表功能,讓自動的更新、刪除行為自動被記錄,以減省開發所需的時間,並可以易於掌握資料表的資料改變。

SQL Server Logo

說明

建立資料表

CREATE TABLE dbo.PokeBox
(
	ID INT NOT NULL PRIMARY KEY IDENTITY (1, 1),
    PokeName nvarchar(50) NOT NULL,
    PokeLevel INT NOT NULL,
	  Experience INT NOT NULL,
    SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
    SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.PokeBoxHistory))

加入資料

為讓資料的建立有時間的區隔,特別以 WAITFOR DELAY 進行等待。

INSERT INTO dbo.PokeBox 
  (PokeName, PokeLevel, Experience) VALUES('Squirtle', 15, 1010)
WAITFOR DELAY '00:00:01'

UPDATE dbo.PokeBox 
  SET PokeName = 'Wartortle', 
      PokeLevel = 16, 
      Experience = 0 
  WHERE PokeName = 'Squirtle'
WAITFOR DELAY '00:00:01'

UPDATE dbo.PokeBox 
  SET PokeName = 'Blastoise', 
      PokeLevel = 32, 
      Experience = 0 
  WHERE PokeName = 'Wartortle'
WAITFOR DELAY '00:00:01'

INSERT INTO dbo.PokeBox (PokeName, PokeLevel, Experience) VALUES('Pidgeot', 36, 100)
WAITFOR DELAY '00:00:01'

DELETE dbo.PokeBox WHERE PokeName = 'Pidgeot'

檢視時態表

SELECT * FROM dbo.PokeBox
SELECT * FROM dbo.PokeBoxHistory

還原資料

UPDATE dbo.PokeBox
SET PokeName = History.PokeName,
    PokeLevel = History.PokeLevel, 
    Experience = History.Experience
FROM dbo.PokeBox
FOR SYSTEM_TIME AS OF '2021-12-19 10:48:51' AS History

停止時態表

ALTER TABLE dbo.PokeBox SET (SYSTEM_VERSIONING = OFF)

在將時態表 SYSTEM_VERSIONING 關掉後,會變原本的資料表及時態表會分別變成兩張普通的 Table 。

DROP TABLE IF EXISTS dbo.PokeBox
DROP TABLE IF EXISTS dbo.PokeBoxHistory

參考資料

相關連結

SQL Server Integrated Service 初探

SQL Server 閃電般快速查詢指南⚡

SQL Server 周邊工具彙整筆記

SQL Server 學習資源筆記