Take the 2-minute tour ×
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 stackoverflow.com Mar 21 '12 at 14:52

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

2  
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

1 Answer 1

up vote 8 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
1  
deletes don't work as normal - they need to delete from both tables. –  usr Apr 16 '12 at 21:34
1  
@usr Great point! –  Brent Ozar Apr 16 '12 at 22:49
1  
Implemented and turned this into a series of blog posts (come see!) michaeljswart.com/2012/04/… –  Michael J Swart May 11 '12 at 20:14

Your Answer

 
discard

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.