8

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

Expected result

+----+-------+------------------+
| ID | Value | RowUpdateCounter |
+----+-------+------------------+
|  1 | A     |                1 |
|  2 | B     |                2 |
|  3 | E     |                5 |
|  4 | F     |                6 |
|  5 | G     |                7 |
|  6 | H     |                8 |
+----+-------+------------------+
  • RowUpdateCounter for rows with ID 1,2 should remain as is, because these rows were not changed.
  • RowUpdateCounter for rows with ID 3,4 should change, because they were updated.
  • RowUpdateCounter for rows with ID 5,6 should change, because they were inserted.
  • RowUpdateCounter for all changed rows should be greater than 4 (the last RowUpdateCounter from the sequence).

The order in which new values (5,6,7,8) are assigned to changed rows doesn't really matter. The new values can have gaps, e.g. 15,26,47,58, but they should never decrease.

There are several tables with such counters in the database. It doesn't matter if all of them use the single global sequence for their numbers, or each table has its own individual sequence.


I don't want to use a column with a datetime stamp instead of an integer counter, because:

  • The clock on the server can jump both forward and backward. Especially when it is on a virtual machine.

  • The values returned by system functions like SYSDATETIME are the same for all affected rows. The sync process should be able to read changes in batches. For example, if batch size is 3 rows, then after the MERGE step above the sync process would read only rows E,F,G. When the sync process is run next time it would continue from row H.


The way I'm doing it now is rather ugly.

Since there is no SEQUENCE in SQL Server 2008, I emulate the SEQUENCE by a dedicated table with IDENTITY as shown in this answer. This in itself is pretty ugly and exacerbated by the fact that I need to generate not a single, but a batch of numbers at once.

Then, I have an INSTEAD OF UPDATE, INSERT trigger on each table with the RowUpdateCounter and generate required sets of numbers there.

In the INSERT, UPDATE and MERGE queries I set RowUpdateCounter to 0, which is replaced by the correct values in the trigger. The ??? in the queries above are 0.

It works, but is there an easier solution?

  • 4
    Could you use row version / timestamp? It's a binary field but the value will change every time the row is updated – James Z Jul 14 '16 at 10:06
  • @JamesZ, I need to know the order in which the rows were changed. The sync process reads the MAX Counter from the outdated copy of the table and then it know to fetch only rows that have Counter more than that value. The rowversion wouldn't give me this possibility, if I correctly understand what it is... Is it guaranteed to be ever increasing? – Vladimir Baranov Jul 14 '16 at 10:28
  • 1
  • Thank you @MartinSmith, I completely forgot about rowversion. It looks very tempting. My only concern is that all examples of its use that I've seen so far revolve around detecting whether a single row changed. I need an efficient way of knowing what set of rows changed since a certain moment. Besides, is it possible to miss an update? – Vladimir Baranov Jul 14 '16 at 11:53
  • @MartinSmith time=0: last rowversion value is, say, 122. time=1: Transaction A updates a row, its rowversion changes to 123, A is not committed yet. time=2: Transaction B updates another row, its rowversion changes to 124. time=3: B commits. time=4: sync process runs and fetches all rows with rowversion > 122, which means rows updated only by B. time=5: A commits. Result: changes by A will never be picked up by the sync process. Am I wrong? Maybe some clever use of MIN_ACTIVE_ROWVERSION will help? – Vladimir Baranov Jul 14 '16 at 12:01
5

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] 
| improve this answer | |
  • Thank you. After reading the docs I think that instead of @@DBTS there should be MIN_ACTIVE_ROWVERSION(), and if using MIN_ACTIVE_ROWVERSION() comparison <= should become < and > become >=. – Vladimir Baranov Jul 15 '16 at 11:51
  • According to the docs there is a material difference between @@DBTS and MIN_ACTIVE_ROWVERSION() if there are active uncommitted transactions. If an application uses @@DBTS rather than MIN_ACTIVE_ROWVERSION, it is possible to miss changes that are active when synchronization occurs. – Vladimir Baranov Jul 15 '16 at 12:14
  • @VladimirBaranov - yep, agreed, edited. – Martin Smith Jul 15 '16 at 13:33
-2

Have you tried using the IDENTITY option?

For example:

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

where

  • 1 --> Starting value
  • 2 --> each new row is incremented by this

This is similar to SEQUENCE in Oracle.

| improve this answer | |
  • SQL Server doesn't have any "AUTOINCREMENT option" – Martin Smith Jul 14 '16 at 11:19
  • yes. It is supported by Access. SQL server supports IDENTITY option. I have updated my reply above. Thanks !! – Bibhuti Bhusan Padhi Jul 14 '16 at 11:35
  • 4
    IDENTITY doesn't do what is required regarding automatically incrementing on both updates and inserts. – Martin Smith Jul 14 '16 at 11:36
  • @BibhutiBhusanPadhi, I need to know what rows have been updated. I don't see how simple IDENTITY can help. – Vladimir Baranov Jul 14 '16 at 12:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.