您的位置:新葡亰496net > 网络数据库 > 带你熟练SQLServer二〇一六中的System

带你熟练SQLServer二〇一六中的System

发布时间:2019-10-30 00:49编辑:网络数据库浏览(83)

    带你熟练SQLServer二〇一六中的System。什么是 System-Versioned Temporal Table?

    System-Versioned Temporal Table,暂且容我管它叫版本由系统控制的临时表,它是 SQL Server 2016 中的新型用户表,用于保留完整的数据更改历史记录。 它之所以称为版本由系统控制的临时表,是因为每一行的有效期由数据库引擎管理。

    每个临时表有两个显式定义的列,其中每个列都有一个 datetime2 数据类型。每当数据修改后,系统将以独占方式使用这些列来记录每行的有效期。

    除了这些列以外,该表还包含对使用镜像架构的另一个历史表的引用。 每当更新或删除了临时表中的某行后,系统将使用历史表来自动存储该行的先前版本。

    这个临时表的推出,在一定程度上完全可以替代CDC,可用于ETL,追溯数据,审计等。之前CDC能用到的地方这个会更好用且更易维护。另外,以前约定让程序员更新数据时必须更新UpdateTime之类的约定也完全可以交由系统自己控制了。

    带你熟练SQLServer二〇一六中的System。什么是系统版本的Temporal Table

    放假之前老大跟我提起了一下2016有个有趣的功能叫 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中是这个样子的:

    新葡亰496net 1

    由于没有指定历史表的名称,历史表自动被命名为MSSQL_TemporalHistoryFor_前缀再加上原始表的objectID.

    删除表时必须先执行关闭表的系统版本开关,否则会报错:

    新葡亰496net 2

    ALTER TABLE MyTable SET (SYSTEM_VERSIONING = OFF)
    

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

    新葡亰496net 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];
    

    新葡亰496net 4

    更新/删除数据后的结果:

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

    新葡亰496net 5

    先关闭SYSTEM_VERSIONING,修改历史表名,并再次打开SYSTEM_VERSIONING,即完成了历史表的归档(当然,这样归档的话,归档的那部分数据也就失去了使用FOR SYSTEM_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));
    

    新葡亰496net 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' 
    

    新葡亰496net 7

    追溯历史数据查询2(数据存在开始时间小于等于2017-03-03 15:43:57.7006650,且终止时间大于这个时间的Id为2的数据):

    新葡亰496net 8

     

    关于历史数据的追溯的新条件写法FOR 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_VERSIONING开关的普通表,也能让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表。

    新葡亰496net 9

     

     然后我们来试下怎么去玩这个功能。首先,确认你的Sql Server 版本是2016。然后我们通过一个这样的语句来创建表

    临时表的工作原理

    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表示行的可用期限。

    新葡亰496net,比如这里我是使用一个TimeFrom 的字段表示数据的作用开始时间,而TimeTo表示这行数据的失效时间(比方说数据被修改,被删除,那么TimeTo就会记录着修改,删除的时间)

    新葡亰496net 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  

    新增的时候,数据都是最新的版本,所以在历史表里面并不存在记录(注明一点,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));

    可以删除括号中的HISTORY_TABLE系统会自动创建history表。

     

    INSERT:对于一个insert,系统会设置SysStartTime列为当前事务的开始时间,SysEndTime为最大的值9999-12-31

    UPDATE:对于update,系统会报之前的行保存到历史表并且设置SysEndTime为当前事务的启动时间。行被关闭,这个期限就是这个行的可用期限。这个行在当前表上的值被修改,那么SysStartTime被设置为当前事务的开始时间。SysEndTime被设置为最大时间。

    DELETE:对于删除,系统把之前的行保存到history表,并且设置SysEndtime为事务的开始时间。标记行关闭,期限记录表示行的可用期限。当前表中行被删除。当前的查询不会被查到当前行。只有带时间的查询,或者直接查询历史表才能查到这个行。

    MERGE:对于MERGE涉及到3个操作INSERT,UPDATE,DELETE,根据操作的不同做不同的记录。

     

    临时数据查询

    可以使用select from的for system_time子句来查询当前表和历史表的数据。

     新葡亰496net 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的数据。这些行在同一个事务里面操作了同一行儿产生。只能通过查询历史表才能返回

     

    关于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_VERSIONING =off )之后才能删除表

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

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

    3 drop 表的时候,不能直接使用drop table 语句,需要先用 ALTER TABLE [dbo].[T1] SET ( SYSTEM_VERSIONING = OFF ) 来把系统维护的版本去掉,然后再分别drop 掉当前表和历史表

    4 我是凑数的╮(╯_╰)╭ ~请其他大神指导补充

    5 谢谢@wy123 的提醒,创建这种类型的表需要有主键才行~居然忘记了

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    本文由新葡亰496net发布于网络数据库,转载请注明出处:带你熟练SQLServer二〇一六中的System

    关键词:

上一篇:新葡亰496net:从0最初搭建SQL

下一篇:没有了