[SOLVED] Retrieving new rowversion after INSERT using @@DBTS

Retrieving new rowversion after INSERT using @@DBTS - is it safe?

It is common practice to pick up the newly created IDENTITY of a table using the @@IDENTITY variable after an INSERT.

Is it just equality correct to retrieve the last rowversion value following an UPDATE in a similar manner using the @@DBTS value?

For example:

IF(OBJECT_ID('XXX') IS NOT NULL)
    DROP TABLE XXX
GO
CREATE TABLE XXX
(
    ID int IDENTITY(1,1) PRIMARY KEY,
    Name varchar(64) NOT NULL,
    RV rowversion
)
GO
INSERT INTO XXX(Name) VALUES 
    ('Apples'),('Bananas'),('Cranberries'),('Dragon Fruit'),('Eggplant'),('Fig'),('Grape')
GO
SELECT * FROM XXX
GO
UPDATE XXX
    SET Name = 'Chocolate' WHERE ID = 3
PRINT @@DBTS
GO

Now is @@DBTS safe from concurrent updates?

If another connection performs insert and updates between the UPDATE and the PRINT, would we end up with the rowversion of the 'other' connection rather than the one from our own update?

Solution

According to MSDN @@DBTS

returns the last-used timestamp value of the current database

This means it is not thread safe.

You also should not use @@IDENTITY. @@IDENTITY and SCOPE_IDENTITY return the last identity value generated in any table in the current session. However, SCOPE_IDENTITY returns the value only within the current scope; @@IDENTITY is not limited to a specific scope. @@IDENTITY can for example return the wrong value if a trigger gets executed on a table with an identity column.