sql 时态表的意义_SQL Server 2016中的时态表的概念和基础

最新推荐文章于 2024-01-15 17:42:46 发布

culuo4781

最新推荐文章于 2024-01-15 17:42:46 发布

阅读量353

点赞数

文章标签：数据库 python mysql java 大数据

原文链接：https://www.sqlshack.com/concept-basics-temporal-tables-sql-server-2016/

版权

sql 时态表的意义

In this article I’ll cover all aspects of a new SQL Server 2016 feature, Temporal Tables (System-Versioned), including:

在本文中，我将介绍新SQL Server 2016功能“时态表（系统版本）”的所有方面，包括：

Introduction
介绍
What is a temporal table?
什么是时间表？
Why Temporal table?
为什么选择时态表？
How does temporal table work?
时态表如何工作？
Consideration and limitation
考虑和限制
Temporal tables vs CDC
时间表与CDC
Creation and configuration
创建和配置
Clean up and removal
清理并清除
References
参考资料

介绍 (Introduction )

AS we know, Microsoft released SQL Server 2016 RTM version (13.00.1601.5) and in November of 2016 updated it by the latest CU (Security Bulletin MS16-136 (CU) KB #3194717) (13.0.2186.0) you can check this update from here.

众所周知，Microsoft已发布SQL Server 2016 RTM版本（ 13.00.1601.5 ），并于2016年11月通过最新的CU（安全公告MS16-136（CU）KB＃3194717）（ 13.0.2186.0 ）对其进行了更新，您可以检查此更新从这里。

什么是时态表？ (What are temporal tables?)

Temporal (system-versioned) tables store and save data changes in a table so that you can return back to it in any time. This feature is very helpful, in day to day work, for developers because many times they asked about old backups to check on some data issues or changes. With this feature there is no need now since everything will be hosted in this system-version tables themselves. With temporal table the value of each record in any time can be determined easily rather than just the current value of each record.

临时（系统版本化）表将数据更改存储并保存在表中，以便您可以随时返回到表中。对于开发人员而言，此功能在日常工作中非常有帮助，因为他们很多次询问旧备份来检查某些数据问题或更改。使用此功能，现在不需要了，因为所有内容都将托管在此系统版本表中。使用时间表，可以轻松确定任何时间的每个记录的值，而不仅仅是每个记录的当前值。

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

Data auditing – to check the values of a specific column
数据审核–检查特定列的值
Track slowing changing dimensions –to be able to track the slowing changing values
跟踪变化缓慢的尺寸–能够跟踪变化缓慢的值
Repair data corruption errors –we can retrieve the data from the historical data and by this we can fix any corrupted data
修复数据损坏错误–我们可以从历史数据中检索数据，从而可以修复任何损坏的数据
Real data sources are dynamic
真实数据源是动态的
No change in programming model or effect on the application
编程模型没有变化或对应用程序没有影响

And one of the biggest advantages is that we don’t need to do workarounds to retrieve historical data and we don’t need to create custom data tracking. We just need to focus on the business logic of the solution and use the newly introduced query construct to query the data.

最大的优势之一是，我们无需执行变通办法即可检索历史数据，也不需要创建自定义数据跟踪。我们只需要关注解决方案的业务逻辑，并使用新引入的查询构造来查询数据。

时态表如何工作 (How does temporal table work)

After enabling the temporal table feature, in any table, SQL Server 2016 will manage the two tables; the current table and the historical table (System-Versioned). The current table (physical table) will contain the last data, after the modification, and the historical table will contain the pervious values for each row after the modification.

启用临时表功能后，在任何表中，SQL Server 2016都将管理两个表。当前表和历史表（系统版本）。修改后，当前表（物理表）将包含最后一个数据，而历史表将包含修改后每行的先前值。

SQL server 2016 introduced query constructs query data from these tables in a single query by using one of the following operations.

SQL Server 2016引入的查询通过使用以下操作之一在单个查询中构造这些表中的查询数据。

Point in time: AS OF <date_time>
时间点 ：AS OF < 日期时间 >
Exclusive bounds: FROM <start_date_time> TO <end_date_time> 排除范围：从<开始日期时间>到<结束日期时间>
Inclusive lower bound, exclusive upper bound: BETWEEN <start_date_time> AND <end_date_time> 包含性下限，排除性上限 ：BETWEEN <开始日期时间>和<结束日期时间>
Inclusive bounds: CONTAINED IN (<start_date_time> , <end_date_time>)
包含范围 ：包含在（<开始日期时间>，<结束日期时间>）

注意事项和限制： (Considerations and limitations:)

There are some requirements and limitations for temporal tables we should be aware of including

我们需要注意的临时表有一些要求和限制

A Primary Key is required in the current table (System-versioned)
当前表中需要主键（系统版本）
A History table must be created in the same database as the current table
必须在与当前表相同的数据库中创建一个历史表
Linked servers are not supported
不支持链接服务器
INSERT and UPDATE statements cannot reference SYSTEM_TIME period columns
INSERT和UPDATE语句无法引用SYSTEM_TIME周期列
The Truncate table operation is not supported on temporal tables
临时表不支持截断表操作
Always On supported
永远在线
A System-Versioned table does not allowed any constraints
系统版本表不允许任何约束
You can’t modify the data in history table “System-Versioned”
您无法修改历史记录表“ System-Versioned”中的数据
Durable memory-optimized tables can be system-versioned
耐用的内存优化表可以系统版本化
Temporal table support Partitioning and column store index
时态表支持分区和列存储索引

For more information check this Microsoft Reference.

有关更多信息，请参见此Microsoft参考。

时间表与CDC (Temporal tables vs CDC)

It depends on your business case as each feature has different functionality and usage

这取决于您的业务案例，因为每个功能都有不同的功能和用法

Temporal Tables can give us a version of the current table at any point in time
临时表可以随时为我们提供当前表的版本
Change data capture change tracking (aka CDC) can help us for determining changes but it will not help us for reviewing (or working with) historical data
变更数据捕获变更跟踪（aka CDC）可以帮助我们确定变更，但无助于我们查看（或使用）历史数据

创建和配置 (Creation and configuration)

Create a new table with the enabled system-versioned table feature
使用启用的系统版本表功能创建新表

To do it we should consider some important things in our implementation for this table

为此，我们应该在此表的实现中考虑一些重要事项

System-Versioned table should have primary key to manage the relation between current table and historical table
系统版本表应具有主键，以管理当前表和历史表之间的关系
The Table should have two columns with data type DATETIME2 to use for the start and end of the row period
该表应具有两列，数据类型为DATETIME2，用于行周期的开始和结束
Two columns used in the row period with data type (DATETIME2) should be NOT NULL
数据类型为（DATETIME2）的行期间中使用的两列应为NOT NULL

These two-period columns we can mark as HIDDEN columns, and they will not appear in the results:

我们可以将以下两个时段的列标记为HIDDEN列，它们将不会出现在结果中：

 
USE SQLSHAKDEMO
Go
---Creating a Temporal Table
CREATE TABLE [dbo].[Countries](
       [CountryID] [int] Primary KEY IDENTITY(1,1) NOT NULL,
       [CountryArName] [nvarchar](255) NULL,
       [CountryEnName] [varchar](255) NULL,
       [IsReserved] [bit] NOT NULL CONSTRAINT [DF_Countries_IsReserved]  DEFAULT ((0)),
	[StartTime] DATETIME2 GENERATED ALWAYS AS ROW START 
              HIDDEN NOT NULL,
       [EndTime]   DATETIME2 GENERATED ALWAYS AS ROW END
              HIDDEN NOT NULL ,
       PERIOD FOR SYSTEM_TIME (StartTime, EndTime)   
) 
WITH(SYSTEM_VERSIONING=ON (HISTORY_TABLE=dbo.CountriesHistory))
Go

Now, after we executed the above script, if we opened SQL Server management studio we will find the new table as system-versioned, and inside this table you will find the corresponding historical table for it.

现在，执行完上面的脚本之后，如果我们打开SQL Server Management Studio，我们将发现新表是系统版本的，并且在该表内部，您将找到与其对应的历史表。

临时表上的DML操作： (DML operations on Temporal Tables:)

Insert: we will push some data in the table “Countries” by using simple insert statement:

插入：我们将使用简单的插入语句将一些数据推入“ 国家 ”表中：

 
 Use SQLSHAKDEMO
GO
INSERT INTO [dbo].[Countries]
           ([CountryArName]
           ,[CountryEnName]
           ,[IsReserved])
     VALUES
           ('Egypt'
           ,'Egypt'
           ,1)
GO

I haven’t mentioned the two DATETIME2 columns (Starttime , Endtime) because they are implemented as hidden columns and won’t impact anything in the application

我没有提到两个DATETIME2列（Starttime，Endtime），因为它们被实现为隐藏列并且不会影响应用程序中的任何内容

After we executed the above Select statement we found the data inserted in the source table but there was not any record in the historical table. By default, when selecting using ( * ) the hidden period columns don’t not appear. To query these columns you must reference them explicitly, like this:

执行上述Select语句后，我们发现数据已插入源表中，但历史表中没有任何记录。默认情况下，使用（*）选择时，隐藏的期间列不会显示。要查询这些列，您必须显式引用它们，如下所示：

 
  Select * , Starttime , Endtime from [Countries]

Update:

更新：

Let’s EXEC this update statement to update the CountryEnName and the check the execution plan to see which tables are affected by this statement:

让我们执行此更新语句来更新CountryEnName并检查执行计划以查看哪些表受此语句影响：

 
Update [Countries] Set 
[CountryEnName] = 'KSA'
where CountryID = 1

As we see in the execution plan, this update statement inserted data in the historical table. Now let us check the historical table:

如我们在执行计划中所见，此update语句将数据插入了历史表。现在让我们检查历史表：

We will discover that the data that existed before the record was updated was inserted into the historical table and we can found the StartTime and EndTime for this operation in the historical table:

我们将发现更新记录之前存在的数据已插入到历史表中，并且我们可以在历史表中找到此操作的StartTime和EndTime ：

清理临时表 (Cleaning up temporal tables)

Delete

删除

 
  Delete from [dbo].[Countries] where CountryID = 1

After the execution of the delete statement you will find that is affected on the both tables {Current table, historical table}

执行delete语句后，您会发现这两个表都受到影响{当前表，历史表}

Truncate

截短

Now if we need to truncate the source table “temporal table” it will not work

现在，如果我们需要截断源表“临时表”，它将无法正常工作

 
  TRUNCATE TABLE dbo.[Countries]

Note: We can’t also do any DML operation on the history table “system-versioned”

注意：我们也无法对“系统版本化”的历史记录表执行任何DML操作

Drop

下降

To drop the temporal table, we should do follow the below sequence to be able to drop the table

要删除时态表，我们应该按照以下顺序进行操作，以便能够删除该表

First if you executed the simple drop statement like this it will not work.

首先，如果您像这样执行简单的drop语句，它将无法正常工作。

 
 Drop table [dbo].[Countries]

So we need to follow the below sequence for cleaning the Temporal table:

 
ALTER TABLE dbo.Countries SET (SYSTEM_VERSIONING = OFF)
GO
ALTER TABLE dbo.Countries
DROP PERIOD FOR SYSTEM_TIME
GO
DROP TABLE dbo.Countries 
DROP TABLE dbo.CountriesHistory

参考资料 (References )

翻译自: https://www.sqlshack.com/concept-basics-temporal-tables-sql-server-2016/

sql 时态表的意义

culuo4781

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
sql 时态表的意义_SQL Server 2016中的时态表的概念和基础

sql 时态表的意义 In this article I’ll cover all aspects of a new SQL Server 2016 feature, Temporal Tables (System-Versioned), including: 在本文中，我将介绍新SQL Server 2016功能“时态表（系统版本）”的所有方面，包括： Introdu...
复制链接

扫一扫