[SQL]Temporal 異常處理經驗

解決 20762 Module 3 課程關於 Temporal Table 在 Demo 時的錯誤

前一陣子在教 20762 課程的時候,在展示 Temporal 的時候,忽然發現 Demo 的 Script 是失敗的,當下因為只剩下沒有幾分鐘,因此沒有立即的處理,就直接拿之前另外一篇文章「SQL Server 2016 新功能 Temporal 測試筆記」中的範例來做展示和說明,因此今天找個時間看一下到底範例出甚麼樣的問題。

首先該 Demo 主要是搭配 AdvantureWorks2016 的資料庫,使用 Person.Person 這個資料表來做展示,看起來沒有太大的問題,因此為了在既有的資料表上面要轉換為 Temporal Table ,該展示採用以下的 Script 來進行

今天比較靜下心來看這個問題,其實主要的問題有幾點:

1. 這個既有的 Person.Person 的資料表是有資料的,因此本來用在 CREATE 上的時候沒有問題,但採用 ALTER 增加欄位的時候,因為會牽涉到欄位的值是 NULL ,因此採用 DEFAULT 的方式填入預設值。

2. SYSTEM_TIME 所填入的欄位值應該是 UTC 的時間,但是用 SYSDATETIME() 所取得資料是精確的當地時間,因此以我的環境來說,這個時間會比 UTC 時間多八個小時,因此會造成異常。

因此如果上述的指令要修改,則可以改成以下的語法,這樣就可以順利地執行了

設定好 SYSTEM_TIME 欄位後,就可以透過下面的指令來指定歷史資料表

ALTER TABLE Person.Person
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = Person.Person_History));
GO 

另外一個在介紹 Temporal 的時候沒有注意到的,就是由於在 SQL Server 2016 版本提出的時候,針對歷史資料的清除時,只能先透過指令將資料表的 SYSTEM_VERSIONING = OFF,此時就可以自己手動去清除歷史資料表內過期的資訊了,當刪除完畢之後,再重新透過 SYSTEM_VERSIONING = ON 的方式,並重新指定歷史資料表的名稱,這樣才可以將歷史資料清除。

而這樣的方式,在 SQL Server 2017 和 Azure SQL Database 上,有了更方便的方式,首先要在資料庫上面開啟歷史資料清除的功能

ALTER DATABASE AdvantureWorks2016 SET TEMPORAL_HISTORY_RETENTION ON

接著在資料表上,只要搭配 HISTORY_RETENTION_PERIOD 的參數,設定好資料保留的期限,那其他你就不用自己去處理了,在下面範例中,我們設定資料只保留一個月

ALTER TABLE Person.Person
SET (SYSTEM_VERSIONING = ON 
		(HISTORY_TABLE = Person.Person_History,
		 HISTORY_RETENTION_PERIOD = 1 MONTHS))