使用保留策略管理临时表中的历史数据Manage historical data in Temporal tables with retention policy

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

与普通表相比,临时表数据库大小的增长幅度可能更大,尤其是长时间保留历史数据时。Temporal tables may increase database size more than regular tables, especially if you retain historical data for a longer period of time. 因此,针对历史数据创建保留策略是规划和管理每个临时表的生命周期的一个重要方面。Hence, retention policy for historical data is an important aspect of planning and managing the lifecycle of every temporal table. Azure SQL 数据库和 Azure SQL 托管实例中的临时表附带了易用的保留机制,可帮助完成此任务。Temporal tables in Azure SQL Database and Azure SQL Managed Instance come with easy-to-use retention mechanism that helps you accomplish this task.

可以在单个表的级别配置临时历史记录保留期,以便用户创建灵活的期限策略。Temporal history retention can be configured at the individual table level, which allows users to create flexible aging polices. 应用临时保留的过程十分简单:只需在创建表或更改架构期间设置一个参数即可。Applying temporal retention is simple: it requires only one parameter to be set during table creation or schema change.

定义保留策略后,Azure SQL 数据库和 Azure SQL 托管实例开始定期检查是否有符合数据自动清理条件的历史数据行。After you define retention policy, Azure SQL Database and Azure SQL Managed Instance starts checking regularly if there are historical rows that are eligible for automatic data cleanup. 匹配行的识别以及从历史记录表中删除这些行的过程在系统计划和运行的后台任务中发生。Identification of matching rows and their removal from the history table occur transparently, in the background task that is scheduled and run by the system. 历史记录表行的期限条件根据表示 SYSTEM_TIME 期限结束时间的列进行检查。Age condition for the history table rows is checked based on the column representing end of SYSTEM_TIME period. 例如,如果保留期设置为六个月,则可以清理符合以下条件的表行:If retention period, for example, is set to six months, table rows eligible for cleanup satisfy the following condition:

ValidTo < DATEADD (MONTH, -6, SYSUTCDATETIME())

在前面的示例中,假设 ValidTo 列对应于 SYSTEM_TIME 期限结束时间。In the preceding example, we assumed that ValidTo column corresponds to the end of SYSTEM_TIME period.

如何配置保留策略How to configure retention policy

在为临时表配置保留策略之前,请先检查是否 在数据库级别启用了临时历史记录保留策略。Before you configure retention policy for a temporal table, check first whether temporal historical retention is enabled at the database level.

SELECT is_temporal_history_retention_enabled, name
FROM sys.databases

数据库标志 is_temporal_history_retention_enabled 默认设置为 ON,但用户可以使用 ALTER DATABASE 语句更改此值。Database flag is_temporal_history_retention_enabled is set to ON by default, but users can change it with ALTER DATABASE statement. 在执行时间点还原操作后,它会自动设置为 OFF。It is also automatically set to OFF after point in time restore operation. 若要为数据库启用临时历史记录保留策略清理,请执行以下语句:To enable temporal history retention cleanup for your database, execute the following statement:

ALTER DATABASE <myDB>
SET TEMPORAL_HISTORY_RETENTION  ON

重要

即使 is_temporal_history_retention_enabled 设置为 OFF,也可以为临时表配置保留策略,但在这种情况下,不会针对陈旧的行触发自动清理。You can configure retention for temporal tables even if is_temporal_history_retention_enabled is OFF, but automatic cleanup for aged rows is not triggered in that case.

在创建表的过程中,可以通过指定 HISTORY_RETENTION_PERIOD 参数的值来配置保留策略:Retention policy is configured during table creation by specifying value for the HISTORY_RETENTION_PERIOD parameter:

CREATE TABLE dbo.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,
        HISTORY_RETENTION_PERIOD = 6 MONTHS
     )
 );

Azure SQL 数据库和 Azure SQL 托管实例允许使用不同的时间单位指定保留策略:天、周、月和年。Azure SQL Database and Azure SQL Managed Instance allow you to specify retention period by using different time units: DAYS, WEEKS, MONTHS, and YEARS. 如果省略 HISTORY_RETENTION_PERIOD,则假设保留期限为 INFINITE(无限期)。If HISTORY_RETENTION_PERIOD is omitted, INFINITE retention is assumed. 也可以显式使用 INFINITE 关键字。You can also use INFINITE keyword explicitly.

在某些情况下,你可能想要在创建表后配置保留策略或更改以前配置的值。In some scenarios, you may want to configure retention after table creation, or to change previously configured value. 在这种情况下,请使用 ALTER TABLE 语句:In that case use ALTER TABLE statement:

ALTER TABLE dbo.WebsiteUserInfo
SET (SYSTEM_VERSIONING = ON (HISTORY_RETENTION_PERIOD = 9 MONTHS));

重要

将 SYSTEM_VERSIONING 设置为 OFF 不会保存保留期值。Setting SYSTEM_VERSIONING to OFF does not preserve retention period value. 在未显式指定 HISTORY_RETENTION_PERIOD 的情况下将 SYSTEM_VERSIONING 设置为 ON 会导致保留期为 INFINITE。Setting SYSTEM_VERSIONING to ON without HISTORY_RETENTION_PERIOD specified explicitly results in the INFINITE retention period.

要查看保留策略的当前状态,请使用以下查询,该查询将数据库级别的临时保留启用标志与单个表的保留期相联接:To review current state of the retention policy, use the following query that joins temporal retention enablement flag at the database level with retention periods for individual tables:

SELECT DB.is_temporal_history_retention_enabled,
SCHEMA_NAME(T1.schema_id) AS TemporalTableSchema,
T1.name as TemporalTableName,  SCHEMA_NAME(T2.schema_id) AS HistoryTableSchema,
T2.name as HistoryTableName,T1.history_retention_period,
T1.history_retention_period_unit_desc
FROM sys.tables T1  
OUTER APPLY (select is_temporal_history_retention_enabled from sys.databases
where name = DB_NAME()) AS DB
LEFT JOIN sys.tables T2
ON T1.history_table_id = T2.object_id WHERE T1.temporal_type = 2

删除“存在时间”行的方式How ages rows are deleted

清理过程取决于历史记录表的索引布局。The cleanup process depends on the index layout of the history table. 必须注意,只能为具有聚集索引(B 树或列存储)的历史记录表配置有限期保留策略。It is important to notice that only history tables with a clustered index (B-tree or columnstore) can have finite retention policy configured. 对于具有有限保留期的所有临时表,系统会创建一个后台任务来执行陈旧数据清理。A background task is created to perform aged data cleanup for all temporal tables with finite retention period. 行存储(B 树)聚集索引的清理逻辑以较小的区块(最大 10K)删除陈旧行,从而可以最大程度地减轻数据库日志和 IO 子系统的压力。Cleanup logic for the rowstore (B-tree) clustered index deletes aged row in smaller chunks (up to 10K) minimizing pressure on database log and IO subsystem. 尽管清理逻辑利用所需的 B 树索引,但不一定能够保证按顺序删除超过保留期的行。Although cleanup logic utilizes required B-tree index, order of deletions for the rows older than retention period cannot be firmly guaranteed. 因此, 请不要对应用程序中的清理顺序有任何依赖Hence, do not take any dependency on the cleanup order in your applications.

针对聚集列存储的清理任务会一次性删除整个行组(每个行组通常包含 1 百万行),这种方式非常高效,尤其是在高速生成历史数据时。The cleanup task for the clustered columnstore removes entire row groups at once (typically contain 1 million of rows each), which is very efficient, especially when historical data is generated at a high pace.

聚集列存储保留

当工作负荷快速生成大量的历史数据时,优异的数据压缩和高效的保留数据清理使得聚集列存储索引成为完美的选择。Excellent data compression and efficient retention cleanup makes clustered columnstore index a perfect choice for scenarios when your workload rapidly generates high amount of historical data. 使用临时表进行更改跟踪和审核、趋势分析或 IoT 数据引入的密集型事务处理工作负荷往往使用该模式。That pattern is typical for intensive transactional processing workloads that use temporal tables for change tracking and auditing, trend analysis, or IoT data ingestion.

索引注意事项Index considerations

针对具有行存储聚集索引的表的清理任务要求索引的开头为对应于 SYSTEM_TIME 期限结束时间的列。The cleanup task for tables with rowstore clustered index requires index to start with the column corresponding the end of SYSTEM_TIME period. 若没有此类索引,则无法配置有限保留期:If such index doesn't exist, you cannot configure a finite retention period:

消息 13765,级别 16,状态 1

在版本由系统控制的临时表 'temporalstagetestdb.dbo.WebsiteUserInfo' 中设置有限保留期失败,因为历史记录表 'temporalstagetestdb.dbo.WebsiteUserInfoHistory' 不包含所需的聚集索引。请考虑在历史记录表中创建聚集列存储,或者创建开头为与 SYSTEM_TIME 期限结束时间匹配的列的 B 树索引。
Msg 13765, Level 16, State 1

Setting finite retention period failed on system-versioned temporal table 'temporalstagetestdb.dbo.WebsiteUserInfo' because the history table 'temporalstagetestdb.dbo.WebsiteUserInfoHistory' does not contain required clustered index. Consider creating a clustered columnstore or B-tree index starting with the column that matches end of SYSTEM_TIME period, on the history table.

请务必注意,Azure SQL 数据库和 Azure SQL 托管实例创建的默认历史记录表已有聚集索引,这符合保留策略。It is important to notice that the default history table created by Azure SQL Database and Azure SQL Managed Instance already has clustered index, which is compliant for retention policy. 如果尝试在具有有限保留期的表中删除该索引,该操作会失败并出现以下错误:If you try to remove that index on a table with finite retention period, operation fails with the following error:

Msg 13766,级别 16,状态 1

无法删除聚集索引 'WebsiteUserInfoHistory.IX_WebsiteUserInfoHistory',因为它正用于自动清理陈旧数据。如果需要删除此索引,请考虑在版本由系统控制的临时表中将 HISTORY_RETENTION_PERIOD 设置为 INFINITE。
Msg 13766, Level 16, State 1

Cannot drop the clustered index 'WebsiteUserInfoHistory.IX_WebsiteUserInfoHistory' because it is being used for automatic cleanup of aged data. Consider setting HISTORY_RETENTION_PERIOD to INFINITE on the corresponding system-versioned temporal table if you need to drop this index.

如果按升序插入历史行(按期限结束时间列排序),则清理聚集列存储索引的过程最为顺利。当历史记录表是由 SYSTEM_VERSIONIOING 机制以独占方式填充时,情况往往如此。Cleanup on the clustered columnstore index works optimally if historical rows are inserted in the ascending order (ordered by the end of period column), which is always the case when the history table is populated exclusively by the SYSTEM_VERSIONIOING mechanism. 如果历史记录表中的行未按期限结束时间列排序(如果迁移了现有历史数据,可能会存在这种情况),则应在正确排序的 B 树行存储索引顶层重新创建聚集列存储索引,以获得最佳性能。If rows in the history table are not ordered by end of period column (which may be the case if you migrated existing historical data), you should re-create clustered columnstore index on top of B-tree rowstore index that is properly ordered, to achieve optimal performance.

避免在具有有限保留期的历史记录表中重建聚集列存储索引,因为这可能会改变行组中由系统版本控制操作施加的固有顺序。Avoid rebuilding clustered columnstore index on the history table with the finite retention period, because it may change ordering in the row groups naturally imposed by the system-versioning operation. 如果需要在历史记录表中重建聚集列存储索引,请在符合条件的 B 树索引顶层创建该索引,同时保留行组的顺序,以便能够执行常规数据清理。If you need to rebuild clustered columnstore index on the history table, do that by re-creating it on top of compliant B-tree index, preserving ordering in the rowgroups necessary for regular data cleanup. 如果要使用具有聚集列索引且数据顺序没有保证的现有历史记录表创建临时表,则应采用同样的方法:The same approach should be taken if you create temporal table with existing history table that has clustered column index without guaranteed data order:

/*Create B-tree ordered by the end of period column*/
CREATE CLUSTERED INDEX IX_WebsiteUserInfoHistory ON WebsiteUserInfoHistory (ValidTo)
WITH (DROP_EXISTING = ON);
GO
/*Re-create clustered columnstore index*/
CREATE CLUSTERED COLUMNSTORE INDEX IX_WebsiteUserInfoHistory ON WebsiteUserInfoHistory
WITH (DROP_EXISTING = ON);

为具有聚集列存储索引的历史记录表配置有限保留期时,无法在该表表创建附加的非聚集 B 树索引:When finite retention period is configured for the history table with the clustered columnstore index, you cannot create additional non-clustered B-tree indexes on that table:

CREATE NONCLUSTERED INDEX IX_WebHistNCI ON WebsiteUserInfoHistory ([UserName])

尝试执行上述语句会失败并出现以下错误:An attempt to execute above statement fails with the following error:

消息 13772,级别 16,状态 1

无法在临时历史记录表 'WebsiteUserInfoHistory' 中创建非聚集索引,因为该表定义了有限保留期和聚集列存储索引。
Msg 13772, Level 16, State 1

Cannot create non-clustered index on a temporal history table 'WebsiteUserInfoHistory' since it has finite retention period and clustered columnstore index defined.

使用保留策略查询表Querying tables with retention policy

针对临时表执行的所有查询会自动筛选出与有限保留策略匹配的历史行,以免出现不可预测且不一致的结果,因为清理任务可能会 在任何时间点按任意顺序删除陈旧行。All queries on the temporal table automatically filter out historical rows matching finite retention policy, to avoid unpredictable and inconsistent results, since aged rows can be deleted by the cleanup task, at any point in time and in arbitrary order.

下图显示一个简单查询的查询计划:The following picture shows the query plan for a simple query:

SELECT * FROM dbo.WebsiteUserInfo FOR SYSTEM_TIME ALL;

该查询计划包含的附加筛选器已应用到历史记录表上“聚集索引扫描”运算符中的期限结束时间列 (ValidTo)(已突出显示)。The query plan includes additional filter applied to end of period column (ValidTo) in the Clustered Index Scan operator on the history table (highlighted). 此示例假设已在 WebsiteUserInfo 表中设置了一个月 (1 MONTH) 的保留期。This example assumes that one MONTH retention period was set on WebsiteUserInfo table.

保留查询筛选器

但是,在直接查询历史记录表时,可以看到超过指定保留期的行,但不保证会看到可重复的查询结果。However, if you query history table directly, you may see rows that are older than specified retention period, but without any guarantee for repeatable query results. 下图显示了针对历史记录表执行的、未应用附加筛选器的查询的查询执行计划:The following picture shows query execution plan for the query on the history table without additional filters applied:

在不使用保留筛选器的情况下查询历史记录

不要依赖于业务逻辑来读取超过保留期的历史记录表,否则可能会收到不一致或意外的结果。Do not rely your business logic on reading history table beyond retention period as you may get inconsistent or unexpected results. 建议配合 FOR SYSTEM_TIME 子句使用临时查询来分析临时表中的数据。We recommend that you use temporal queries with FOR SYSTEM_TIME clause for analyzing data in temporal tables.

时间点还原注意事项Point in time restore considerations

通过将现有数据库还原到特定时间点创建新数据库时,将在数据库级别禁用临时保留。When you create new database by restoring existing database to a specific point in time, it has temporal retention disabled at the database level. is_temporal_history_retention_enabled 标志设置为 OFF)(is_temporal_history_retention_enabled flag set to OFF). 使用此功能可以在还原时检查所有历史行,无需担心在查询陈旧行之前它们是否已删除。This functionality allows you to examine all historical rows upon restore, without worrying that aged rows are removed before you get to query them. 可以使用此功能检查已超过配置的保留期的历史数据You can use it to inspect historical data beyond configured retention period.

假设为某个临时表指定了一个月的保留期。Say that a temporal table has one MONTH retention period specified. 如果数据库是在高级服务层中创建的,则可以使用保持过去最多 35 天前状态的数据库创建数据库副本。If your database was created in Premium Service tier, you would be able to create database copy with the database state up to 35 days back in the past. 这样,便可以通过直接查询历史记录表,有效分析保留时间最长为 65 天前的历史行。That effectively would allow you to analyze historical rows that are up to 65 days old by querying the history table directly.

如果想要激活临时保留清理,请在执行时间点还原后运行以下 Transact-SQL 语句:If you want to activate temporal retention cleanup, run the following Transact-SQL statement after point in time restore:

ALTER DATABASE <myDB>
SET TEMPORAL_HISTORY_RETENTION  ON

后续步骤Next steps

若要了解如何在应用程序中使用临时表,请查看临时表入门To learn how to use temporal tables in your applications, check out Getting Started with Temporal Tables.

有关临时表的详细信息,请查看临时表For detailed information about temporal tables, review Temporal tables.