Aumente um contador para cada linha alterada

Estou usando o SQL Server 2008 Standard, que não possui um SEQUENCErecurso.

Um sistema externo lê dados de várias tabelas dedicadas do banco de dados principal. O sistema externo mantém uma cópia dos dados e verifica periodicamente se há alterações nos dados e atualiza sua cópia.

Para tornar a sincronização eficiente, desejo transferir apenas as linhas que foram atualizadas ou inseridas desde a sincronização anterior. (As linhas nunca são excluídas). Para saber quais linhas foram atualizadas ou inseridas desde a última sincronização, há uma bigintcoluna RowUpdateCounterem cada tabela.

A ideia é que sempre que uma linha for inserida ou atualizada, o número em sua RowUpdateCountercoluna mudará. Os valores que vão para a RowUpdateCountercoluna devem ser retirados de uma sequência crescente de números. Os valores na RowUpdateCountercoluna devem ser exclusivos e cada novo valor armazenado em uma tabela deve ser maior que qualquer valor anterior.

Consulte os scripts que mostram o comportamento desejado.

Esquema

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

INSERIR algumas linhas

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

Resultado esperado

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

Os valores gerados em RowUpdateCounterpodem ser diferentes, digamos, 5, 3, 7, 9. Eles devem ser únicos e devem ser maiores que 0, pois partimos de uma tabela vazia.

INSERT e UPDATE algumas linhas

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

Resultado esperado

+----+-------+------------------+
| ID | Value | RowUpdateCounter |
+----+-------+------------------+
|  1 | A     |                1 |
|  2 | B     |                2 |
|  3 | E     |                5 |
|  4 | F     |                6 |
|  5 | G     |                7 |
|  6 | H     |                8 |
+----+-------+------------------+

• RowUpdateCounterpara linhas com ID 1,2devem permanecer como estão, porque essas linhas não foram alteradas.
• RowUpdateCounterpara linhas com ID 3,4devem mudar, porque foram atualizados.
• RowUpdateCounterpara linhas com ID 5,6devem mudar, porque foram inseridas.
• RowUpdateCounterpara todas as linhas alteradas deve ser maior que 4 (o último RowUpdateCounterda sequência).

A ordem na qual os novos valores ( 5,6,7,8) são atribuídos às linhas alteradas realmente não importa. Os novos valores podem ter lacunas, por exemplo 15,26,47,58, mas nunca devem diminuir.

Existem várias tabelas com esses contadores no banco de dados. Não importa se todos eles usam uma única sequência global para seus números, ou se cada tabela tem sua própria sequência individual.

Não quero usar uma coluna com carimbo de data e hora em vez de um contador inteiro, porque:

• O relógio no servidor pode avançar e retroceder. Principalmente quando está em uma máquina virtual.

• Os valores retornados pelas funções do sistema como SYSDATETIMEsão os mesmos para todas as linhas afetadas. O processo de sincronização deve ser capaz de ler as alterações em lotes. Por exemplo, se o tamanho do lote for de 3 linhas, após a MERGEetapa acima, o processo de sincronização lerá apenas linhas E,F,G. Quando o processo de sincronização for executado na próxima vez, ele continuará da linha H.

A maneira como estou fazendo isso agora é bastante feia.

Como não há SEQUENCEno SQL Server 2008, emulo o SEQUENCEpor uma tabela dedicada com IDENTITYconforme mostrado nesta resposta . Isso em si é muito feio e exacerbado pelo fato de que preciso gerar não um único, mas um lote de números de uma vez.

Em seguida, tenho um INSTEAD OF UPDATE, INSERTgatilho em cada tabela com os RowUpdateCounterconjuntos de números necessários e gero lá.

Nas consultas INSERT, UPDATEe MERGEdefino RowUpdateCountercomo 0, que é substituído pelos valores corretos no gatilho. As ???nas consultas acima são 0.

Funciona, mas existe uma solução mais fácil?