澳门新萄京官方网站-www.8455.com-澳门新萄京赌场网址

带你熟悉SQLServer2016中的System

2019-11-16 作者:数据库网络   |   浏览(183)

什么是 System-Versioned Temporal Table?

System-Versioned Temporal Table,一时容小编管它叫版本由系统调整的有时表,它是 SQL Server 二〇一五中的新型顾客表,用于保留完好的数据变动历史记录。 它由此称为版本由系统调整的有时表,是因为每风流倜傥行的保藏期由数据库引擎管理。

每一个一时表有多少个显式定义的列,此中每一个列都有一个 datetime2 数据类型。每当数据更改后,系统将以垄断(monopoly卡塔 尔(阿拉伯语:قطر‎方式接纳那一个列来记录每行的保质期。

除去那些列以外,该表还带有对接收镜像架构的另一个历史表的引用。 每当更新或删除了有的时候表中的某行后,系统将选取历史表来自动积存该行的先前版本。

本条临时表的出产,在必然水平上完全可以代表CDC,可用来ETL,追溯数据,审计等。早先CDC能用到之处这一个会越来越好用且更易维护。此外,此前约定让程序员更新数据时必得创新Update提姆e之类的预约也全然能够交由系统和谐主宰了。

怎么是系统版本的Temporal Table

放假之前十分跟自家聊到了弹指间2014有个风趣的法力叫 Temporal Table ,明日去看了瞬间素材整理一下。

始建语法:

CREATE TABLE MyTable
(
 Id BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY
,MyCode CHAR(5)
,MyName NVARCHAR(200)
,RecordStartTime DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL
,RecordEndTime DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL
,PERIOD FOR SYSTEM_TIME(RecordStartTime,RecordEndTime)
) 
WITH(SYSTEM_VERSIONING = ON); 

系统版本的Temporal Table是足以保存历史更改数据同不通常间能够简单的指准时间解析的客商表。 那些Temporal Table就是系统版本的Temporal Table因为每行的有效期由系统托管的。

其意气风发效应看上去疑似不常表,可是实际上是系统一保险障的多个历史记录表。(在有个别程度上边比起大家手动维护的历史表应该有益于了好几的)

建好后,在SSMS中是这么些样子的:

图片 1

是因为没有一点点名历史表的称呼,历史表自动被命名称叫MSSQL_TemporalHistoryFor_前缀再加上原始表的objectID.

去除表时必需先实践关闭表的系统版本开关,不然会报错:

图片 2

ALTER TABLE MyTable SET (SYSTEM_VERSIONING = OFF)

关门之后,在SSMS中都成了普通表。

图片 3

删去之后以给定历史表的名字重新建设构造(这里历史表的名字指按期必需钦赐schema,不然会报错卡塔 尔(阿拉伯语:قطر‎:

CREATE TABLE MyTable
(
 Id BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY
,MyCode CHAR(5)
,MyName NVARCHAR(200)
,RecordStartTime DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL
,RecordEndTime DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL
,PERIOD FOR SYSTEM_TIME(RecordStartTime,RecordEndTime)
) 
WITH(SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.MyTableHistory)); 

 插入数据:

INSERT INTO [MyTable]([MyCode],[MyName])VALUES('SH001','万剑齐发'),('SH002','ajiangg');
SELECT * FROM [MyTable];

图片 4

改革/删除数据后的结果:

UPDATE [MyTable] SET MyCode = 'SH003' WHERE ID = 1;
DELETE FROM [MyTable] WHERE ID = 2;
SELECT * FROM [MyTable];
SELECT * FROM [MyTableHistory];

图片 5

先关闭SYSTEM_VE猎豹CS6SIONING,订正历史表名,并再次张开SYSTEM_VE奥迪Q5SIONING,即成功了历史表的存档(当然,那样归档的话,归档的那部分数量也就遗失了使用FOSportageSYSTEM_TIME语法查询的力量了):

ALTER TABLE MyTable SET (SYSTEM_VERSIONING = OFF);
EXEC sp_rename 'MyTableHistory', 'MyTableHistory_20170303';
ALTER TABLE MyTable SET (SYSTEM_VERSIONING = ON(HISTORY_TABLE = dbo.MyTableHistory));

图片 6

归根到底历史数据:

  SELECT * FROM [MyTable];
  SELECT * FROM [MyTableHistory];
  SELECT * FROM [MyTable]
  FOR SYSTEM_TIME    
        BETWEEN '2017-03-03 15:43:57.7006650' AND '9999-12-31 23:59:59.9999999' 

图片 7

百川归海历史数据查询2(数据存在开头时间低于等于2017-03-03 15:43:57.7006650,且终止时间大于那些时刻的Id为2的数额卡塔 尔(阿拉伯语:قطر‎:

图片 8

 

至于历史数据的追溯的新标准写法FOEscort SYSTEM_TIME ****,支持以下两种语法:

表达式 符合条件的行 说明
AS OF<date_time> SysStartTime <= date_time AND SysEndTime > date_time 返回一个表,其行中包含过去指定时间点的实际(当前)值。 在内部,将在临时表及其历史记录表之间进行联合,然后筛选结果以返回在 <date_time> 参数指定的时间点有效的行中的值。 如果 system_start_time_column_name 值小于或等于 <date_time> 参数值,且 system_end_time_column_name 值大于 <date_time> 参数值,则此行的值被视为有效。
FROM<start_date_time>TO<end_date_time> SysStartTime < end_date_time AND SysEndTime > start_date_time 返回一个表,其中包含在指定的时间范围内保持活动状态的所有行版本的值,不管这些版本是在 FROM 自变量的 <start_date_time> 参数之前开始活动,还是在 TO 自变量的 <end_date_time> 参数值之后停止活动。 在内部,将在临时表及其历史记录表之间进行联合,然后筛选结果,以返回在指定时间范围内任意时间保持活动状态的所有行版本的值。 正好在 FROM 终结点定义的下限时间激活的行将包括在内,正好在 TO 终结点定义的上限时间激活的记录将被排除。
BETWEEN<start_date_time>AND<end_date_time> SysStartTime <= end_date_time AND SysEndTime > start_date_time 与上面的 FOR SYSTEM_TIME FROM <start_date_time>TO <end_date_time> 描述相同,不过,返回的行表包括在 <end_date_time> 终结点定义的上限时间激活的行。
CONTAINED IN (<start_date_time> , <end_date_time>) SysStartTime >= start_date_time AND SysEndTime <= end_date_time 返回一个表,其中包含在 CONTAINED IN 参数的两个日期时间值定义的时间范围内打开和关闭的所有行版本的值。 正好在下限时间激活的记录,或者在上限时间停止活动的行将包括在内。
ALL 所有行 返回属于当前表和历史记录表的行的联合。

制造表时,关于GENERATED ALWAYS AS ROW START/END列还有个HIDDEN慎选,那在意气风发部分自个儿觉着的不标准SQL写法中会有个别差别(举个例子Insert时不点名插入的列,查询数据时采取select *等)

另外,不打开SYSTEM_VE传祺SIONING按钮的普通表,也能让GENERATED ALWAYS AS ROW START的标记列自动更新更新时间。(比方如下脚本中开创的表,UpdateTime列会被系统自动更新为末段贰回校正的光阴)

CREATE TABLE MyTable
(
 Id BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY
,MyCode CHAR(5)
,MyName NVARCHAR(200)
,UpdateTime DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL
,RecordEndTime DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL
,PERIOD FOR SYSTEM_TIME(RecordStartTime,RecordEndTime)
);

好了,关于System-Versioned Temporal Table,就介绍到那啦。

本文链接:

参照链接:

各个Temporal Table有2个展现定义的列,类型是datetime2。这么些用来表示保质期。那几个列用来标志这一个行是或不是在里面内可用。

简短间接的说,它的分界面看起来是那样的(就像是T1同样) 创建了后头,就能够在底下有三个T1History的表中表来记录。

除却下边包车型客车period列,l临时表也包蕴了引用到别的表,系统利用这几个表来保存行改革删除前的行版本。那个附加表能够认为是history表,主表包涵了脚下的行版本为当下表。在Temporal Table成立的时候能够钦定多个history表恐怕让系统创造叁个私下认可的history表。

图片 9

 

 然后大家来试下怎么去玩那些作用。首先,确认你的Sql Server 版本是2014。然后大家由此叁个这么的语句来创立表

有时表的劳作规律

Use Test
go

create table T1(ID int identity primary key,
    COl1 nvarchar(50),
    TimeFrom datetime2 generated always as row start,
    TimeTo datetime2 generated always as row end,
    period for system_time(TimeFrom,TimeTo)) with (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.T1History));

系统版本的表是有风流洒脱对表,当前表和历史表。那么些表都包涵2个附加的datetime2字段用来定义每一个行的可用期限:

 

  • 按期初阶列:系统把行的开始时间记下在这里个列上,称为SysStartTime
  • 按期结束列:系统把行的终结时间记下在这里个列上,称为SysEndTime

这里自个儿就轻便的创制一个唯有自增主键和二个列的表。创立System_Versioning 的表。必须有2个注脚为datetime2 的时间字段才行,因为须求用那2个字段来记录数据的爆发轨迹。

日前表包涵了每种行的眼下值。历史表富含每一个行的事先的只,starttime,endtime代表行的可用期限。

例如此处我是选拔叁个TimeFrom 的字段表示数据的功能初叶时间,而TimeTo表示那行数据的失灵时间(举个例子说数据被退换,被去除,那么TimeTo就能够记录着校正,删除的光阴)

图片 10

上面大家实行测验,先做测验样例,然后再作证

以下是贰个例证:

Step 1:新扩充加少

CREATE TABLE dbo.Employee  

insert into T1 (Col1) 
    values ('1111'),('2222'),('3333')

select * from T1
select * from T1History

ID          COl1                                               TimeFrom                    TimeTo
----------- -------------------------------------------------- --------------------------- ---------------------------
1           1111                                               2016-10-07 07:28:30.3598532 9999-12-31 23:59:59.9999999
2           2222                                               2016-10-07 07:28:30.3598532 9999-12-31 23:59:59.9999999
3           3333                                               2016-10-07 07:28:30.3598532 9999-12-31 23:59:59.9999999

(3 行受影响)

ID          COl1                                               TimeFrom                    TimeTo
----------- -------------------------------------------------- --------------------------- ---------------------------

(   

 

  [EmployeeID] int NOT NULL PRIMARY KEY CLUSTERED  

新扩大的时候,数据都以新型的本子,所以在历史表里面并空中楼阁记录(注解生龙活虎(Wissu卡塔尔国点,TimeFrom 和TimeTo 那2个字段将由系统调整维护,并无需手工插入,纵然显示写入那一个字段,将抛出荒谬。系统敬服那2个字段,选择的时间将运用UTC格式的时刻,对于大家国内,正是小时-8的操作)

  , [Name] nvarchar(100) NOT NULL 

 

  , [Position] varchar(100) NOT NULL  

Step 2:改良数据

  , [Department] varchar(100) NOT NULL 

update T1 set Col1 = Col1 'New' where ID = 2

select * from T1
select * from T1History

ID          COl1                                               TimeFrom                    TimeTo
----------- -------------------------------------------------- --------------------------- ---------------------------
1           1111                                               2016-10-07 07:28:30.3598532 9999-12-31 23:59:59.9999999
2           2222New                                            2016-10-07 07:30:38.0561513 9999-12-31 23:59:59.9999999
3           3333                                               2016-10-07 07:28:30.3598532 9999-12-31 23:59:59.9999999

ID          COl1                                               TimeFrom                    TimeTo
----------- -------------------------------------------------- --------------------------- ---------------------------
2           2222                                               2016-10-07 07:28:30.3598532 2016-10-07 07:30:38.0561513

  , [Address] nvarchar(1024) NOT NULL 

 

  , [AnnualSalary] decimal (10,2) NOT NULL 

改过数据的时候。就要历史表里面写入一条历史记录,并将TimeTo设置为当下涂改的UTC时间,在主表将保存数据的新式版本。

  , [ValidFrom] datetime2 (2) GENERATED ALWAYS AS ROW START 

 

  , [ValidTo] datetime2 (2) GENERATED ALWAYS AS ROW END 

Step 3:删除

  , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo) 

delete from T1 where ID = 3
select * from T1
select * from T1History


ID          COl1                                               TimeFrom                    TimeTo
----------- -------------------------------------------------- --------------------------- ---------------------------
1           1111                                               2016-10-07 07:28:30.3598532 9999-12-31 23:59:59.9999999
2           2222New                                            2016-10-07 07:30:38.0561513 9999-12-31 23:59:59.9999999

ID          COl1                                               TimeFrom                    TimeTo
----------- -------------------------------------------------- --------------------------- ---------------------------
2           2222                                               2016-10-07 07:28:30.3598532 2016-10-07 07:30:38.0561513
3           3333                                               2016-10-07 07:28:30.3598532 2016-10-07 07:32:04.3640717

 )   

 

 WITH

除去数据的时候和改过的体制大概,就是主表删除了行记录,可是历史表保留了豆蔻年华份删除的动作。

(SYSTEM_VERSIONING

ON

 

(HISTORY_TABLE

dbo.EmployeeHistory));

能够去除括号中的HISTOTiguanY_TABLE系统会自行创设history表。

 

INSERT:对于三个insert,系统会安装SysStartTime列为当前专业的起来时间,SysEndTime为最大的值9999-12-31

UPDATE:对于update,系统会报以前的行保存到历史表何况安装SysEndTime为当前业务的运维时间。行被关门,这几个期限正是那几个行的可用期限。那个行在如今表上的值被涂改,那么SysStartTime被设置为日前政工的上虎时间。SysEndTime被安装为最大日子。

DELETE:对于删除,系统把后面包车型客车行保存到history表,何况安装SysEndtime为业务的起来时间。标记行关闭,期限记录代表行的可用期限。当前表中央银行被删除。当前的查询不会被查到日前进。只有带时间的查询,也许直接询问历史表工夫查到那几个行。

MEHavalGE:对于ME奥迪Q3GE涉及到3个操作INSERT,UPDATE,DELETE,根据操作的例外做差异的记录。

 

偶尔数据查询

可以应用select from的for system_time子句来询问当前表和历史表的数额。

 图片 11

以下是查询的例子:

SELECT * FROM Employee  

    FOR SYSTEM_TIME   

        BETWEEN '2014-01-01 00:00:00.0000000' AND '2015-01-01 00:00:00.0000000'  

            WHERE

简轻巧单的测量检验就能够成功这里。上边还会有多少个测量检验注脚

EmployeeID

1000 ORDER BY ValidFrom;

注意:

FOR SYSTEM_TIME会过滤掉SysStartTime=SysEndTime的数据。这么些行在同叁个政工里面操作了同风度翩翩行儿发生。只可以通过查询历史表技巧回到

 

带你熟悉SQLServer2016中的System。关于SYSTEM_TIME过滤

表达式 符合条件的行 Description
AS OF<date_time> SysStartTime <= date_time AND SysEndTime > date_time 返回一个表,其行中包含过去指定时间点的实际(当前)值。 在内部,临时表及其历史记录表之间将进行联合,然后筛选结果以返回在 <date_time> 参数指定的时间点有效的行中的值。 如果 system_start_time_column_name 值小于或等于 <date_time> 参数值,并且 system_end_time_column_name 值大于 <date_time> 参数值,则此行的值视为有效。
FROM<start_date_time>TO<end_date_time> SysStartTime < end_date_time AND SysEndTime > start_date_time 返回一个表,其中包含在指定的时间范围内保持活动状态的所有行版本的值,不管这些版本是在 FROM 自变量的 <start_date_time> 参数之前开始活动,还是在 TO 自变量的 <end_date_time> 参数值之后停止活动。 在内部,将在临时表及其历史记录表之间进行联合,然后筛选结果,以返回在指定时间范围内任意时间保持活动状态的所有行版本的值。 正好在 FROM 终结点定义的下限时间停止活动的行将被排除,正好在 TO 终结点定义的上限时间开始活动的记录也将被排除。
BETWEEN<start_date_time>AND<end_date_time> SysStartTime <= end_date_time AND SysEndTime > start_date_time 与上面的 FOR SYSTEM_TIME FROM <start_date_time>TO<end_date_time> 描述相同,不过,返回的行表包括在 <end_date_time> 终结点定义的上限时间激活的行。
CONTAINED IN (<start_date_time> , <end_date_time>) SysStartTime >= start_date_time AND SysEndTime <= end_date_time 返回一个表,其中包含在 CONTAINED IN 参数的两个日期时间值定义的时间范围内打开和关闭的所有行版本的值。 正好在下限时间激活的记录,或者在上限时间停止活动的行将包括在内。
ALL 所有行 返回属于当前表和历史记录表的行的联合。

注意:

能够透过Hidden隐敝期限列,删除表须要先关闭系统版本 ALTER TABLE Employee SET (SYSTEM_VE福睿斯SIONING =off )之后手艺删除表

1 假设运用Merge,那么Merge做的操作将对应以上的增/删/改来维护版本

2 使用了经版本维护的表之后,不能应用truncate table 的操作,因为操作不帮助

3 drop 表的时候,不可能平素利用drop table 语句,必要先用 ALTE昂科拉 TABLE [dbo].[T1] SET ( SYSTEM_VE奥迪Q5SIONING = OFF ) 来把系统拥戴的本子去掉,然后再各自drop 掉当前表和历史表

4 小编是密集的╮(╯_╰)╭ ~请别的大神指引补充

5 多谢@wy123 的升迁,创立那体系型的表须求有主键才行~居然忘记了

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

本文由澳门新萄京官方网站发布于数据库网络,转载请注明出处:带你熟悉SQLServer2016中的System

关键词: