Server 2016:向现有应用程序添加时态表

Question

我有一个由外部公司开发的大型PHP应用程序，目前正在完成；我不能要求对它进行更改。它使用Microsoft SQL Server 2016数据库，包括我在内的一些用户通过SSMS访问该数据库，并在一天中的特定时间通过一些预定任务进行访问。有时，我会在一些表中找到意想不到的内容，这是令人沮丧的，因为我不知道它是什么时候到达的。我刚刚发现了时态表:知道记录更改的确切日期和时间将非常有用。

是否可以在不更改PHP代码的情况下向应用程序中的2-3个关键表添加时态表？是否存在兼容性或性能的风险？

Answer 1

是否可以在不更改PHP代码的情况下向应用程序中的2-3个关键表添加时态表？

您应该能够实现这个目标(假设应用程序是以向表中添加额外列的方式编写的，不会造成问题)。当然，测试。

使用Greg的post 修改现有表以支持时态数据中提供的示例代码，让我们学习一个示例。

这是我们目前的桌子。

DROP TABLE IF EXISTS dbo.Product
DROP TABLE IF EXISTS dbo.ProductHistory

CREATE TABLE dbo.Product (
    ID INT Identity
    ,ProductName VARCHAR(50)
    ,ProductPrice DECIMAL(20, 2)
    );

INSERT INTO dbo.Product (ProductName, ProductPrice)
VALUES ('Widget',33.49),('Doo-Hickey',21.76),('Thing-A-Ma-Jig',20.16);

SELECT * FROM dbo.Product

| ID | ProductName    | ProductPrice |
|----|----------------|--------------|
| 1  | Widget         | 33.49        |
| 2  | Doo-Hickey     | 21.76        |
| 3  | Thing-A-Ma-Jig | 20.16        |

我们现在要跟踪版本。

为了开始为我的dbo.Product表收集历史信息，我需要修改表，以便它能够支持时态数据。Server 2016时态表要求表具有主键和日期/时间列的一对。两个日期/时间列将用于确定记录有效的时间段。因此，我需要做的第一件事是修改Product，以满足时态数据表的要求。为此，我将运行以下代码：

ALTER TABLE dbo.Product 
    ADD CONSTRAINT PK_ProductID PRIMARY KEY (ID),
    BeginDate datetime2 GENERATED ALWAYS AS ROW START NOT NULL
        DEFAULT SYSUTCDATETIME()
,
    EndDate datetime2 GENERATED ALWAYS AS ROW END NOT NULL
        DEFAULT CAST('9999-12-31 23:59:59.9999999' AS datetime2),
    PERIOD FOR SYSTEM_TIME (BeginDate,EndDate);

在创建系统验证的时态表中有一些非常好的信息，可以将非时态表更改为系统验证时态表。

在转换现有表时，请考虑使用隐藏子句隐藏新的句点列，以避免影响设计为处理新列的现有应用程序。

设置时态表的下一步是识别与我的dbo.Product表一起的历史表。为此，我将运行以下代码：

ALTER TABLE dbo.Product
        SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ProductHistory));

现在，Product表如下所示

| ProductName    | ProductPrice | BeginDate                   | EndDate                     |
|----------------|--------------|-----------------------------|-----------------------------|
| Widget         | 33.49        | 2017-09-29 14:28:04.8327739 | 9999-12-31 23:59:59.9999999 |
| Doo-Hickey     | 21.76        | 2017-09-29 14:17:55.7149252 | 9999-12-31 23:59:59.9999999 |
| Thing-A-Ma-Jig | 20.16        | 2017-09-29 14:17:55.7149252 | 9999-12-31 23:59:59.9999999 |

您还会发现，已经创建了一个新的ProductHistory表，该表看起来与Product表类似。ProductHistory表当前为空，因为我们尚未更新或删除产品表中的任何内容

现在，让我们运行一个更新来生成一些历史

UPDATE dbo.Product 
SET ProductPrice = 34.65
WHERE ProductName = 'Widget';

select * from dbo.Product
select * from dbo.Producthistory

product表具有当前数据

| ProductName    | ProductPrice | BeginDate                   | EndDate                     |
|----------------|--------------|-----------------------------|-----------------------------|
| Widget         | 34.65        | 2017-09-29 14:35:49.7943164 | 9999-12-31 23:59:59.9999999 |
| Doo-Hickey     | 21.76        | 2017-09-29 14:35:41.8079586 | 9999-12-31 23:59:59.9999999 |
| Thing-A-Ma-Jig | 20.16        | 2017-09-29 14:35:41.8079586 | 9999-12-31 23:59:59.9999999 |

现在，ProductHistory表有一个版本化的行

| ProductName | ProductPrice | BeginDate                   | EndDate                     |
|-------------|--------------|-----------------------------|-----------------------------|
| Widget      | 33.49        | 2017-09-29 14:35:41.8079586 | 2017-09-29 14:35:49.7943164 |

您甚至可以向Product表中添加一列，并且ProductHistory表也会自动获得添加的新列。

ALTER TABLE dbo.Product ADD ProductColor VARCHAR(10)

Product

| ProductName    | ProductPrice | BeginDate                   | EndDate                     | ProductColor |
|----------------|--------------|-----------------------------|-----------------------------|--------------|
| Widget         | 34.65        | 2017-09-29 14:35:49.7943164 | 9999-12-31 23:59:59.9999999 | NULL         |
| Doo-Hickey     | 21.76        | 2017-09-29 14:35:41.8079586 | 9999-12-31 23:59:59.9999999 | NULL         |
| Thing-A-Ma-Jig | 20.16        | 2017-09-29 14:35:41.8079586 | 9999-12-31 23:59:59.9999999 | NULL         |

ProductHistory

| ProductName | ProductPrice | BeginDate                   | EndDate                     | ProductColor |
|-------------|--------------|-----------------------------|-----------------------------|--------------|
| Widget      | 33.49        | 2017-09-29 14:35:41.8079586 | 2017-09-29 14:35:49.7943164 | NULL         |

是否存在兼容性或性能的风险？

当然，您需要首先在开发环境中测试这一点。您可能会遇到影响您决定使用时态表的独特情况。

下面是一些重要的链接，并提供了有关时态表的其他信息

开始使用系统版本的时态表

时态表的考虑和限制

管理系统中历史数据的保留-可验证的时态表

停止系统。系统上的版本控制。可验证的时态表