How can I add a rowversion column to a large table with minimal downtime

Question

Using SQL Server 2008 and later, I want to add a rowversion column to a large table however when I simply

ALTER TABLE [Tablename]
ADD Rowversion [Rowversion] NOT NULL

Then the table is unavailable for updates for too long.

What strategies can I use to reduce this downtime? I'll consider anything. The simpler the better of course, but I'll consider any strategy.

My thinking is that as a last resort, I could maintain a copy staging table maintained by triggers and then sp_rename the staging table into the original table. But I'm hoping for something simpler/easier.

Answer 1

Consider creating a new table with the same schema plus the rowversion column, and add a view atop both tables that does a union all. Have people use the view, and write instead-of triggers against the underlying tables & views.

Inserts should be sent to the new table, updates should move data to the new table, and deletes should be applied to both tables.

Then do batch moves in the background, moving as many records at a time as you can over to the new table. You can still have concurrency issues while this is going on, and some craptacular execution plans, but it lets you stay online while the moves are happening.

Ideally, you start the process on a Friday afternoon to minimize the effect on end users, and try to get it done before Monday morning. Once it's in place, you can change the view to point to just the new table, and the craptacular execution plans go away. Ideally.

To avoid the triggers firing when the data is being migrated in batches, look at the number of rows in the deleted/inserted tables in the trigger, and skip the activities if they're near to the number of rows in your batch.

---

In the finish, Michael decided to skip the view (and not delete from the original table) to get more stable plans. The trade off was holding essentially two copies of the table. He turned it into a series of blog posts.

Answer 2

If you have time to plan ahead, there's a much easier solution... (usually)

The long locks are almost certainly caused by page splits at the storage layer. So force them on your own schedule.

1. Add a NULL-able temporary column with datatype VARBINARY(8).
2. Find available slack time in the database to update batches of the existing records with a valid value for the field. (0x0000000027F95A5B for example)
3. The updates will force the necessary page splits and allocate more space to the table.
4. When you're caught up, drop the temporary column (doesn't touch the allocated storage) and add the rowversion column.
5. No page splits, and a lock that's needed only long enough to populate the values.

I've used this successfully to add a rowversion column to a 150M row table in under 10 minutes.

Caveat... if you have a table with large varchar fields (especially varchar(max)) SQL Server decides to rebuild the table instead of re-using the newly available space. Still trying to figure out a way around that one.

Answer 3

This is (very nearly) possible in SQL Server 2022.

You will be able to add a nullable rowversion column as a metadata-only operation.

This will give you NULL for existing rows and the usual rowversion values for new and updated rows.

The new functionality is currently only accessible via undocumented global trace flag 4085. It's obviously not for production use until officially released and supported.

Answer 4

If the TIMESTAMP you are adding is NULLABLE:

1. Add a VARBINARY(8) column
2. Populate with data.

After it is populated, in back to back SQL statements, DROP the VARBINARY(8) column you just added and populated, and add the TIMESTAMP NULL column.

---

If the TIMESTAMP you are adding is NOT NULLABLE:

1. Add a BINARY(8) column
2. Populate with data.

After it is populated, in back to back SQL statements, DROP the BINARY(8) column you just added and populated and ADD THE TIMESTAMP NOT NULL column.