CODEWITHSUNDEEP
sqlsql-servert-sqlrowversion
user1501171
user1501171

Reputation: 230

SQL RowVersion incrementing before data has been augmented in Trigger

I've just started using T-SQL's RowVersion and I've noticed something very interesting. I created a Proc to update a table with a RowVersion column and at the end of this proc I print out a humanreadable date representation of RowVersion. I also added a Trigger which updates data in the table and prints out RowVersion before and after. It's given some interesting results...

From Insert Proc after data is inserted = 1900-01-01 05:43:13.373

From start of Trigger before we change any data = 1900-01-01 05:43:13.377

From end of trigger after data has changed = 1900-01-01 05:43:13.377

The start of the trigger value for the RowVersion column has changed from the end of the proc value for the RowVersion Column despite the fact we have not augmented any data yet. It's also the same as the end of the Trigger. Why is this? Is it something to do with the sequencing of SQL statement execution? Any help understanding this would be appreciated. The proc and trigger are listed below:

CREATE PROCEDURE [dbo].[TestRowVersionProc]
AS
    INSERT INTO TestRowversion (ID, Number)
    VALUES ('6', '6')

    DECLARE @rv DATETIME 
    SELECT @rv = GenerationNumber FROM TestRowversion WHERE ID = '6'
    SELECT CONVERT(VARCHAR(50), @rv , 121)
GO


CREATE TRIGGER [dbo].[TestRowversion_AfterInsertUpdate] ON [dbo].[TestRowversion] AFTER INSERT,UPDATE
AS
    if @@rowcount = 0 return
    set nocount on

    DECLARE @rv DATETIME 
    SELECT @rv = GenerationNumber FROM TestRowversion WHERE ID = '6'
    SELECT CONVERT(VARCHAR(50), @rv , 121)

    DECLARE @Id NVARCHAR(1) 
    SELECT @Id = ID FROM inserted

    UPDATE TestRowversion
    SET Number = 'FromTrigger'
    WHERE ID = '6'

    SELECT @rv = GenerationNumber FROM TestRowversion WHERE ID = '6'
    SELECT CONVERT(VARCHAR(50), @rv , 121)

GO

Upvotes: 0

Views: 1482

Answers (2)

Ian Boyd
Ian Boyd

Reputation: 257047

The order of execution is, using some actual timestamp values:

| Event time                | Event time                  | RowVersion |
|---------------------------|-----------------------------|------------|
| Start of trigger          | 2017-12-05 10:50:26.8506034 | 28,804,001 |
|  End  of trigger          | 2017-12-05 10:50:26.8506034 | 28,804,002 |
| After insert              | 2017-12-05 10:50:26.8662212 | 28,804,002 |

You can turn the rowversion into a human readable form with:

SELECT CAST(GenerationNumber AS bigint) FROM TestRowVersion --timestamp is an 8-byte (64-bit) integer

Your mental model of the ordering was wrong.

Table: 

CREATE TABLE TestRowVersion (
    ID varchar(50), 
    Number varchar(50),
    GenerationNumber timestamp
)

Inserting procedure:

ALTER PROCEDURE [dbo].[TestRowVersionProc]
AS
    INSERT INTO TestRowversion (ID, Number)
    VALUES ('6', '6')

    DECLARE @rv bigint
    SELECT @rv = CAST(GenerationNumber AS bigint) FROM TestRowversion WHERE ID = '6'
    SELECT CAST(SYSDATETIME() AS varchar(50))+' After insert    '+CAST(@rv AS varchar(50))
GO

Trigger:

CREATE TRIGGER [dbo].[TestRowversion_AfterInsertUpdate] ON [dbo].[TestRowversion] AFTER INSERT,UPDATE
AS
    if @@rowcount = 0 return
    set nocount on

    DECLARE @rv bigint
    SELECT @rv = CAST(GenerationNumber AS bigint) FROM TestRowversion WHERE ID = '6'
    SELECT CAST(SYSDATETIME() AS varchar(50))+' Start of trigger    '+CAST(@rv AS varchar(50))

    DECLARE @Id NVARCHAR(1) 
    SELECT @Id = ID FROM inserted

    UPDATE TestRowversion
    SET Number = 'FromTrigger'
    WHERE ID = '6'

    SELECT @rv = CAST(GenerationNumber AS bigint) FROM TestRowversion WHERE ID = '6'
    SELECT CAST(SYSDATETIME() AS varchar(50))+' End of trigger  '+CAST(@rv AS varchar(50))

Upvotes: 0

Stanislav Kundii
Stanislav Kundii

Reputation: 2894

https://learn.microsoft.com/en-us/sql/t-sql/data-types/rowversion-transact-sql

  1. The rowversion data type is just an incrementing number and does not preserve a date or a time.

  2. Each database has a counter that is incremented for each insert or update operation that is performed on a table that contains a rowversion column within the database. This counter is the database rowversion. This tracks a relative time within a database, not an actual time that can be associated with a clock.

  3. DATETIME Accuracy Rounded to increments of .000, .003, or .007 seconds

Upvotes: 0

Related Questions