时态表入门

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

时态表是可编程功能，可用于跟踪和分析数据更改的完整历史记录，而无需编写自定义代码。 时态表保存与时间上下文密切相关的数据，因此，只有特定时段内的存储事实才会解译为有效。 利用时态表的这种属性，可执行基于时间的有效分析，并从数据演变中获得见解。

时态表方案

本文演示了在应用程序方案中使用时态表的步骤。 假设要跟踪从头开始开发的新网站上的用户活动，或要通过用户活动分析扩展的现有网站上的用户活动。 在这个简化的示例中，我们假设一段时间内浏览过的网页数是需要在托管于 Azure SQL 数据库或 Azure SQL 托管实例上的网站数据库中捕获和监视的指标。 用户活动历史分析的目标是获取有关重新设计网站的意见，并为访客提供更好的体验。

此方案的数据库模型简单 - 用户活动指标以一个整数字段 PageVisited 表示，并与用户配置文件中的基本信息一起捕获。 此外，对于基于时间的分析，需要为每个用户保留一系列的行，其中每行代表特定时间段内特定用户访问过的网页数。

幸运的是，无需对应用进行任何操作即可维护此活动信息。 可以使用时态表将过程自动化：使你在网站设计过程中保有完全的弹性并节省更多的时间，从而将重心放在数据分析本身。 只需确保将 WebSiteInfo 表配置为版本由系统控制的临时表即可。 下面描述了在此方案中使用时态表的确切步骤。

步骤 1：将表配置为临时表

根据是要开始新的开发工作，还是升级现有的应用程序，可以创建临时表，或者通过添加临时属性来修改现有表。 一般情况下，用户方案可能混用了这两个选项。 使用 SQL Server Management Studio (SSMS)、SQL Server Data Tools (SSDT)、Azure Data Studio 或其他任何 Transact-SQL 开发工具执行以下操作。

创建新表

在 SSMS 对象资源管理器中使用上下文菜单项“新建版本由系统控制的表”打开包含临时表模板脚本的查询编辑器，然后使用“指定模板参数的值”(Ctrl+Shift+M) 来填充模板：

在 SSDT 中将新项添加到数据库项目时，请选择“时态表(版本由系统控制)”模板。 此时会打开表设计器，让你轻松指定表布局：

也可以通过直接指定 Transact-SQL 语句来创建时态表，如以下示例中所示。 每个临时表的必需元素为 PERIOD 定义以及引用将存储历史行版本的另一个用户表的 SYSTEM_VERSIONING 子句：

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));

创建版本由系统控制的临时表时，会自动创建随附默认配置的历史记录表。 默认历史记录表包含期限列（结束、开始）上启用页压缩的聚集 B 树索引。 此配置非常适合使用临时表的大部分方案，特别是用于数据审核。

在此特定案例中，我们的目标是针对一段较长的数据历史记录以及较大的数据集，执行基于时间的趋势分析，因此历史记录表的存储选择为聚集列存储索引。 聚集列存储为分析查询提供极佳的压缩和性能。 时态表允许灵活且完全独立地在当前表和时态表中配置索引。

以下脚本演示如何将历史记录表的默认索引更改为聚集列存储：

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

时态表在对象资源管理器中以特定图标表示以便于识别，其历史记录表显示为子节点。

将现有表更改为临时表

下面探讨替代方案，其中 WebsiteUserInfo 表已存在，但不是针对保留更改历史记录而设计的。 在这种情况下，只需将现有表扩展为临时表即可，如以下示例中所示：

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：定期运行工作负荷

临时表的主要优点是，不需要以任何方式更改或调整网站就可以执行更改跟踪。 创建时态表后，每当对数据进行修改时，以前的行版本都会自动保存。

若要利用此特定方案的自动更改跟踪功能，只需在每次用户在网站上结束其会话时更新列 PagesVisited：

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

请务必注意，更新查询不需要知道实际操作进行的具体时间，也不需要知道如何保留历史数据以供将来分析使用。 Azure SQL 数据库和 Azure SQL 托管实例会自动处理这两个方面。 下图演示了如何在每次更新时生成历史记录数据。

步骤 3：执行历史数据分析

现在，当启用版本由系统控制的临时表时，只需一个查询就能执行历史数据分析。 本文将提供一些解决常见分析方案的示例 - 若要了解所有详细信息，请浏览随 FOR SYSTEM_TIME 子句一起引入的各种选项。

若要查看按访问网页次数排序的前 10 个用户，请运行以下查询：

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

可轻松修改此查询，以分析一天前、一个月前或所需的任何过去时间点的站点访问记录。

若要执行前一天的基本统计分析，请使用以下示例：

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 子句：

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;

图形可视化对于临时查询特别方便，因为可以轻松、直观地显示趋势和使用模式：

不断演变的表架构

通常，开发应用时需要更改临时表架构。 为此，只需运行常规 ALTER TABLE 语句，Azure SQL 数据库或 Azure SQL 托管实例就会正确传播历史记录表的更改。 以下脚本演示如何添加要跟踪的其他属性：

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

同样，可在工作负荷处于活动状态时更改列定义：

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

最后，可删除不再需要的列。

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

或者，在已连接到数据库（联机模式）或正在开发数据库项目（脱机模式）时，使用最新的 SSDT 来更改临时表架构。

控制历史数据的保留期

使用版本由系统控制的临时表时，历史记录表可能比常规表更容易增大数据库大小。 大型以及不断增长的历史记录表可能会成为一个问题，这不单单体现在存储成本的增加上，而且还会降低临时查询的性能。 因此，针对管理历史记录表中的数据制定一个数据保留策略，是规划和管理每个临时表的生命周期的一个重要方面。 使用 Azure SQL 数据库和 Azure SQL 托管实例，可以通过以下方法来管理时态表中的历史数据：

• 表分区
• 自定义清理脚本

相关内容

• 有关临时表的详细信息，请参阅临时表。