SQL Server Temporal Table 時態表
2021-12-19
說明如何使用 SQL Server 時態表功能，讓自動的更新、刪除行為自動被記錄，以減省開發所需的時間，並可以易於掌握資料表的資料改變。
說明
建立資料表
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