五餅二魚工作室

James Fu 的技術學習之路

2015-09-21

[SQL]SQL Server 2016 新功能 Temporal 測試筆記

[SQL]SQL Server 2016 新功能 Temporal 測試筆記

在 Techday 2015 的時候聽到百敬老師介紹有關於 Temporal 的新功能，可以將資料歷史版本給保存下來，看起來是個蠻炫的功能，就不用像之前一樣花時間寫 Trigger 來處理了。因此周末利用一點時間趕快測試一下，把它的特性給了解一下。

 

當我們要建立的時候，可以使用以下的語法，在建立資料表的時候一併指定 Temporal 的資料表


CREATE TABLE DemoTemporal
( 
	ID	INT	PRIMARY KEY,
	Product	NVARCHAR(50),
	Price	NUMERIC(10,2),
	SysStartDateTime DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN,
	SysDueDateTime	 DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN,
    PERIOD FOR SYSTEM_TIME (SysStartDateTime,SysDueDateTime)  
) WITH(SYSTEM_VERSIONING = ON);

 

在建立的時候要注意資料表一定要有 Primary Key，並且加入兩個 DATETIME2 型態的欄位，用來記錄資料的存留時間，這兩個欄位名稱可以自訂，更特別的是可以加入 HIDDEN 的屬性，這樣對一般應用程式來說，在處理的時候就不會感覺到有那個欄位的存在。最後再加上 SYSTEM_VERSIONING 的參數，並且指定為 ON，就會自動建立出另外一個 Temporal 的資料表了。因此接下來我們用以下指令來查看我們所建立出來的資料表：


SELECT * FROM DemoTemporal
GO

SELECT * FROM sys.tables
GO

SELECT * FROM MSSQL_TemporalHistoryFor_565577053
GO

image

 

從上面圖中的第一項中可以看出，因為我們在欄位上加入 HIDDEN 的參數，因此當我們在下 SELECT 的時候，並不會看到那兩個欄位的存在，因此對於以往開發的程式來說，在搭配使用上應該沒有任何問題；而透過第二項中可以，當我們建立好我們的資料表的時候，如果沒有特別指定 Temporal 的資料表，那麼 SQL Server 預設會使用 「MSSQL_TemporalHistoryFor_<object id>」 的方式來命名。因此如果我們覺得這樣在使用上容易搞混的話，那麼也可以自己先建立好 Temporal 的資料表，然後再使用 ALTER 的指令去設定

 

在下面這個範例中，我另外建立一個命名為 history 的 schema，這樣原始資料表和 Temporal 的資料表都可以有相同的名稱，只是 schema 不同，這樣後面要使用的時候就會比較方便一點了。


CREATE SCHEMA history
GO

CREATE TABLE dbo.DemoTemporal2
( 
	ID	INT	PRIMARY KEY,
	Product	NVARCHAR(50),
	Price	NUMERIC(10,2),
	SysStartDateTime DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN,
	SysDueDateTime	 DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN,
    PERIOD FOR SYSTEM_TIME (SysStartDateTime,SysDueDateTime)  
) 
GO

CREATE TABLE history.DemoTemporal2
( 
	ID	INT	NOT NULL,
	Product	NVARCHAR(50),
	Price	NUMERIC(10,2),
	SysStartDateTime DATETIME2 NOT NULL,
	SysDueDateTime	 DATETIME2 NOT NULL,
) 
GO

ALTER TABLE DemoTemporal2 
	SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE=history.DemoTemporal2))
GO

建立好前面的範例資料表，我們可以看到在 SSMS 2016 上面，它會很貼心的在資料表後面加上 「(由系統控制版本)」 和 「(紀錄)」

image

 

 

在建立好測試資料表之後，接著我們就可以填入資料來測試


INSERT INTO [dbo].[DemoTemporal] (ID,Product,Price)
 VALUES ( 1 , 'A', 10 ),( 2 , 'B', 20 )
GO

SELECT * FROM DemoTemporal
GO

SELECT * FROM [dbo].[MSSQL_TemporalHistoryFor_565577053]
GO

image

 

因為 Temporal 是放歷史異動資料的，因此當使用 INSERT 指令的時候，Temporal 資料表內是沒有任何資料的，也就是說你可以放心使用 BULK INSERT or BCP 之類的匯入處理，是不會有影響的。那如果我們使用 UPDATE 或 DELETE 的指令的時候呢 ?


UPDATE DemoTemporal SET Price = 15 WHERE ID = 1
DELETE DemoTemporal WHERE ID = 2
GO

SELECT * FROM DemoTemporal
GO

SELECT * FROM [dbo].[MSSQL_TemporalHistoryFor_565577053]
GO

 

從圖片中可以看出，因為刪除後原始 Table 只剩下一筆被 UPDATE 後的紀錄，但在 Temporal 中確有兩筆資料異動的紀錄存在

image

 

但如果我們使用 TRUNCATE 指令呢 ? 它會被記錄下來嗎 ?? 答案是不行的，因此如果你需要下 TRUNCATE 來清空的時候，則必須先把  SYSTEM_VERSIONING 的參數指定為 OFF 之後，才可以下指令來處理。


TRUNCATE TABLE DemoTemporal
GO

image

 

 

 

前面測試了相關指令，接下來我們繼續測試如何查看歷史資料，首先我們先放一些基本資料


INSERT INTO [dbo].[DemoTemporal2](ID,Product,Price)
	VALUES ( 1, 'AAA', 100 ),( 2, 'BBB', 150 ),( 3, 'CCC', 200 )
GO

 

透過指令來進行資料維護，為了能確認時間點，因此這裡搭配 WAITFOR 的指令來執行


WAITFOR TIME '22:10';

UPDATE [dbo].[DemoTemporal2] 
	SET Price = Price + 5
	WHERE ID = 1

WAITFOR DELAY '00:02'

UPDATE [dbo].[DemoTemporal2] 
	SET Price = Price + 5
	WHERE ID > 1

WAITFOR DELAY '00:02'

DELETE [dbo].[DemoTemporal2] 
	WHERE ID = 1

WAITFOR DELAY '00:02'

DELETE [dbo].[DemoTemporal2] 
	WHERE ID > 1

image

 

因為 SQL Server 在進行管理的時候，都是使用 UTC 的時間來做存放，因此如果要取得真正 Local 的時間，則需要使用 SWITCHOFFSET 或透過 DATEADD 去做個轉換。


SELECT [ID]
      ,[Product]
      ,[Price]
      ,switchoffset([SysStartDateTime],'+08:00') [SysStartDateTime]
      ,switchoffset([SysDueDateTime]  ,'+08:00') [SysDueDateTime]
  FROM [DEMO].[history].[DemoTemporal2]

image

 

基本上 SQL Server 可以讓我們直接查到相關歷史異動，但如果你想直接知道在某個時間點所看到的資料會是如何，那麼 SQL Server 也有提供相關指令，可以在來源資料表上搭配 FOR SYSTEM_TIME 來查詢在某個時間點的資料，但要記住時間的轉換


SELECT * 
	FROM [dbo].[DemoTemporal2] 
	FOR SYSTEM_TIME AS OF '2015-09-20 14:15:00' 
GO

DECLARE @PointInTime DATETIME2
SET @PointInTime = DATEADD( hh, -8, '2015-09-20 22:15:00' )
SELECT * 
	FROM [dbo].[DemoTemporal2] 
	FOR SYSTEM_TIME AS OF @PointInTime
GO

image

 

除了可以使用 AS OF 指定一個時間之外，SQL Server 2016 還有提供其他幾種方式，像是 FROM .. TO、Between .. TO .. 和 CONTAINED IN ( .., ..) 的條件方式，讓你可以查到特定時間範圍內的資料


DECLARE @PointInTimeStart DATETIME2, @PointInTimeEnd DATETIME2
SET @PointInTimeStart = DATEADD( hh, -8, '2015-09-20 22:10:00' )
SET @PointInTimeEnd   = DATEADD( hh, -8, '2015-09-20 22:15:00' )
SELECT * 
	FROM [dbo].[DemoTemporal2] 
	FOR SYSTEM_TIME FROM @PointInTimeStart TO @PointInTimeEnd

SELECT * 
	FROM [dbo].[DemoTemporal2] 
	FOR SYSTEM_TIME CONTAINED IN( @PointInTimeStart,@PointInTimeEnd)
GO

image

 

而透過執行計畫查看的話，就會發現基本上當我們在使用這些指令的話，SQL Server 就是透過我們原始資料表上的時間欄位，以及 Temporal 資料表上的時間欄位去做 UNION ALL 的處理。

image

 

 

由於這個 Temporal 有可能因為長時間的資料異動，造成資料量會非常的大，因此在 SQL Server 的預設，會將 Temporal 資料庫採用 Page 壓縮。

image

 

但如果是我們自己建立的資料表，指定為 temporal 的話，那麼 SQL Server 不會主要幫你改變設定成為 Page 壓縮

image

 

只是從這樣的方式來看，因為資料表壓縮在舊版本中都放在 Enterprise Editon 中，那麼會不會 SQL Server 又是將這個功能給放到 Enterprise Editon 中呢 ?! 看來這個部分只能等明年四月正式發行的時候，才有辦法知道了，但從測試過程中，看起來它的確是個不錯的功能，這樣可以讓我們在開發或者是系統維護中，可以很容易的了解資料在不同時間的改變，也不會對既有系統有很影響，應該是很值得期待的一個新功能。

 

參考資料

1. Temporal Tables ( https://msdn.microsoft.com/zh-tw/library/dn935015.aspx )

2. Temporal data support in SQL Server 2016 ( http://sqlwithmanoj.com/2015/06/15/temporal-data-support-in-sql-server-2016-part-1/ )

系列文章

標籤雲