如何使用SQL Server的时间表

吴欣
306 阅读4分钟

时间表，也被称为系统版本表，是允许你跟踪和保持一个特定表的数据历史的表。使用时态表，你可以跟踪对一个表中的数据所做的修改的历史。

这篇文章将为在SQL Server中创建、处理和使用时态表奠定基础。

系统版本表是在ANSI SQL 2011标准中引入的，并在SQL Server 2016及以上版本中作为一项功能提供。

与只能显示和处理当前数据的普通表不同，时态表允许你甚至可以查看和处理以前删除的数据。如前所述，这是由于时态表能够跟踪表内数据的变化而实现的。

该表包含两个关键列。SysStartTime和SysEndTime。这两列用于定义表内每条记录的现有和以前的数据。你可以使用特定的时间间隔来查看表内数据的变化情况。

创建一个时态表

在你创建一个时态表之前，它必须满足以下要求。

  1. 暂时性表必须包含一个定义的主键约束。
  2. 它必须包含两列记录开始和结束日期。这些列必须是datetime2的数据类型。这些列应该被声明为GENERATED ALWAYS AS ROW START/END。
  3. SQL Server假定这两个列是不可归零的。因此，如果查询试图设置可归零的列，创建表的语句就会失败。
  4. SQL Server自动生成一个历史表，使用与时间表类似的模式。
  5. 不能在系统版本表中使用INSTEAD OF触发器。
  6. 历史表不应该包含任何约束。
  7. 你不能改变历史表的数据。
  8. 语句，如INSERT和UPDATE不能引用周期列。
  9. 历史表被创建为一个行历史表，如果适用的话，会应用页面压缩。否则，该表将不被压缩。
  10. SQL Server将为历史表自动生成一个聚类索引。

如何创建一个时间表：T-SQL

让我们看一下创建时态表的一个简单演示。考虑下图所示的查询示例。

CREATE TABLE dbo.my_temporal_table(
    id INT,
    fname VARCHAR(50),
    email VARCHAR (255),
    department VARCHAR(50),
    CONSTRAINT pk PRIMARY KEY (id),
    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);

一旦我们运行上面的查询，SQL Server将以指定的名称创建表。

在SQL Server Management Studio中，你可以通过扩展目标数据库中的表选项来查看系统版本的表。

注意，SQL Server会自动生成一个历史表，其模式与系统版本表类似。然而，请注意历史表中的列。注意它们没有任何约束。

考虑下图所示的图像。

正如你将看到的，SQL Server会在一个遵循特定格式的名称下生成一个历史表。要为你的历史表设置一个自定义的名字，请在创建表的语句中指定它，如图所示。

---
period FOR system_time (SysStartTime, SysEndTime)) WITH (system_versioning = ON, history_table = mytemporal_tableHistory);
---

接下来，如果你展开历史表的索引选项，你会发现SQL Server自动生成了一个聚类索引。

使用时态表

让我们通过在表中插入几条记录来测试时态表的功能。考虑下图所示的查询示例。

INSERT INTO my_temporal_table(id, fname, email, department)
VALUES (1, 'John Davis', 'davis@keep.mail', 'Front-End'),
       (2, 'Ruby Raw', 'rubyraw@info.io', 'Database'),
       (3, 'Scott Turner','turner@mail.com', 'Full-Stack'),
       (4, 'Alice Jensen', 'alice.jensen@hotmail.com', 'Version Control'),
       (5, 'Peter Green', 'green@p.tv', 'Backend');

一旦我们把样本数据插入到表中，我们就可以像这样查询它。

SELECT * FROM my_temporal_table;

你应该得到一个接近于下图的输出，如

为了了解系统版本表是如何工作的，让我们删除和更新表中的行。

DELETE FROM my_temporal_table WHERE department = 'Database';
UPDATE my_temporal_table SET fname = 'John M' WHERE id = 5;

接下来，查询主表中的数据。

SELECT * FROM my_temporal_table;

如果你查询历史表，你应该看到带有正确时间戳的旧版数据。

总结

本指南涵盖了SQL Server中的时态表或系统版本表的概念。使用本指南，你将能够使用SQL Server的时间表来跟踪你的数据的历史。

avatar
文章
阅读
粉丝