Increase a counter for each changed row

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]