'How to fast-forward @@DBTS in a MS SQL Server database?
Is there a legit or a hacky way to override or "fast-forward" value of @@DBTS in SQL server?
The only way I was able to do it is to do some dummy updates to a table that has a timestamp / rowversion column in a loop, where one loop step is like:
SELECT @@DBTS -- original value
-- no-op update that increments @@DBTS nevertheless
UPDATE Table_with_a_rowversion_column SET any_other_column = any_other_column
SELECT @@DBTS -- value has incremented by a number of rows in Table_with_a_rowversion_column
I don't like this because it's slow and takes resources. I want to increment @@DBTS by many billions and do it fast - as much as 16^9. My loop script did less than 16^8 of incrementing in 30 minutes, so it'll take at least 8 hours.
Background / why anybody would want to do that?
I'm migrating data from database 1 to database 2. They have different schemas, but both 1 & 2 have a "state" table (about a million of rows) and a "state_history" table (10's of millions rows).
state table has a rowversion column.
trigger on state table inserts pre-update data into state_history table (subject to some filtering) - including captured rowversion value (saved as binary(8))
union of state and state_history sorted by the rowversion/binary(8) column is the only reliable way to sort latest & historical values in the order they actually occurred (captured system clock is not always reliable).
I want to keep same monotonically increasing rowversion invariant in target database 2, few parts of the system rely on this invariant.
I do have control over historical rowversions because it's binary(8) but they range from as little as 16^5 to as much as 16^9 i.e. there`s no large base value to subtract so I have to keep them as is.
But I don't have any control over the main target state table - SQL inserts small rowversion values based on target database's @@DBTS. I wish I could simply tell it to override it to 16^9 and then run my migration script.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
