主页 / dba / 问题 / 143846
Accepted
Vladimir Baranov
Asked: 2016-07-15 01:31:30 +0800 CST

为每个更改的行增加一个计数器

  • 772

我正在使用没有SEQUENCE功能的 SQL Server 2008 Standard。

外部系统从主数据库的几个专用表中读取数据。外部系统保留数据副本并定期检查数据的更改并刷新其副本。

为了使同步有效，我只想传输自上次同步以来更新或插入的行。（这些行永远不会被删除）。要知道自上次同步以来更新或插入了哪些行，每个表中都有一bigint列RowUpdateCounter。

这个想法是，每当插入或更新一行时，其RowUpdateCounter列中的数字就会改变。进入该RowUpdateCounter列的值应取自不断增加的数字序列。列中的值RowUpdateCounter应该是唯一的，并且存储在表中的每个新值都应该大于任何先前的值。

请查看显示所需行为的脚本。

架构

CREATE TABLE [dbo].[Test](
    [ID] [int] NOT NULL,
    [Value] [varchar](50) NOT NULL,
    [RowUpdateCounter] [bigint] NOT NULL,
CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED
(
    [ID] ASC
))
GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_RowUpdateCounter] ON [dbo].[Test]
(
    [RowUpdateCounter] ASC
)
GO

插入一些行

INSERT INTO [dbo].[Test]
    ([ID]
    ,[Value]
    ,[RowUpdateCounter])
VALUES
(1, 'A', ???),
(2, 'B', ???),
(3, 'C', ???),
(4, 'D', ???);

预期结果

+----+-------+------------------+
| ID | Value | RowUpdateCounter |
+----+-------+------------------+
|  1 | A     |                1 |
|  2 | B     |                2 |
|  3 | C     |                3 |
|  4 | D     |                4 |
+----+-------+------------------+

中生成的值RowUpdateCounter可以不同，例如5, 3, 7, 9. 它们应该是唯一的并且应该大于 0，因为我们是从空表开始的。

插入和更新一些行

DECLARE @NewValues TABLE (ID int NOT NULL, Value varchar(50));
INSERT INTO @NewValues (ID, Value) VALUES
(3, 'E'),
(4, 'F'),
(5, 'G'),
(6, 'H');

MERGE INTO dbo.Test WITH (HOLDLOCK) AS Dst
USING
(
    SELECT ID, Value
    FROM @NewValues
)
AS Src ON Dst.ID = Src.ID
WHEN MATCHED THEN
UPDATE SET
     Dst.Value            = Src.Value
    ,Dst.RowUpdateCounter = ???
WHEN NOT MATCHED BY TARGET THEN
INSERT
    (ID
    ,Value
    ,RowUpdateCounter)
VALUES
    (Src.ID
    ,Src.Value
    ,???)
;

预期结果

+----+-------+------------------+
| ID | Value | RowUpdateCounter |
+----+-------+------------------+
|  1 | A     |                1 |
|  2 | B     |                2 |
|  3 | E     |                5 |
|  4 | F     |                6 |
|  5 | G     |                7 |
|  6 | H     |                8 |
+----+-------+------------------+
  • RowUpdateCounter具有 ID 的行1,2应保持原样，因为这些行没有更改。
  • RowUpdateCounter具有 ID 的行3,4应该更改，因为它们已更新。
  • RowUpdateCounter对于具有 ID 的行5,6应该更改，因为它们已被插入。
  • RowUpdateCounter对于所有更改的行应大于 4（RowUpdateCounter序列中的最后一个）。

将新值 ( 5,6,7,8) 分配给更改的行的顺序并不重要。新值可以有间隙，例如15,26,47,58，但它们不应减少。

数据库中有几个带有此类计数器的表。不管他们是否都使用单一的全局序列作为他们的数字，或者每个表都有自己的单独序列。

我不想使用带有日期时间戳的列而不是整数计数器，因为：

  • 服务器上的时钟可以向前和向后跳跃。特别是当它在虚拟机上时。

  • 系统函数返回的值SYSDATETIME对于所有受影响的行都是相同的。同步过程应该能够批量读取更改。例如，如果批量大小为 3 行，则在上述MERGE步骤之后，同步过程将只读行E,F,G。下次运行同步过程时，它将从 row 继续H。

我现在这样做的方式相当丑陋。

由于SEQUENCESQL Server 2008 中没有，因此我SEQUENCE通过一个专用表来模拟，IDENTITY如this answer所示。这本身就很丑陋，而且由于我需要一次生成的不是一个数字，而是一批数字，这一事实更加严重。

然后，我INSTEAD OF UPDATE, INSERT在每个表上都有一个触发器，RowUpdateCounter并在那里生成所需的数字集。

在INSERT,UPDATE和MERGE查询中，我设置RowUpdateCounter为 0，它被触发器中的正确值替换。上述???查询中的 是0。

它有效，但有更简单的解决方案吗？

sql-server sql-server-2008

2 个回答

  1. Best Answer

    您可以ROWVERSION为此使用一列。

    该文件指出

    每个数据库都有一个计数器，对于在数据库中包含 rowversion 列的表执行的每个插入或更新操作，该计数器都会递增。

    这些值是BINARY(8)，您应该将它们视为，BINARY而不是BIGINT在0x7FFFFFFFFFFFFFFF它继续0x80...并开始工作之后（-9223372036854775808如果将其视为已签名bigint）。

    下面是一个完整的工作示例。如果您有大量更新，则维护ROWVERSION列上的索引会很昂贵，因此您可能希望在有无的情况下测试您的工作负载，看看是否值得付出代价。

    CREATE TABLE [dbo].[Test]
  (
     [ID]               [INT] NOT NULL CONSTRAINT [PK_Test] PRIMARY KEY,
     [Value]            [VARCHAR](50) NOT NULL,
     [RowUpdateCounter] [ROWVERSION] NOT NULL UNIQUE NONCLUSTERED
  )

INSERT INTO [dbo].[Test]
            ([ID],
             [Value])
VALUES     (1,'Foo'),
            (2,'Bar'),
            (3,'Baz');

DECLARE @RowVersion_LastSynch ROWVERSION = MIN_ACTIVE_ROWVERSION();

UPDATE [dbo].[Test]
SET    [Value] = 'X'
WHERE  [ID] = 2;

DECLARE @RowVersion_ThisSynch ROWVERSION = MIN_ACTIVE_ROWVERSION();

SELECT *
FROM   [dbo].[Test]
WHERE  [RowUpdateCounter] >= @RowVersion_LastSynch
       AND RowUpdateCounter < @RowVersion_ThisSynch;

/*TODO: Store @RowVersion_ThisSynch somewhere*/

DROP TABLE [dbo].[Test]
    • 5

  2. 您是否尝试过使用该IDENTITY选项？

    例如：

    [RowUpdateCounter] [bigint] NOT NULL IDENTITY(1,2)

    在哪里

    • 1 --> 起始值
    • 2 --> 每个新行都由此递增

    这类似于 Oracle 中的 SEQUENCE。

    • -2

相关问题