sql 时态表的意义_SQL Server中的时态表

sql 时态表的意义

Temporal tables have been a feature of SQL Server since version 2016. SQL Server professionals have historically had several options to track data change events. The evolution of the data tracking mechanism started with Change tracking (CT), Change Data Capture (CDC) and now Temporal Tables.

自2016年以来，临时表一直是SQL Server的功能。SQLServer专业人员从历史上就拥有多个跟踪数据更改事件的选项。 数据跟踪机制的发展始于变更跟踪（CT），变更数据捕获（CDC）和现在的临时表。

介绍 (Introduction)

In my experience, I have seen that a few projects still use custom data tracking solutions. And in a few other enterprises, third-party solutions are in place to manage the same. In this article, we’ll discuss temporal tables in SQL Server 2016.

根据我的经验，我看到一些项目仍在使用自定义数据跟踪解决方案。 在其他一些企业中，也有第三方解决方案可以对其进行管理。 在本文中，我们将讨论SQL Server 2016中的时态表。

GDPR（通用数据保护法规） (GDPR (General Data Protection Regulation))

As we all know, the General Data Protection Regulation (GDPR), a new data privacy regulatory act, introduces significant changes in the way organizations collect or use data. Enterprises must adhere to the data privacy and protection act.

众所周知，通用数据保护法规（GDPR）是一项新的数据隐私法规，对组织收集或使用数据的方式进行了重大更改。 企业必须遵守数据隐私和保护法。

• Note:注意：Using production data for testing in a post GDPR world使用生产数据在GDPR后世界进行测试

To comply with GDPR requirements, organizations strive towards working with various tools and techniques.

为了符合GDPR要求，组织努力使用各种工具和技术。

SQL Server offers various native tools and techniques and is capable of fulfilling most of the security requirements for being GDPR complaint.

SQL Server提供了各种本机工具和技术，并且能够满足GDPR投诉的大多数安全要求。

• Note注意SQL Server data security feature RLS (Row-Level Security) and GDPRSQL Server数据安全功能RLS（行级安全性）和GDPR

入门 (Getting Started)

Let’s deep-dive into the concepts of temporal table and see what can be derived out of this feature.

让我们深入了解时态表的概念，看看可以从此功能中得出什么。

什么是时间表？ (What is Temporal Table?)

Temporal tables, also known as system-versioned tables, provide us with new functionality to track data changes. It allows SQL Server to maintain and manage the history of the data in the table automatically. This feature provides a full history of every change made to the data.

时态表，也称为系统版本表，为我们提供了跟踪数据更改的新功能。 它允许SQL Server自动维护和管理表中数据的历史记录。 此功能提供对数据所做的所有更改的完整历史记录。

It was first introduced in ANSI (American National Standards Institute) SQL 2011 standard. Now, it’s available in SQL Server 2016 and later versions.

它是在ANSI（美国国家标准学会） SQL 2011标准中首次引入的。 现在，它在SQL Server 2016和更高版本中可用。

Temporal tables are considered as one of the critical features used to audit SQL Server data.

时态表被视为用于审核SQL Server数据的关键功能之一。

The two period columns which are of datetime2 data type, SysStartTime and SysEndTime define the current and historical data for every row of the table. It defines the validity of the data. The DateTime range has a value that is appropriate during that timeframe. By querying the table, we can easily see the transitioning of the data to its various states within the specific date-time intervals.

datetime2数据类型的两个期间列SysStartTime和SysEndTime定义了表的每一行的当前和历史数据。 它定义了数据的有效性。 DateTime范围的值在该时间段内合适。 通过查询该表，我们可以轻松地看到在特定日期时间间隔内数据向其各种状态的过渡。

为什么使用临时表？ (Why Temporal tables?)

The following are some usage scenarios of Temporal tables

以下是临时表的一些使用场景

1. Auditing
   稽核
2. Rebuilding the data in case of inadvertent changes
   在意外更改的情况下重建数据
3. Projecting and reporting for historical trend analysis
   预测和报告历史趋势分析
4. Protecting the data in case of accidental data loss
   在意外丢失数据的情况下保护数据

演示版 (Demo)

Let’s jump in to get started with the demonstration.

让我们开始演示。

The following example creates an employee temporal table created with a few sample rows.

下面的示例创建一个雇员临时表，该临时表创建了一些示例行。

 CREATE TABLE dbo.employee(
  EMPNO   INT,
  ENAME    VARCHAR(10),
  JOB      VARCHAR(9),
  MGR      INT,
  HIREDATE DATE,
  SAL      NUMERIC(7,2),
  COMM     NUMERIC(7,2),
  DEPTNO   INT,
     CONSTRAINT EMP_PK PRIMARY KEY (EMPNO),
   SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL
  ,SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL
  ,PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)) WITH (SYSTEM_VERSIONING = ON);

By default, the SQL Server will automatically generate a name for the history table in the following format

默认情况下，SQL Server将自动以以下格式为历史记录表生成一个名称

dbo.MSSQL_TemporalHistoryFor_<ObjectID>.

dbo.MSSQL_TemporalHistoryFor_ <ObjectID> 。

If you browse the table, we can see the absence of “Delete” option for Temporal Tables properties.

如果您浏览该表，我们会看到Temporal Tables属性缺少“删除”选项。

To get the delete option, first, turn off the system_versioning setting using the alter table statement. After that, you’ll notice that the tables, temporal table and history table become a regular table.

要获得删除选项，首先，使用alter table语句关闭system_versioning设置。 之后，您会注意到表，时态表和历史记录表成为常规表。

You can also use the SSMS generate script option to drop the temporal table.

您还可以使用SSMS生成脚本选项删除时态表。

ALTER TABLE [dbo].employee SET ( SYSTEM_VERSIONING = OFF )
GO

Now, the tables can be dropped either by using T-SQL or SSMS.

现在，可以使用T-SQL或SSMS删除表。

DROP TABLE [dbo].employee
GO
DROP TABLE [dbo].MSSQL_TemporalHistoryFor_1061578820

如何为时间历史表创建自定义名称 (How to Create a Custom Name for Temporal History table)

In this section, we’ll see how to create a custom temporal table along with a consistency checker option. In this case, the employeeHistory is the history table and DATA_CONSISTENCY_CHECK=ON is used with the WITH clause of the create table statement.

在本节中，我们将看到如何创建自定义时间表以及一致性检查器选项。 在这种情况下， employeeHistory是历史记录表，并且DATA_CONSISTENCY_CHECK = ON与create table语句的WITH子句一起使用。

The create table statement is discussed in detail here.

在CREATE TABLE语句中详细讨论这里 。

CREATE TABLE dbo.employee(
  EMPNO   INT,
  ENAME    VARCHAR(10),
  JOB      VARCHAR(9),
  MGR      INT,
  HIREDATE DATE,
  SAL      NUMERIC(7,2),
  COMM     NUMERIC(7,2),
  DEPTNO   INT,
     CONSTRAINT EMP_PK PRIMARY KEY (EMPNO),
   SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL
  ,SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL
  ,PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory, DATA_CONSISTENCY_CHECK = ON));

In the following output, we can see that the custom history table dbo.EmployeeHistory is created as part of the Create table statement.

在以下输出中，我们可以看到定制历史记录表dbo.EmployeeHistory是作为Create table语句的一部分创建的。

Insert a few records into the employee table

在员工表中插入一些记录

INSERT INTO employee (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)values
 (7369, 'SMITH', 'CLERK', 7902, '02-MAR-1970', 8000, NULL, 20),
(7499, 'ALLEN', 'SALESMAN', 7698, '20-MAR-1971', 1600, 3000, 30),
(7521, 'WARD', 'SALESMAN', 7698, '07-FEB-1983', 1250, 5000, 30),
(7566, 'JONES', 'MANAGER', 7839, '02-JUN-1961', 2975, 50000, 20),
(7654, 'MARTIN', 'SALESMAN', 7698, '28-FEB-1971', 1250, 14000, 30),
(7698, 'BLAKE', 'MANAGER', 7839, '01-JAN-1988', 2850, 12000, 30),
(7782, 'CLARK', 'MANAGER', 7839, '09-APR-1971', 2450, 13000, 10),
(7788, 'SCOTT', 'ANALYST', 7566, '09-DEC-1982', 3000, 1200, 20),
(7839, 'KING', 'PRESIDENT', NULL, '17-JUL-1971', 5000, 1456, 10),
(7844, 'TURNER', 'SALESMAN', 7698, '08-AUG-1971', 1500, 0, 30),
(7876, 'ADAMS', 'CLERK', 7788, '12-MAR-1973', 1100, 0, 20),
(7900, 'JAMES', 'CLERK', 7698, '03-NOV-1971', 950, 0, 30),
(7902, 'FORD', 'ANALYST', 7566, '04-MAR-1961', 3000, 0, 20),
(7934, 'MILLER', 'CLERK', 7782, '21-JAN-1972', 1300, 0, 10)

Query the employee table

查询员工表

select * from employee;

Now, If you want to hide the SysStartTime and SysEndTime columns from viewing, add a hidden keyword in the create table statement so that the column does not show up in a select query.

现在，如果要隐藏SysStartTime和SysEndTime列，请在create table语句中添加一个hidden关键字，以使该列不会显示在select查询中。

CREATE TABLE dbo.employee(
  EMPNO   INT,
  ENAME    VARCHAR(10),
  JOB      VARCHAR(9),
  MGR      INT,
  HIREDATE DATE,
  SAL      NUMERIC(7,2),
  COMM     NUMERIC(7,2),
  DEPTNO   INT,
     CONSTRAINT EMP_PK PRIMARY KEY (EMPNO),
   SysStartTime datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL
  ,SysEndTime datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL
  ,PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory, DATA_CONSISTENCY_CHECK = ON));

Let us perform SQL Update DML operations to understand the usage of SYSTEM_TIME column.

让我们执行SQL Update DML操作以了解SYSTEM_TIME列的用法。

For example, SMITH’s salary has got an increment of 2000. The update SQL is as follows.

例如，SMITH的工资增加了2000。更新SQL如下。

UPDATE EMPLOYEE
SET SAL=SAL+2000
WHERE EMPNO=7369
 
SELECT * FROM EMPLOYEE WHERE EMPNO=7369

After the update, we can see that the Salary column is updated. The column SysEndTime is a reference pointer to indicate the active record of the table. It’s really easy to audit the data flow by referring the SysStartTime and SysEndTime.

更新之后，我们可以看到Salary列已更新。 SysEndTime列是指示表的活动记录的参考指针。 通过引用SysStartTime和SysEndTime来审核数据流确实非常容易。

SELECT *, SysStartTime,SysEndTime FROM EMPLOYEE WHERE EMPNO=7369
 
SELECT * FROM EmployeeHistory WHERE EMPNO=7369
 
SELECT *, SysStartTime,SysEndTime FROM EMPLOYEE 
FOR SYSTEM_TIME ALL
order by empno, SysEndTime

The SysEndTime ‘9999-12-31 23:59:59.9999999’ always refers to the active record set. The dbo.emplyeehistory table has the previous reference pointers of the modified data. In this case, the column, salary, is seen as updated on “2018-01-25 20:31:40.9852938”. The unmodified record made an entry into the history table with the updated timestamp. The updated timestamp becomes the SysStartTime column of the modified record.

SysEndTime'9999-12-31 23：59：59.9999999'始终引用活动记录集。 dbo.emplyeehistory表具有已修改数据的先前引用指针。 在这种情况下，“ 薪水 ”列被视为在“ 2018-01-25 20：31：40.9852938”上更新。 未修改的记录使用更新的时间戳记进入历史记录表。 更新的时间戳将成为修改后的记录的SysStartTime列。

The entire update process can be easily traced by referring the Sys columns that is arranged in a Zig-Zag manner.

通过引用以Zig-Zag方式排列的Sys列，可以轻松地跟踪整个更新过程。

Now, Let us perform the SQL Delete operation to delete the employee record 7369 and see what happens

现在，让我们执行SQL Delete操作以删除员工记录7369，看看会发生什么

DELETE from EMPLOYEE WHERE EMPNO=7369
 
SELECT * FROM Employee WHERE EMPNO=7369
 
SELECT * FROM EmployeeHistory WHERE EMPNO=7369
 
SELECT *, SysStartTime,SysEndTime FROM EMPLOYEE 
FOR SYSTEM_TIME ALL
order by empno, SysEndTime

The output is an empty result set. The SysEndTime is updated with the timestamp of DELETE statement. This indicates that the record was deleted from the table.

输出为空结果集。 SysEndTime用DELETE语句的时间戳更新。 这表明该记录已从表中删除。

>

>

The FOR SYSTEM_TIME clause has many variations and options. It is further classified into four temporal sub-clauses. This provides a way to query the data across current and history tables.

FOR SYSTEM_TIME子句具有许多变体和选项。 它进一步分为四个时间子条款。 这提供了一种查询当前表和历史表中的数据的方法。

1. AS OF <datetime>
   截至<datetime>
2. FROM <startdatetime> TO <enddatetime>
   从<开始日期时间>到<结束日期时间>
3. BETWEEN <startdatetime> AND <enddatetime>
   在<开始日期时间>和<结束日期时间>之间
4. CONTAINED IN (<startdatetime> , <enddatetime>)
   包含在（<开始日期时间>，<结束日期时间>）
5. ALL
   所有

The AS OF clause is used when there is a need to rebuild the original state of the data and need to know the state it was at any specific time in the past. This is possible by specifying the date time as its input.

当需要重建数据的原始状态并且需要知道过去任何特定时间的状态时，可以使用AS OF子句。 通过指定日期时间作为输入是可能的。

SELECT *, [SysStartTime],[SysEndTime]   
FROM [dbo].EMPLOYEE  
FOR SYSTEM_TIME AS OF '2018-01-25 20:17:28.9598446' 
WHERE EMPNO=7369

Here are some other sub-clause temporal options that are useful for data audit.

以下是一些其他子时间选项，它们对于数据审核很有用。

SELECT * ,SysStartTime,SysEndTime FROM EMPLOYEE
FOR SYSTEM_TIME BETWEEN '2018-01-25 20:17:28.9598446' AND '2018-01-25 20:58:13.7242195';
 
SELECT * ,SysStartTime,SysEndTime FROM EMPLOYEE
FOR SYSTEM_TIME CONTAINED IN('2018-01-25','2018-01-26')
 
SELECT *, SysStartTime,SysEndTime FROM EMPLOYEE 
FOR SYSTEM_TIME ALL
order by empno, SysEndTime 

摘要 (Summary)

In this article, we see the step-by-step procedures to setup temporal tables in SQL Server. Temporal Tables are generally useful in scenarios that require tracking the history of data changes. In some cases, it can be used to reconstruct the data. Its steps are pretty simple and straightforward. It doesn’t require writing triggers or stored procedures or any application code change. Data retrieval is made very simple using the SYSTEM_TIME clause along with the associated sub-clause.

在本文中，我们介绍了在SQL Server中设置时态表的分步过程。 临时表通常在需要跟踪数据更改历史的方案中很有用。 在某些情况下，它可以用于重建数据。 它的步骤非常简单明了。 它不需要编写触发器或存储过程，也不需要更改任何应用程序代码。 使用SYSTEM_TIME子句以及相关的子句，数据检索变得非常简单。

The history table can grow big. It depends on the number of transactions on the temporal table so, space is the biggest tradeoff. You can consider purging the data from the history table.

历史记录表会变得很大。 它取决于时间表上的事务数，因此，空间是最大的折衷方案。 您可以考虑从历史记录表中清除数据。

翻译自: https://www.sqlshack.com/temporal-tables-in-sql-server/

sql 时态表的意义