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

Question

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?

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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.

Answer 5

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.