主页 / dba / 问题 / 281352
Accepted
p.vitzliputzli
Asked: 2020-12-12 06:00:55 +0800 CST

MSSQL 中的事务时间表和约束

  • 772

我正在尝试按照 Richard Snodgrass 的书（在 SQL 中开发面向时间的数据库应用程序）在 MSSQL Server 2016 上实现事务时间表。

通过触发器具有顺序主键的表可以这样实现：

CREATE TABLE test_table (
a [tinyint] NOT NULL,
b [date] NOT NULL,
c [float] NOT NULL,
[tt_start] [datetime2](7) NOT NULL,
[tt_end] [datetime2](7) NOT NULL)
GO

CREATE TRIGGER Seq_Primary_Key_tt ON test_table FOR INSERT, UPDATE, DELETE AS
  BEGIN
    IF (( EXISTS ( SELECT * FROM test_table AS b1
    WHERE 1 < (SELECT COUNT(b2.a) FROM
        test_table AS b2
        WHERE b1.a = b2.a AND
        b1.b = b2.b AND
        b1.tt_start < b2.tt_end AND b2.tt_start < b1.tt_end) )))
    BEGIN
        RAISERROR('Transaction violates sequenced constraint', 1, 2)
        ROLLBACK TRANSACTION
    END
 END

 GO

触发器在较大的表上非常慢。因此，使用多个 INSERT 会导致等待时间过长。此外，像更新这样的许多操作需要不止一个操作（例如 INSERT，然后是 UPDATE）。这不适用于此触发器，因为触发器将在 INSERT 之后启动并失败，尽管如果触发器在 UPDATE 之后启动，该操作将完美运行。

这就是为什么 Snodgrass 写道约束/断言（或这里的触发器）必须是 DEFERRABLE INITIALLY DEFERRED。这样，将在所有操作完成后检查约束。这同时也会提高多个 INSERT 的性能。

但是，据我所知，MSSQL Server 没有实现 DEFERRABLE INITIALLY DEFERRED。然后如何实施类似的约束或触发器？或者“临时表”功能可以替代我正在尝试做的事情吗？

sql-server sql-server-2016

2 个回答

  1. 触发器在较大的表上非常慢。

    您没有任何索引，也没有将检查限制在使用 INSERTED 虚拟表的语句影响的行上。请参阅：使用插入和删除的表

    许多操作（如更新）需要不止一项操作

    在这种情况下，您最好通过存储过程来管理更新，而不是尝试使用触发器来处理所有事情。例如，如果您插入一个相邻的区间，您可能想要合并它们。

    • 2
  2. Best Answer

    从评论中，您的要求是：

    对于一个时间点，只有一行有效。

    为了强制执行这一点，我们只需要行更改/将生效的时间点。

    这是一种误解，认为为了查询和维护与时间相关的数据的完整性，我们必须有一个结束/结束日期1。除非我们定义合同的持续时间或真正的间隔，否则不需要2并且需要大量的事务逻辑来确保不插入无效行。

    对于您的情况，它看起来像列A并B形成复合主键。根据这种模式，我们会这样设置：

    /* Need an entity to maintain the parent key for any time-independent relationships */
CREATE TABLE TestEntity
(
  ColumnA  TINYINT  NOT NULL
 ,ColumnB  DATE     NOT NULL
  /* Any immutable columns would go here */
 ,CONSTRAINT PK_TestEntity PRIMARY KEY (ColumnA, ColumnB)
)
GO

CREATE TABLE TestEntityVersion
(
  ColumnA    TINYINT       NOT NULL
 ,ColumnB    DATE          NOT NULL
 ,tt_start   DATETIME2(7)  NOT NULL
 ,ColumnC    FLOAT         NOT NULL
 ,CONSTRAINT FK_TestEntityVersion_VersionOf_TestEntity FOREIGN KEY (ColumnA, ColumnB) REFERENCES TestEntity (ColumnA, ColumnB)
 ,CONSTRAINT PK_TestEntityVersion PRIMARY KEY (ColumnA, ColumnB, tt_start)
)
GO

    为了在某​​个时间点获得实体的全貌，我们将使用以下查询：

    SELECT
  E.ColumnA
 ,E.ColumnB
 ,EV.tt_start
 ,EV.ColumnC
FROM
  TestEntity E
LEFT JOIN
  TestEntityVersion EV
    ON EV.ColumnA = E.ColumnA
        AND EV.ColumnB = E.ColumnB
        AND EV.tt_start=
          (
            SELECT
              MAX(tt_start)
            FROM
              TestEntityVersion
            WHERE
              ColumnA = E.ColumnA
                AND ColumnB = E.ColumnB
                AND tt_start <= '2020-12-12 13:56:23.2352342'
          )

    主键保证将返回一行。 不需要额外的约束/触发器/功能。

    对于旧版本的 SQL Server（我相信 2014 和更早版本），上述查询将导致两次搜索TestEntityVersion（尽管数据通常是从磁盘读取一次）。较新的版本将只执行一次查找，返回与操作符最近的行TOP。无论哪种情况，只要数据正确规范化并且表格保持窄，我发现性能是可以接受的。

    如果人们真的，真的，真的必须有一个结束日期，你应该派生它并将其仅用于显示目的。这可以通过窗口函数轻松完成，并且可以合并到视图中：

      SELECT
    ColumnA
   ,ColumnB
   ,tt_start
   ,LEAD(tt_start,1,'9999-12-31 23:59:59.9999999')
      OVER 
       (
         Partition BY 
           ColumnA
          ,ColumnB 
         ORDER BY 
           tt_start
       ) AS tt_end
   ,ColumnC
  FROM
    EntityVersion

    其他注意事项

    可能会有额外的要求，而不是强加的必要条件，例如：

    1. 如果值与以前的版本相比没有变化，则不要插入该行
    2. 如果存在具有更大日期时间值的行，则不要插入行

    通常这些可以通过您的存储过程逻辑来处理，但是如果您将有几个过程将行插入到表中，您可以构建一个函数并使用它来强制执行约束。 Anchor Modeling有一些很好的例子（尽管该特定使用模式的其余部分很糟糕）。

    这些关于 SO 的答案也可能对您有益，就像对我一样：

    存储时间序列数据，关系型还是非关系型？

    历史/可审计数据库

    1试图解决这个主题的书籍作者（包括 Hugh Darwen 和 CJ Date，遗憾的是）在概念化这个特定问题空间方面做得如此糟糕，这让我有点莫名其妙，迫使一切都进入间隔而不是点的心态 -及时。导致许多不必要的工作以确保间隔是连续的并且不重叠。他们的“解决方案”的实施总是导致查询性能低于标准和插入不必要的开销。

    2对于这种情况，我们会将规则合并到我们的插入/更新过程中。

    • 1

相关问题