微软的SQL Server 2016新增了历史表（Temporal Table）功能。启用历史表后，SQL Server会将主表中已经被修改过的历史数据，按时间存放在一张历史表中。管理员可以通过对比主表和历史表，获取该表在某段时间内的操作记录，达到表级别的审核效果。当主表数据发生误操作时，管理员可以直接通过历史表恢复数据。
历史表的作用：
1、能够保留表的所有数据的旧版本；
2、可以达到审核的功能，让管理员获悉表的数据变化情况；
3、主表数据发生误操作，可以快速从历史表中还原数据。
历史表的要求：
1、必须有主键；
2、必须定义两个数据类型为datetime2的列，用来记录开始（SysStartTime）和结束（SysEndTime）时间点。如果需要，可以使用HIDDEN标记隐藏这两个列；
3、历史表必须是和主表在结构上一模一样，包括字段名字和数据类型。
历史表的原理：
当插入（insert）发生时，事务开始的时间作为主表的SysStartTime，SysEndTime则被更新为9999-12-31，历史表不会有任何变化。当更新（update）发生时，历史记录表中的SysEndTime被更新为事务开始的时间，主表的SysStartTime则被更新为事务开始的时间，SysEndTime则被更新为9999-12-31。当删除（delete）发生时，历史记录表中的SysEndTime被更新为事务开始的时间。
本实验在VM Workstation上搭建2台虚拟机，分别为DC1和SQL16NA，均在域环境中，2台虚拟机的名称、角色以及IP可查看上表。DC1是域控制器，SQL16NA上部署了SQL Server 2016实例。
1、登录SQL Server 2016服务器：SQL16NA，并预先创建了一个测试数据库Northwind_CS。
2、预先准备了创建历史表和测试的T-SQL语句。
--创建支持历史表的T1数据表，指定历史表的名称为dbo.T1History
createtable T1(ID int identity primary key,
name varchar(500),
TimeStart datetime2 generated always as rowstart,
TimeEnd datetime2 generated always as rowend,
period for system_time(TimeStart,TimeEnd))
with(SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.T1History));
--往T1数据表中插入数据、更新数据和删除数据，观察两张表的数据变化
insertinto t1(name) values('ggg')
insertinto t1(name) values('ggg')
insertinto t1(name) values('ggg')
updatet1 set name='Tom' where id=3
deletefrom t1 where id=1
--查看主表和查看历史表
select* from [dbo].[T1]
select* from [dbo].[T1History]
--利用历史表恢复主表的误操作数据
UpdateNorthwindCS..T1 set name=B.name
FromNorthwindCS..t1 AS A Join NorthwindCS..T1History AS B
On A.ID= B.ID where A.id=3
3、进入Northwind_CS数据库，并运行T-SQL命令创建历史表。
4、查看已经支持历史表功能的T1数据表。会发现，T1表下多了一张命名为T1History的历史表。
5、往T1数据表中插入三行数据，然后查看T1表，发现数据已经插入成功。
此时，再查看历史表，发现历史表中没有数据，因为插入操作并没有修改原表的数据，因此历史表不会记录insert操作的数据。
6、为更新id=3的数据，删除id=1的数据。然后查看主表和历史表进行对比，发现历史表中将update和delete操作的历史记录都记录在历史表中了。
7、使用历史表还原被修改过的id=3的数据，发现数据能够正常恢复。
Windows Server 2016基础架构管理（MCSE）
学领未来——嘉为教育倾力打造的IT职业发展学习平台，依托于嘉为教育专业企业人才培养的经验，为众多客户提供包括职业发展与成长顾问、能力评测与提升建议、学习资源与学习管理、人才评价与认证服务的O2O立体化学习成长体系。
关注一下，干货不停
点
阅读原文查看更多精彩课程
