SQL RowVersion number columns for change tracking problem

Question

I've got a situation where I am using a RowVersion columns and a Binary(8) columns to track whether a row has been changed.

Ideally whenever:

RowVersion != Binary(8)

Then there has been a change in that record. The real problem with this is that I cannot find a good method to set the two columns to equal. If I update the Binary field, the update query increments the RowVersion field for that record. I'v messed with optimistically incrementing the Binary field, and it almost works. The key is I have to increment the Binary field by the total number of records that the UPDATE query will affect. Any idea on how to pause the rowversion, or determine what it will be at the end of an update statement to use the value IN the update statement?

For clarity, here's an example of what will work to make the two fields match:

    UPDATE [table] SET BinaryField = MyRowVersion + 
(SELECT COUNT(*) FROM [table] WHERE (MyRowVersion != BinaryField)) 
WHERE (MyRowVersion != BinaryField)

Why are you updating the binary column with the rowcount? And then expect it to synch to a rowversion column? — gbn, Jul 27 '11 at 16:04
Perhaps a little unclear. If you wanted to set the binary column to be the same value as the rowversion, you have to add the count of affected records to make them equal. — SamuelWarren, Jul 27 '11 at 16:33
rowversion is database unique: nothing to do with row counts at all. Do you have a reference for what you say please? MSDN says http://msdn.microsoft.com/en-us/library/ms182776.aspx "Is a data type that exposes automatically generated, unique binary numbers within a database." and "The rowversion data type is just an incrementing number" — gbn, Jul 27 '11 at 16:47
So, besides the rowversion field, you want another field that you set to store (now, or at some time point) the same value as the rowversion field? So, when you later compare these two, you'll know if there was an update of that row? — ypercubeᵀᴹ, Jul 27 '11 at 19:54
I am trying to simply track changed records. If the records have been changed then the two columns will not match. If they have not been changed, then the two columns will be equal. After I've queried all the changes, I need to make the columns equal so I can find out what changes next. — SamuelWarren, Jul 27 '11 at 19:58

score 2 · Answer 1 · answered Jul 27 '11 at 19:23

2

I would use binary field on separate table that holds last rowversion value and put a after trigger on the table to sync those. Then join these two tables and compare them if they are same.

answered Jul 27 '11 at 19:23

THEn

1,920
3
28
35

I thought of that. I do need to implement this on multiple tables though, which poses a problem (unless a create a sync table for every single one of them). – SamuelWarren Jul 27 '11 at 19:48

score 2 · Accepted Answer · answered Jul 27 '11 at 20:24

I'm not very sure that there is no better way to do this, but here's an option:

1.Create another table with only the PK of your table and a binary(8) field.

2.Copy the rowversion from the rows of your table to the respecting rows at the second table (at the time points you want).

3.Then, when you can later compare these two fields (rowversion, binary(8)).

--- 1 ---
CREATE TABLE MyTest 
( myKey INT PRIMARY KEY
, myData INT
, RV rowversion
) ;

CREATE TABLE MyTestCheck
( myKey INT PRIMARY KEY
, RVcheck binary(8)
, FOREIGN KEY (myKey) REFERENCES MyTest(myKey)
) ;

--- 2 ---
UPDATE MyTestCheck 
SET RVcheck = RV
FROM MyTest 
WHERE MyTest.myKey = MyTestCheck.myKey ;

INSERT INTO MyTestCheck
  SELECT myKey, RV
  FROM MyTest
  WHERE myKey NOT IN
    ( SELECT myKey
      FROM MyTestCheck
    ) ;

--- 3 ---
SELECT m.*, m2.RVcheck 
FROM MyTest AS m
  LEFT JOIN MyTestCheck AS m2
    ON m2.myKey = m.myKey 
WHERE m2.RVcheck <> m.RV          --- updates since last time procedure2 run
   OR m2.RVcheck IS NULL ;        --- inserts since  ...

You could check for deletes, using a FULL JOIN and having dropped the Foreign Key constraint.

SQL RowVersion number columns for change tracking problem

2 Answers2