Getting latest rowversion/timestamp value in update statement - Sql Server

Question

I'm using rowversion columns for handling optimistic concurrency and want to get the new rowversion value back when I've done an update so that my data layer has the latest value and can perform another update with getting a concurrency exception (unless the record has been update by someone else).

I was just doing a get in the data layer after doing an update but this wasn't very efficient or perfectly reliable.

For the following table:

CREATE TABLE PurchaseType
(
    PurchaseTypeCode nvarchar(20) NOT NULL PRIMARY KEY CLUSTERED (PurchaseTypeCode),
    Name nvarchar(50) NOT NULL,
    TS rowversion NOT NULL
)

I tried:

CREATE PROCEDURE PurchaseType_UpdateWithGet
@PurchaseTypeCode nvarchar(20),
@Name nvarchar(50),
@TS rowversion OUTPUT    
AS

UPDATE PurchaseType
SET Name = @Name
WHERE PurchaseTypeCode = @PurchaseTypeCode
 AND TS = @TS

SELECT @TS = TS FROM PurchaseType WHERE PurchaseTypeCode = @PurchaseTypeCode
GO

but wasn't entirely happy because of the possibility of not getting the rowverion value from someone else's update. Then I came across the OUTPUT statement in rowversion documentation (http://msdn.microsoft.com/en-us/library/ms182776.aspx / http://msdn.microsoft.com/en-us/library/ms177564.aspx) and tried this:

CREATE PROCEDURE PurchaseType_UpdateWithOutput
@PurchaseTypeCode nvarchar(20),
@Name nvarchar(50),
@TS rowversion OUTPUT    
AS

DECLARE @Output TABLE (TS BINARY(8))

UPDATE PurchaseType
SET Name = @Name
    OUTPUT inserted.TS into @Output
WHERE PurchaseTypeCode = @PurchaseTypeCode
    AND TS = @TS

SELECT TOP 1 @TS = TS FROM @Output
GO

This works well. In my very basic tests (just running 10000 calls and timing it) the OUTPUT option takes about 40% longer but still less than half a millisecond. Neither took any measurable time with SET STATISTICS TIME ON.

My question is, does anyone know of a better/simpler way to do this?

I had hoped for a function I could use similar to SCOPE_IDENTITY() for identity columns but can't find anything like that. Anyone know if I'm missing something?

Thanks in advance.

Answer 1

From MSDN:

@@DBTS returns the last-used timestamp value of the current database. A new timestamp value is generated when a row with a timestamp column is inserted or updated.