how can a row version be less than a previous one in a later insert in a later batch

asked 2020-09-22T21:19:50.85+00:00
db042190 1,416 Reputation points

Hi we run 2014 developer where this occurred.

i'm looking at evidence that a row version in my most recent set of inserts has a lower value than that of an insert from a previous batch. i thought that was impossible.

arent row versions always supposed to be increasing? is it possible someone ran something that reset them?

Azure SQL Database
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
8,483 questions
No comments
{count} votes

Accepted answer
  1. answered 2020-09-23T06:46:17.867+00:00
    Miamiao-MSFT 4,216 Reputation points

    Hi @db042190 ,

    arent row versions always supposed to be increasing?

    Yes.The rowversion data type is an incrementing number.
    And you should note this : Duplicate rowversion values can be generated by using the SELECT INTO statement in which a rowversion column is in the SELECT list. We do not recommend using rowversion in this manner.

    is it possible someone ran something that reset them?

    No. It is not possible to reset them. Because they are generated automatically by rowversion counter.

    Quote from rowversion-transact-sql
    It is a data type that exposes automatically generated, unique binary numbers within a database. rowversion is generally used as a mechanism for version-stamping table rows. The storage size is 8 bytes. The rowversion data type is just an incrementing number and does not preserve a date or a time.

    BR,
    Mia


    If the answer is helpful, please click "Accept Answer" and upvote it.

    No comments

10 additional answers

Sort by: Most helpful
  1. answered 2020-09-23T00:45:26.06+00:00
    Mike Ubezzi 2,771 Reputation points

    Hi @db042190 - Please see the remarks for rowversion (Transact-SQL) where

    "Every time that a row with a rowversion column is modified or inserted, the incremented database rowversion value is inserted in the rowversion column. This property makes a rowversion column a poor candidate for keys, especially primary keys. Any update made to the row changes the rowversion value and, therefore, changes the key value."

    In the case of a single host (Windows Server instance running on-premise) where the host is never to rarely bounced (full restart) and any counters or uniqueidentifier (Transact-SQL) will see the value increase and stay within a consistent number or block range. However, in a hosted environment where your PaaS Azure SQL (Logical) Server is deployed as a node to a large cluster, there are multiple compute resource that are made available to run your Azure SQL Database and because the rowversion and uniqueidentifier functions rely on functionality tied to physical hardware resources, the corresponding values that are generated will not always be sequential or part of the same block or range.

    Please let me know if you have additional questions or require additional information.


  2. answered 2020-09-23T07:02:29.553+00:00
    Olaf Helper 25,571 Reputation points

    my most recent set of inserts has a lower value than that of an insert from a previous batch. i thought that was impossible

    The row version values isn't set once on insert, it gets modify also on every update of the data. Means, one update the data of previous batch data after you insert your data.


  3. answered 2020-09-23T09:57:04.687+00:00
    db042190 1,416 Reputation points

    thx all. i dont see an answer s far, at least not to my question.

    my understanding is that an insert will always use an rv higher than any rv used so far in that db. and a change will reassign the value previously used to an rv higher than ANY previously used RV in that db. i'm going to post evidence that that isnt true, at least in my case. this is not a hosted environment.


  4. answered 2020-09-23T10:23:10.303+00:00
    Simon Sabin 1 Reputation point

    The other point to note is that the value is generated at the time of insertion, not at the time of commiting. If you can have two inserts running in parallel and the one that finishes last could have values lower than in the other insert.

    This is why you have to be very careful about using timestamp for delta detection, as timestamp values might be assign but not having been committed.

    No comments