How the rowversion datatype works when adding and deleting columns

For years, I had thought (and was probably taught in SQL.AlongTimeAgoInAPlaceFarFarAway) that the timestamp column (well before rowversion was a thing,) was not guaranteed to be an ever increasing value. But this is not the case.

This makes it useful for determining rows that have changed, because it it automatic and the user cannot override the value in the column. However, there is a major concern when you use rowversions, and that is what happens when you change the structure of the table, and expect the consumer to see that change. The problem is that when you change the structure of the table, the rowversion will not be set (except when adding a new rowversion column.)

So let's create a new database and a set of tables:

CREATE DATABASE TestRowVersion; GO USE TestRowVersion; GO

Now, lets check the status of the two functions that we can use to see the status of the rowversion values:

SELECT @@DBTS AS DBTS, --the last rowversion that has been used --the next rowversion that will be used MIN_ACTIVE_ROWVERSION() AS MIN_ACTIVE_ROWVERSION;

This returns and will always on a new database, at least always has every time I have ever built a new db and checked (which over the years is more often than I care to remember.)

So, if you are using this value in some form of ETL, this is something you will need to be aware of, particularly for columns that are declared as NOT NULL. You may need to tweak the rowversion values, using a query such as:

UPDATE dbo.TestRowversion1 SET NewNotNullColumn = NewNotNullColumn;

Note that if your software keeps the lastrowversion per table, and not at the full database level, you might just set that value back to the start of time 0x0000000000000000, which will perform a lot better! In fact, for a very large table, you may need to do a "chunked" update, just updating rows where the rowversion value is still NULL.

This is, from a performance standpoint, expectable. You would not expect that they would want to change every single row in the table when adding or deleting a column. Especially for a nullable column I was not surprised, that the rowversion stayed the same. It is however, just something you need to realize when using rowversions for ETL (it would be the same if you created your own time based ETL datetime value as well).

Finally, what about when you add the RowVersion column to the table? This one is a bit more obvious than the previous case, since it obviously needs to grab a value to add it, but it never hurts to check it out.