Capture RowVersion on Insert

Question

My tables have a RowVersion column called LastChanged.

ID | LastChanged | Foo |

I am developing some sync related functionality. I will be selecting all records from the table between a min and max RowVersion. The initial sync won't have a Min Row Version so I will be including all rows upto MIN_ACTIVE_ROWVERSION().

Subsequent syncs will have a min RowVersion - typically it will be the MIN_ACTIVE_ROWVERSION() from the previous sync.

Selecting rows that are between the Min and Max RowVersion like this is easy. However I would also like to determine, which of those rows, are Inserts and which rows are Updates. The easiest way for me to do this, is to add another column:

ID | LastChanged (RowVersion) | CreationRowVersion (Binary(8)) | Foo |

For CreationRowVersion - The idea is to capture the RowVersion value on insert. That value will then never change for the row. So I would like to default CreationRowVersion to the same value as RowVersion when the row is initially Inserted.

With this in place, I should then be able to determine which rows have been created, and which rows have been updated since the last sync (i.e between min and max RowVersions) - because for created rows, I can look at rows that have a CreationRowVersion that fall within the min and max row version range. For Updated Rows, I can look at rows that have a LastChanged that fall within min and max row version range - but I can also exclude rows from being detected as "Updates" if their CreationRowVersion also falls between min and max RowVersions as then I know they are actually already included as Inserts.

So now that the background is out of the way, it brings me to the crux of my question. What is the most efficient way to default CreationRowVersion to the RowVersion on Insert? Can this be done with a default constrain on the column, or does it have to be done via a trigger? I'd like this column to be a Binary(8) as this matches the datatype of RowVersion.

Thanks

I have accepted @Brett's solution as the answer, because, even though it doesn't actually capture each individual row's unique creation version (i.e RowVersion at the time of Insert for that row) - it captures the the Min Active Row Version at the time of creation - which can be assigned to multiple rows being inserted - and may even relate to the rowversion of row being inserted in another transaction? - but none the less, this is enough for me to detect new inserts given a row version range - which is all I needed :-) — Darrell, Sep 22 '17 at 11:44

Dave Mason · Accepted Answer · 2017-09-21T21:08:39.353

Try using the MIN_ACTIVE_ROWVERSION() function as the default value for your CreationRowVersion BINARY(8) column.

CREATE TABLE dbo.RowVerTest (
    ID INT IDENTITY,
    LastChanged ROWVERSION,
    CreationRowVersion BINARY(8)
        CONSTRAINT DF_RowVerTest_CreationRowVersion DEFAULT(MIN_ACTIVE_ROWVERSION()),
    Foo VARCHAR(256)
)
GO

INSERT INTO dbo.RowVerTest (Foo) VALUES ('Hello');
GO

--[LastChanged] and [CreationRowVersion] should be equal.
SELECT * FROM dbo.RowVerTest;
GO

UPDATE dbo.RowVerTest SET Foo = 'World' WHERE ID = 1;
GO

--[LastChanged] should be incremented, while [CreationRowVersion] 
--should retain its original value from the insert.
SELECT * FROM dbo.RowVerTest;
GO

CAUTION: in my testing, the above only works when rows are inserted one at a time. The code for the scenario below does not appear to work for your use case:

--Insert multiple records with a single INSERT statement.
INSERT INTO dbo.RowVerTest (Foo)
SELECT TOP(5) name FROM sys.objects;

--All the new rows have the same value for [CreationRowVersion] :{
SELECT * FROM dbo.RowVerTest;

Yes upon reading this suggestion, I was about to say, If I have two concurrent transactions, wont MIN_ACTIVE_ROWVERSION() return the same value in each of them... :-(. I'm just thinking through if that will actually cause a problem for my change detection logic.. Because I think what is being captured in that case, for all the rows, they should all have the minimum Min_Active_RowVersion - i.e the RowVersion for the first inserted / modified row. I think this may be enough for me to detect inserts! I will experiment! — Darrell, Sep 22 '17 at 11:32

score 1 · Answer 2 · answered Sep 21 '17 at 18:08

1

There is an existing question about referencing columns in a default statement. You can't do it, but there are other suggestions to look at, including an AFTER INSERT trigger.

You may want to take a look at this question on RowVersion and Performance.

answered Sep 21 '17 at 18:08

Brett

1,540
9
13

Ok Thanks. The `After Insert `trigger is definitely an option, but obviously its quite a big performance hit to consider so i would prefer to avoid if possible. Also I would have to first Insert the row, then do a subsequent Update of the row in the trigger - meaning the RowVersion would increment twice, once when the Row was inserted, and then again when I update the row to set CreationRowVersion=RowVersion. Still workable but not ideal :-(. RE: – Darrell Sep 22 '17 at 11:28
RE: RowVersion and Performance, thanks - yes full table scans are to be avoided. Luckily I have an index into the table by UserId in this case, so I can avoid full table scanning. – Darrell Sep 22 '17 at 11:29

Capture RowVersion on Insert

2 Answers2