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 -9223372036854775808
if 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]