Copy ROWVERSION Value to Another Column on Insert in SQL Server

sql server

While pondering this question Is there a function like min_active_rowversion for Identity?, I thought about another possible solution for my problem – using ROWVERSION as primary key (and monotonically increasing sequence number).

Of course nobody thinks that's a good idea as ROWVERSION changes all the time, on updates to the row.

I wonder though, if it's possible to copy the ROWVERSION value to another column on insert. This column would not be updated on update and could thus be used as primary key and sequence number?

Best Answer

I do not think that there is a clean solution to add the rowversion value in the same insert statement. Here are some reasons as to why I would think that

You can create a Binary(8) column and store the MIN_ACTIVE_ROWVERSION() in there, but among other issues, multiple inserts will fail.

CREATE TABLE dbo.ID_Test_rowver
(
Rowver rowversion NOT NULL,
EmpName nvarchar(50),
Rowver2 binary(8) PRIMARY KEY NOT NULL
);

INSERT INTO ID_Test_rowver(EmpName,Rowver2)
VALUES('Emp3',CONVERT(BIGINT,MIN_ACTIVE_ROWVERSION()));

Validating the values

Rowver              EmpName Rowver2
0x00000000000007E3  Emp3    0x00000000000007E3

Run an update

UPDATE dbo.ID_Test_rowver 
SET EmpName = 'Emp4'

Validating

Rowver              EmpName Rowver2
0x00000000000007E4  Emp4    0x00000000000007E3

Inserting multiple records is going to fail

INSERT INTO dbo.ID_Test_rowver(EmpName,Rowver2)
SELECT EmpName, MIN_ACTIVE_ROWVERSION()
FROM  dbo.ID_Test_rowver;

Violation of PRIMARY KEY constraint 'PK__ID_Test___51A71CFE5E24148F'. Cannot insert duplicate key in object 'dbo.ID_Test_rowver'. The duplicate key value is (0x00000000000007e9).

Because it is calculated once per set, not per row.

An after insert trigger to update the column

CREATE TRIGGER RowverTrigger
ON dbo.ID_Test_rowver
AFTER INSERT 
AS
UPDATE RT
SET RT.Rowver2 = I.RowVer
FROM dbo.ID_Test_rowver RT
INNER JOIN INSERTED I
ON I.RowVer = RT.Rowver;

But you would not be able to do that with a primary key constraint unless you have a unique placeholder value.

CREATE TABLE dbo.ID_Test_rowver
(
Rowver rowversion NOT NULL,
EmpName nvarchar(50),
Rowver2 binary(8) 
);

And, as a side effect of this update, the rowversion column is updated again.

Rowver              EmpName Rowver2
0x00000000000007EF  Emp3    0x00000000000007ED

Changing the query to do an insert + Update in would get the same end result as the trigger.

BEGIN TRANSACTION
INSERT INTO dbo.ID_Test_rowver(EmpName)
VALUES('Emp5'),('Emp6')

UPDATE RT
SET RT.Rowver2 = RT.RowVer
FROM dbo.ID_Test_rowver RT
Where Rowver2 IS NULL
COMMIT TRANSACTION

Another option would be Row by row inserts with an instead of trigger.

CREATE TRIGGER RowverTrigger
ON dbo.ID_Test_rowver
INSTEAD OF INSERT 
AS
BEGIN
        DECLARE @count INT ;
        DECLARE @counter INT ;

        SELECT  @count = COUNT(*) ,
                @counter = 0
        FROM    inserted ;

        SELECT  * ,

                ROW_NUMBER() OVER ( ORDER BY ( SELECT   1
                                             ) ) AS TriggerRowNumber
        INTO    #inserted
        FROM    inserted ;
        WHILE @counter < @count 
            BEGIN
                INSERT  INTO dbo.ID_Test_rowver
                        ( EmpName ,
                          Rowver2
                        )
                        SELECT  EmpName ,
                                @@DBTS + 1
                        FROM    #inserted
                        WHERE   TriggerRowNumber = @counter + 1 ;
                SET @counter = @counter + 1 ;
            END
 END

Based on Listing 9 from this source

Note that I had to use @@DBTS + 1 here, because MIN_ACTIVE_ROWVERSION() was not deemed active inside the loop.

Among other considerations, performance of this will be very slow depending on the amount of inserted records.

When considering these examples, you would have to choose between

  • Not being able to do multi value inserts.
  • Row by row inserts.
  • Not having a primary key.

Those choises are really not the choices I would like to make.

Not to say that somebody more knowledgable than me might give you a better solution

Related Question