SqlServer 2016 新特性之Temporal Table(历史表)

 

先创建一个吧，sql出来的语法一大堆

 

-- =========================================
-- 创建系统版本的临时表模板
-- Create system-versioned temporal table template
-- 使用“为模板参数指定值”命令（Ctrl-Shift-M）填写下面的参数值。
-- Use the Specify Values for Template Parameters command (Ctrl-Shift-M) to fill in the parameter values below.
-- 此模板创建系统版本的临时表。
-- This template creates a system-versioned temporal table.
-- 有关系统版本的时态表的详细信息，请参阅MSDN文档：
-- For more details on system-versioned temporal tables please refer to MSDN documentation:
-- https://msdn.microsoft.com/en-IN/library/dn935015.aspx#Anchor_0
-- 要了解如何在应用程序中使用系统版本表，请查看“开始使用系统版本的临时表”：
-- To learn more how to use system-versioned tables in your applications, take a look at "Getting Started with System-Versioned Temporal Tables":
-- https://msdn.microsoft.com/en-US/library/mt604462.aspx
-- =========================================

USE <database, sysname, AdventureWorks>
GO

BEGIN
	--如果表是系统版本控制的，则必须首先将系统版本控制设置为关闭
    --If table is system-versioned, SYSTEM_VERSIONING must be set to OFF first 
    IF ((SELECT temporal_type FROM SYS.TABLES WHERE object_id = OBJECT_ID('<schema_name, sysname, dbo>.<temporal_table_name, sysname, sample_table>', 'U')) = 2)
    BEGIN
        ALTER TABLE [<schema_name, sysname, dbo>].[<temporal_table_name, sysname, sample_table>] SET (SYSTEM_VERSIONING = OFF)
    END
    DROP TABLE IF EXISTS [<schema_name, sysname, dbo>].[<temporal_table_name, sysname, sample_table>]
END
GO

--创建系统版本的临时表。它必须有主键和两个datetime2列，这是系统时间段定义的一部分
--Create system-versioned temporal table. It must have primary key and two datetime2 columns that are part of SYSTEM_TIME period definition
CREATE TABLE [<schema_name, sysname, dbo>].[<temporal_table_name, sysname, sample_table>]
(
    <columns_in_primary_key, , c1> <column1_datatype, , int> <column1_nullability,, NOT NULL>,
    <column2_name, sysname, c2> <column2_datatype, , char(10)> <column2_nullability,, NULL>,
    <column3_name, sysname, c3> <column3_datatype, , datetime> <column3_nullability,, NULL>,

	--系统时间定义的期间列和期间
    --Period columns and PERIOD FOR SYSTEM_TIME definition
    <period_start_column_name, sysname, SysStartTime> datetime2(7) GENERATED ALWAYS AS ROW START <period_start_column_hidden,,> NOT NULL ,
    <period_end_column_name, sysname, SysEndTime> datetime2(7) GENERATED ALWAYS AS ROW END <period_end_column_hidden,,> NOT NULL ,
    PERIOD FOR SYSTEM_TIME(<period_start_column_name, sysname, SysStartTime>,<period_end_column_name, sysname, SysEndTime>),

	--主键定义
    --Primary key definition
    CONSTRAINT <constraint_name, sysname, PK_sampletable> PRIMARY KEY (<columns_in_primary_key, , c1>)
)
WITH
(
	--将“系统版本控制”设置为“开”，并提供对历史记录表的引用。
    --Set SYSTEM_VERSIONING to ON and provide reference to HISTORY_TABLE. 
    SYSTEM_VERSIONING = ON 
    (
		--如果历史表不存在，将创建默认表。
        --If HISTORY_TABLE does not exists, default table will be created.
        HISTORY_TABLE = [<history_table_schema_name, sysname, dbo>].[<history_table_name, sysname, sample_table_history>],
		--指定是否在当前表和历史表之间执行数据一致性检查（默认为启用）
        --Specifies whether data consistency check will be performed across current and history tables (default is ON)
        DATA_CONSISTENCY_CHECK = <data_consistency_check,, ON>
    )
)
GO

然后自己改改

USE Mini_Test
GO

BEGIN
	--如果表是系统版本控制的，则必须首先将系统版本控制设置为关闭
    IF ((SELECT temporal_type FROM SYS.TABLES WHERE object_id = OBJECT_ID('Mini_Test.dbo.TemporalTest', 'U')) = 2)
    BEGIN
        ALTER TABLE Mini_Test.dbo.TemporalTest SET (SYSTEM_VERSIONING = OFF)
    END
    DROP TABLE IF EXISTS Mini_Test.dbo.TemporalTest
END
GO

--创建系统版本的临时表。它必须有主键和两个datetime2列，这是系统时间段定义的一部分
--Create system-versioned temporal table. It must have primary key and two datetime2 columns that are part of SYSTEM_TIME period definition
CREATE TABLE Mini_Test.dbo.TemporalTest
(
    ID int identity(1,1),
    UserName nvarchar(50),
	--系统时间定义的期间列和期间
    --Period columns and PERIOD FOR SYSTEM_TIME definition
    BeginTime datetime2(7) GENERATED ALWAYS AS ROW START hidden NOT NULL ,--period_start_column_hidden 期间开始列隐藏
    EndTime datetime2(7) GENERATED ALWAYS AS ROW END hidden NOT NULL ,--period_end_column_hidden 期间结束列隐藏
    PERIOD FOR SYSTEM_TIME(BeginTime,EndTime),

	--主键定义
    --Primary key definition
	PRIMARY KEY CLUSTERED (ID asc)
)
WITH
(
	--将“系统版本控制”设置为“开”，并提供对历史记录表的引用。
    --Set SYSTEM_VERSIONING to ON and provide reference to HISTORY_TABLE. 
    SYSTEM_VERSIONING = ON 
    (
		--如果历史表不存在，将创建默认表。
        --If HISTORY_TABLE does not exists, default table will be created.
        HISTORY_TABLE = dbo.TemporalTest_History,
		--指定是否在当前表和历史表之间执行数据一致性检查（默认为启用）
        --Specifies whether data consistency check will be performed across current and history tables (default is ON)
        DATA_CONSISTENCY_CHECK = ON
    )
)
GO

F5一下，完成，看看创建的历史表

右键看看

居然没有正常表的设计菜单，编辑下看看，嗯，由于前面隐藏的两个时间列，用*查询也是这样，也可以把这2个时间输出来也是可以查看到的

做点操作再看看

INSERT INTO [dbo].[TemporalTest]([UserName])VALUES('张三'),('李四'),('王五'),('赵六'),('麻子')
GO

update [dbo].[TemporalTest] Set UserName='张三先生' where ID=1
GO

Delete from [dbo].[TemporalTest] where ID=1
GO





--再看看结果数据

Select * from dbo.TemporalTest

Select * from dbo.TemporalTest_History

历史表中两个ID为1，而且第一条数据的结束时间和第二条数据的开始时间相同，EndTime是会根据变更的时候发生变化的

再看看完整的

Select ID,UserName,BeginTime,EndTime from dbo.TemporalTest for system_time all order by ID

直接查询历史表，数据拿来做变更集合还是不错。特别是价格，就可以直接拿来做个折线图了，比较有意思。

TRUNCATE Table TemporalTest
TRUNCATE Table TemporalTest_History

截断表是不行的，在表“Mini_Test.dbo.TemporalTest”上的截断操作失败，因为此操作不是经系统版本控制的表中支持的操作。

delete from TemporalTest_History

删除历史表也是不行的，无法在临时历史记录表“Mini_Test.dbo.TemporalTest_History”中删除行。

 

虽然是在临时表选项里添加出来的，重启服务再查询历史数据依然存在。

 

具体的细节看看 Jerry_Chen 的文章