解决20762 Module 3 课程关于Temporal Table 在 Demo 时的错误

首先该Demo 主要是搭配AdvantureWorks2016 的资料库,使用Person.Person 这个资料表来做展示,看起来没有太大的问题,因此为了在既有的资料表上面要转换为Temporal Table ,该展示采用以下的Script 来进行

[SQL]Temporal 异常处理经验-LMLPHP

今天比较静下心来看这个问题,其实主要的问题有几点:

1.这个既有的Person.Person的资料表是有资料的,因此本来用在CREATE上的时候没有问题,但采用ALTER增加栏位的时候,因为会牵涉到栏位的值是NULL ,因此采用DEFAULT的方式填入预设值。

2. SYSTEM_TIME所填入的栏位值应该是UTC的时间,但是用SYSDATETIME()所取得资料是精确的当地时间,因此以我的环境来说,这个时间会比UTC时间多八个小时,因此会造成异常。

因此如果上述的指令要修改,则可以改成以下的语法,这样就可以顺利地执行了

[SQL]Temporal 异常处理经验-LMLPHP

设定好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 = MONTHS))
04-16 06:23