Increase a counter for each changed row

I'm using SQL Server 2008 Standard, which doesn't have a SEQUENCE feature.

An external system reads data from several dedicated tables of the main database.
External system keeps a copy of data and periodically checks for changes in the data and refreshes its copy.

To make the sync efficient I want to transfer only rows that were updated or inserted since the previous sync. (The rows are never deleted).
To know which rows were updated or inserted since the last sync there is a bigint column RowUpdateCounter in each table.

The idea is that whenever a row is inserted or updated, the number in its RowUpdateCounter column would change.
The values that go into the RowUpdateCounter column should be taken from an ever increasing sequence of numbers.
Values in the RowUpdateCounter column should be unique and each new value stored in a table should be greater than any previous value.

Please see the scripts that show the desired behaviour.

Schema

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 some rows

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

Expected result

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

The generated values in RowUpdateCounter can be different, say, 5, 3, 7, 9. They should be unique and they should be greater than 0, since we started from empty table.

INSERT and UPDATE some rows

```
DECLARE @NewValues TABLE (ID int NOT NULL, Value va

You can use a ROWVERSION column for this.

The documentation states that


Each database has a counter that is incremented for each insert or
update operation that is performed on a table that contains a
rowversion column within the database.

The values are BINARY(8) and you should consider them as BINARY rather than BIGINT as after 0x7FFFFFFFFFFFFFFF it goes on to 0x80... and starts working up from -9223372036854775808if treated as a signed bigint.

A full worked example is below. Maintaining the index on the ROWVERSION column will be expensive if you have lots of updates so you might want to test your workload both with and without to see if it is worth the cost.

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]

StackExchange Database Administrators Q#143846, answer score: 5