Sign up ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from Mar 21 '12 at 14:52

This question came from our site for professional and enthusiast programmers.

I don't think you can avoid it - the correct rowversion value has to be applied to every row in the table, before it can start accepting updates. – Damien_The_Unbeliever Mar 20 '12 at 19:34
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. – Michael J Swart Mar 20 '12 at 19:38
1. Create a new table and migrate data to it. 2. Take a momentary downtime to rename tables so that new table becomes old table. 3. Run a sync check to get any last minute data changes. 4. Come out of downtime. – Robert L Davis Mar 20 '12 at 19:41

3 Answers 3

up vote 17 down vote accepted

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 just work as normal. 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.

share|improve this answer
Nice one. That's so crazy it just might work. – Michael J Swart Mar 21 '12 at 18:52
Those craptacular execution plans are the key. Easier said than done, but if we can avoid them and be sure that we can avoid them then this method is great. – Michael J Swart Mar 22 '12 at 17:20
deletes don't work as normal - they need to delete from both tables. – usr Apr 16 '12 at 21:34
@usr Great point! – Brent Ozar Apr 16 '12 at 22:49
Implemented and turned this into a series of blog posts (come see!)… – Michael J Swart May 11 '12 at 20:14

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.

share|improve this answer
Interesting, I guess I didn't specify what "too long" meant in my question. If >30 minutes is too long for your scenario and 10 minutes is tolerable, this solution would work. My scenario involved trying to achieve zero downtime or more specifically < 10 seconds which is achieved by Brent's answer. – Michael J Swart Jan 29 at 14:19
Concur. We're a bit bound by both the storage space of the original table (over a TB), and the fact that it's replicated. – Scott Lynch Jan 30 at 16:19

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.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.