3

According to the rowversion docs

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.

however this 'increment' skips an integer when looping back from FF to 01. e.g.

0x00000000000007FF
0x0000000000000801

To reproduce, create a table 

CREATE TABLE [dbo].[TestTable](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[SomeData] [varchar](200) NOT NULL,
[RowVersion] [rowversion]  not null
) ON [PRIMARY]

Now add some inserts:

DECLARE @i INT = 0

WHILE @i < 256
BEGIN
    SET @i = @i + 1

  INSERT INTO [TestTable] ([SomeData]) VALUES (CONVERT(VARCHAR(255), NEWID()))    
END

view the data:

select * from [TestTable] order by [RowVersion] asc

Data will vary depending on whether you have used rowversion We see in this case 2047 (0x00000000000007FF) jumps to 2049 (0x0000000000000801)

Why is this?

enter image description here

wal
  • 17,409
  • 8
  • 74
  • 109
  • @MitchWheat yes possibly but not in this case. its running in isolation. freshly created database with a single sql query window. one caveat is that given this is database wide there may be some *internal* table with a rowversion that gets an update - this seems unlikely – wal Mar 16 '18 at 00:17
  • Hmm, one interesting thing to note is that when it reaches a multiple of 256, before inserting, if you check the `MIN_ACTIVE_ROWVERSION()` it will tell you `0x...00` (meaning it's theoretically possible) but when you do something like an insert, it skips that number and goes straight to `0x...01` – ZLK Mar 16 '18 at 00:39

2 Answers2

2

The referenced documentation mentions the rowversion value is unique, binary, and incrementing. It doesn't say there won't be gaps. The value is intended to be used for optimistic concurrency validation so gaps should not matter for that purpose.

Dan Guzman
  • 43,250
  • 3
  • 46
  • 71
0

That is internal implementation of rowversion (timestamp). That implementation allows gaps by design.

For example, let's see how DB Backup/Restore affects it.

The value of largest rowversion is stored at database boot page. That value is not changed when every new rowversion is generated. Instead, SQL engine reserves a few thousands of row versions and updates it, e.g. from 20,000 to 24,000. In case when you restore the database or if DB is offline, the range 20,000..24,000 is lost. The new row version will start from 24,000

So your code should never rely on assumption there is no gaps.

You can check the last saved value of rowverion:

DBCC TRACEON (3604);
DBCC DBINFO ('db-name')

and look for 'dbi_maxDbTimestamp'

Anton
  • 2,846
  • 1
  • 10
  • 15
  • Anton this does not answer the question. - whether you should rely on it is not relevant here (and yes i agree you should not) - why would (and that is the question) the internal implementation increment by 2 (instead of 1) when arriving at the 'end' of the hexidecimal range – wal Mar 16 '18 at 03:12
  • @wal, it is incremented by two only when DB is online. If there is outage or db restore, it may be incremented by a value up to a few thousands, you never know that, there is no such rule +1 for 1..255 range and +2 for 256. MS designed that the increment may be variable. I don't know specific details why in that case it is exactly +2... we can only guess it is reserved for some internal usage. What we know is that this 'problem' is not new, it's mentioned in a few books at least since 2003. – Anton Mar 16 '18 at 03:29
  • Also increment is variable when there is logshipping, mirroring or Always On – Anton Mar 16 '18 at 04:18