CODEWITHSUNDEEP
t-sql
Darrell
Darrell

Reputation: 2175

Capture RowVersion on Insert

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

Upvotes: 1

Views: 1633

Answers (2)

Dave Mason
Dave Mason

Reputation: 4936

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;

Upvotes: 2

Brett
Brett

Reputation: 1550

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.

Upvotes: 1

Related Questions