开始使用 Azure SQL 数据库和 Azure SQL 托管实例中的时态表Getting started with temporal tables in Azure SQL Database and Azure SQL Managed Instance

适用于: Azure SQL 数据库 Azure SQL 托管实例

时态表是 Azure SQL 数据库和 Azure SQL 托管实例中的可编程功能,可用于跟踪和分析数据更改的完整历史记录,而无需编写自定义代码。Temporal tables are a programmability feature of Azure SQL Database and Azure SQL Managed Instance that allows you to track and analyze the full history of changes in your data, without the need for custom coding. 时态表保存与时间上下文密切相关的数据,因此,只有特定时段内的存储事实才会解译为有效。Temporal tables keep data closely related to time context so that stored facts can be interpreted as valid only within the specific period. 利用时态表的这种属性,可执行基于时间的有效分析,并从数据演变中获得见解。This property of temporal tables allows for efficient time-based analysis and getting insights from data evolution.

时态表方案Temporal scenario

本文演示了在应用程序方案中使用时态表的步骤。This article illustrates the steps to utilize temporal tables in an application scenario. 假设要跟踪从头开始开发的新网站上的用户活动,或要通过用户活动分析扩展的现有网站上的用户活动。Suppose that you want to track user activity on a new website that is being developed from scratch or on an existing website that you want to extend with user activity analytics. 在这个简化的示例中,我们假设一段时间内浏览过的网页数是需要在托管于 Azure SQL 数据库或 Azure SQL 托管实例上的网站数据库中捕获和监视的指标。In this simplified example, we assume that the number of visited web pages during a period of time is an indicator that needs to be captured and monitored in the website database that is hosted on Azure SQL Database or Azure SQL Managed Instance. 用户活动历史分析的目标是获取有关重新设计网站的意见,并为访客提供更好的体验。The goal of the historical analysis of user activity is to get inputs to redesign website and provide better experience for the visitors.

此方案的数据库模型非常简单 - 用户活动指标以一个整数字段 PageVisited 表示,并与用户配置文件中的基本信息一起捕获。The database model for this scenario is very simple - user activity metric is represented with a single integer field, PageVisited, and is captured along with basic information on the user profile. 此外,对于基于时间的分析,需要为每个用户保留一系列的行,其中每行代表特定时间段内特定用户访问过的网页数。Additionally, for time-based analysis, you would keep a series of rows for each user, where every row represents the number of pages a particular user visited within a specific period of time.

架构

幸运的是,无需对应用进行任何操作即可维护此活动信息。Fortunately, you do not need to put any effort in your app to maintain this activity information. 可以使用时态表将过程自动化:使你在网站设计过程中保有完全的弹性并节省更多的时间,从而将重心放在数据分析本身。With temporal tables, this process is automated - giving you full flexibility during website design and more time to focus on the data analysis itself. 只需确保将 WebSiteInfo 表配置为版本由系统控制的临时表即可。The only thing you have to do is to ensure that WebSiteInfo table is configured as temporal system-versioned. 下面描述了在此方案中使用时态表的确切步骤。The exact steps to utilize temporal tables in this scenario are described below.

步骤 1:将表配置为临时表Step 1: Configure tables as temporal

根据是要开始新的开发工作,还是升级现有的应用程序,可以创建临时表,或者通过添加临时属性来修改现有表。Depending on whether you are starting new development or upgrading existing application, you will either create temporal tables or modify existing ones by adding temporal attributes. 一般情况下,用户方案可能混用了这两个选项。In general case, your scenario can be a mix of these two options. 使用 SQL Server Management Studio (SSMS)、SQL Server Data Tools (SSDT)、Azure Data Studio 或其他任何 Transact-SQL 开发工具执行以下操作。Perform these action using SQL Server Management Studio (SSMS), SQL Server Data Tools (SSDT), Azure Data Studio, or any other Transact-SQL development tool.

重要

建议始终使用最新版本的 Management Studio 以保持与 Azure SQL 数据库和 Azure SQL 托管实例的更新同步。It is recommended that you always use the latest version of Management Studio to remain synchronized with updates to Azure SQL Database and Azure SQL Managed Instance. 更新 SQL Server Management StudioUpdate SQL Server Management Studio.

创建新表Create new table

在 SSMS 对象资源管理器中使用上下文菜单项“新建版本由系统控制的表”打开包含临时表模板脚本的查询编辑器,然后使用“指定模板参数的值”(Ctrl+Shift+M) 来填充模板:Use context menu item "New System-Versioned Table" in SSMS Object Explorer to open the query editor with a temporal table template script and then use "Specify Values for Template Parameters" (Ctrl+Shift+M) to populate the template:

SSMSNewTable

在 SSDT 中将新项添加到数据库项目时,请选择“时态表(版本由系统控制)”模板。In SSDT, choose "Temporal Table (System-Versioned)" template when adding new items to the database project. 此时会打开表设计器,让你轻松指定表布局:That will open table designer and enable you to easily specify the table layout:

SSDTNewTable

也可以通过直接指定 Transact-SQL 语句来创建时态表,如以下示例中所示。You can also create temporal table by specifying the Transact-SQL statements directly, as shown in the example below. 请注意,每个临时表的必需元素为 PERIOD 定义以及引用将存储历史行版本的另一个用户表的 SYSTEM_VERSIONING 子句:Note that the mandatory elements of every temporal table are the PERIOD definition and the SYSTEM_VERSIONING clause with a reference to another user table that will store historical row versions:

CREATE TABLE WebsiteUserInfo
(  
    [UserID] int NOT NULL PRIMARY KEY CLUSTERED
  , [UserName] nvarchar(100) NOT NULL
  , [PagesVisited] int NOT NULL
  , [ValidFrom] datetime2 (0) GENERATED ALWAYS AS ROW START
  , [ValidTo] datetime2 (0) GENERATED ALWAYS AS ROW END
  , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
 )  
 WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.WebsiteUserInfoHistory));

创建版本由系统控制的临时表时,会自动创建随附默认配置的历史记录表。When you create system-versioned temporal table, the accompanying history table with the default configuration is automatically created. 默认历史记录表包含期限列(结束、开始)上启用页压缩的聚集 B 树索引。The default history table contains a clustered B-tree index on the period columns (end, start) with page compression enabled. 此配置非常适合使用临时表的大部分方案,特别是用于数据审核This configuration is optimal for the majority of scenarios in which temporal tables are used, especially for data auditing.

在此特定案例中,我们的目标是针对一段较长的数据历史记录以及较大的数据集,执行基于时间的趋势分析,因此历史记录表的存储选择为聚集列存储索引。In this particular case, we aim to perform time-based trend analysis over a longer data history and with bigger data sets, so the storage choice for the history table is a clustered columnstore index. 聚集列存储为分析查询提供极佳的压缩和性能。A clustered columnstore provides very good compression and performance for analytical queries. 时态表允许灵活且完全独立地在当前表和时态表中配置索引。Temporal tables give you the flexibility to configure indexes on the current and temporal tables completely independently.

备注

业务关键、常规用途、高级层、标准层、S3 及更高版本中提供列存储索引。Columnstore indexes are available in the Business Critical, General Purpose, and Premium tiers and in the Standard tier, S3 and above.

以下脚本演示如何将历史记录表的默认索引更改为聚集列存储:The following script shows how default index on history table can be changed to the clustered columnstore:

CREATE CLUSTERED COLUMNSTORE INDEX IX_WebsiteUserInfoHistory
ON dbo.WebsiteUserInfoHistory
WITH (DROP_EXISTING = ON);

时态表在对象资源管理器中以特定图标表示以便于识别,其历史记录表显示为子节点。Temporal tables are represented in the Object Explorer with the specific icon for easier identification, while its history table is displayed as a child node.

AlterTable

将现有表更改为临时表Alter existing table to temporal

下面探讨替代方案,其中 WebsiteUserInfo 表已存在,但不是针对保留更改历史记录而设计的。Let's cover the alternative scenario in which the WebsiteUserInfo table already exists, but was not designed to keep a history of changes. 在这种情况下,只需将现有表扩展为临时表即可,如以下示例中所示:In this case, you can simply extend the existing table to become temporal, as shown in the following example:

ALTER TABLE WebsiteUserInfo
ADD
    ValidFrom datetime2 (0) GENERATED ALWAYS AS ROW START HIDDEN  
        constraint DF_ValidFrom DEFAULT DATEADD(SECOND, -1, SYSUTCDATETIME())
    , ValidTo datetime2 (0)  GENERATED ALWAYS AS ROW END HIDDEN
        constraint DF_ValidTo DEFAULT '9999.12.31 23:59:59.99'
    , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);

ALTER TABLE WebsiteUserInfo  
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.WebsiteUserInfoHistory));
GO

CREATE CLUSTERED COLUMNSTORE INDEX IX_WebsiteUserInfoHistory
ON dbo.WebsiteUserInfoHistory
WITH (DROP_EXISTING = ON);

步骤 2:定期运行工作负荷Step 2: Run your workload regularly

时态表的主要优点是,不需要以任何方式更改或调整网站就可以执行更改跟踪。The main advantage of temporal tables is that you do not need to change or adjust your website in any way to perform change tracking. 创建时态表后,每当对数据进行修改时,以前的行版本都会自动保存。Once created, temporal tables transparently persist previous row versions every time you perform modifications on your data.

若要利用此特定方案的自动更改跟踪功能,只需在每次用户在网站上结束其会话时更新列 PagesVisitedIn order to leverage automatic change tracking for this particular scenario, let's just update column PagesVisited every time a user ends their session on the website:

UPDATE WebsiteUserInfo  SET [PagesVisited] = 5
WHERE [UserID] = 1;

请务必注意,更新查询不需要知道实际操作进行的时间,也不需要知道如何保留历史数据以供将来分析使用。It is important to notice that the update query doesn’t need to know the exact time when the actual operation occurred nor how historical data will be preserved for future analysis. Azure SQL 数据库和 Azure SQL 托管实例会自动处理这两个方面。Both aspects are automatically handled by Azure SQL Database and Azure SQL Managed Instance. 下图演示了如何在每次更新时生成历史记录数据。The following diagram illustrates how history data is being generated on every update.

TemporalArchitecture

步骤 3:执行历史数据分析Step 3: Perform historical data analysis

现在,当启用版本由系统控制的临时表时,只需一个查询就能执行历史数据分析。Now when temporal system-versioning is enabled, historical data analysis is just one query away from you. 本文将提供一些解决常见分析方案的示例 - 若要了解所有详细信息,请浏览随 FOR SYSTEM_TIME 子句一起引入的各种选项。In this article, we will provide a few examples that address common analysis scenarios - to learn all details, explore various options introduced with the FOR SYSTEM_TIME clause.

若要查看按访问网页次数排序的前 10 个用户,请运行以下查询:To see the top 10 users ordered by the number of visited web pages as of an hour ago, run this query:

DECLARE @hourAgo datetime2 = DATEADD(HOUR, -1, SYSUTCDATETIME());
SELECT TOP 10 * FROM dbo.WebsiteUserInfo FOR SYSTEM_TIME AS OF @hourAgo
ORDER BY PagesVisited DESC

可轻松修改此查询,以分析一天前、一个月前或所需的任何过去时间点的站点访问记录。You can easily modify this query to analyze the site visits as of a day ago, a month ago or at any point in the past you wish.

若要执行前一天的基本统计分析,请使用以下示例:To perform basic statistical analysis for the previous day, use the following example:

DECLARE @twoDaysAgo datetime2 = DATEADD(DAY, -2, SYSUTCDATETIME());
DECLARE @aDayAgo datetime2 = DATEADD(DAY, -1, SYSUTCDATETIME());

SELECT UserID, SUM (PagesVisited) as TotalVisitedPages, AVG (PagesVisited) as AverageVisitedPages,
MAX (PagesVisited) AS MaxVisitedPages, MIN (PagesVisited) AS MinVisitedPages,
STDEV (PagesVisited) as StDevViistedPages
FROM dbo.WebsiteUserInfo
FOR SYSTEM_TIME BETWEEN @twoDaysAgo AND @aDayAgo
GROUP BY UserId

若要搜索特定用户在某个时间段的活动,请使用 CONTAINED IN 子句:To search for activities of a specific user, within a period of time, use the CONTAINED IN clause:

DECLARE @hourAgo datetime2 = DATEADD(HOUR, -1, SYSUTCDATETIME());
DECLARE @twoHoursAgo datetime2 = DATEADD(HOUR, -2, SYSUTCDATETIME());
SELECT * FROM dbo.WebsiteUserInfo
FOR SYSTEM_TIME CONTAINED IN (@twoHoursAgo, @hourAgo)
WHERE [UserID] = 1;

图形可视化对于临时查询特别方便,因为可以轻松、直观地显示趋势和使用模式:Graphic visualization is especially convenient for temporal queries as you can show trends and usage patterns in an intuitive way very easily:

TemporalGraph

不断演变的表架构Evolving table schema

通常,开发应用时需要更改临时表架构。Typically, you will need to change the temporal table schema while you are doing app development. 为此,只需运行常规 ALTER TABLE 语句,Azure SQL 数据库或 Azure SQL 托管实例就会正确传播历史记录表的更改。For that, simply run regular ALTER TABLE statements and Azure SQL Database or Azure SQL Managed Instance appropriately propagates changes to the history table. 以下脚本演示如何添加要跟踪的其他属性:The following script shows how you can add additional attribute for tracking:

/*Add new column for tracking source IP address*/
ALTER TABLE dbo.WebsiteUserInfo
ADD  [IPAddress] varchar(128) NOT NULL CONSTRAINT DF_Address DEFAULT 'N/A';

同样,可在工作负荷处于活动状态时更改列定义:Similarly, you can change column definition while your workload is active:

/*Increase the length of name column*/
ALTER TABLE dbo.WebsiteUserInfo
    ALTER COLUMN  UserName nvarchar(256) NOT NULL;

最后,可删除不再需要的列。Finally, you can remove a column that you do not need anymore.

/*Drop unnecessary column */
ALTER TABLE dbo.WebsiteUserInfo
    DROP COLUMN TemporaryColumn;

或者,在已连接到数据库(联机模式)或正在开发数据库项目(脱机模式)时,使用最新的 SSDT 来更改临时表架构。Alternatively, use latest SSDT to change temporal table schema while you are connected to the database (online mode) or as part of the database project (offline mode).

控制历史数据的保留期Controlling retention of historical data

使用版本由系统控制的临时表时,历史记录表可能比常规表更容易增大数据库大小。With system-versioned temporal tables, the history table may increase the database size more than regular tables. 大型以及不断增长的历史记录表可能会成为一个问题,这不单单体现在存储成本的增加上,而且还会降低临时查询的性能。A large and ever-growing history table can become an issue both due to pure storage costs as well as imposing a performance tax on temporal querying. 因此,针对管理历史记录表中的数据制定一个数据保留策略,是规划和管理每个临时表的生命周期的一个重要方面。Hence, developing a data retention policy for managing data in the history table is an important aspect of planning and managing the lifecycle of every temporal table. 使用 Azure SQL 数据库和 Azure SQL 托管实例,可以通过以下方法来管理时态表中的历史数据:With Azure SQL Database and Azure SQL Managed Instance, you have the following approaches for managing historical data in the temporal table:

后续步骤Next steps

有关时态表的详细信息,请参阅签出时态表For more information on Temporal Tables, see check out Temporal Tables.