1

Typically to expose version data you'd have to add a column of type rowversion, but this operation would take quite a while on a large table. I did it anyway in a dev sandbox environment, and indeed it took a while, but I also noticed that the column was populated with some meaningful-looking initial value. I expected it to be all 0's or 1's to indicate that each row is in some sort of "initial" state (after all, there was no history before this), but what I saw were what looked like accurate values for each row (they were all different, non-default-looking values).

Where did they come from? It seems like the rowversion is being tracked behind the scenes anyway, regardless of whether you've exposed it in a column. If so, can I get at it directly without adding the column? Like maybe some kind of system function I can call directly? I really want to avoid downtime, and I also have a huge number of existing queries so migration to a different table/view/combo is not an option (as suggested in other related questions).

gzak
  • 3,590
  • 4
  • 27
  • 50

1 Answers1

1

The rowversion value is generated when a table with a rowversion (a.k.a timestamp) value is modified. The rowversion value is database-scoped and the last generated value can be retrieved via @@DBTS.

Since the value is incremented only when a rowversion table is modified, I don't think you'll be able to use @@DBTS to avoid the downtime.

Dan Guzman
  • 35,410
  • 3
  • 30
  • 55
  • But then how was it able to initialize the column with seemingly "right" values when I added the column to an existing table? Seems like `rowversion` is being generated and tracked even before the column exists, otherwise how can it know what to use as the default value for the new column? – gzak Oct 31 '17 at 00:25
  • @gzak - `SELECT CAST(@@DBTS AS bigint)` gives you the current value of this global database counter. When you add a new `rowversion` column it will just populate the values incrementing from there. – Martin Smith Oct 31 '17 at 07:56