Tell me more ×
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.
up vote 8 down vote favorite
1

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
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

migrated from stackoverflow.com Mar 21 '12 at 14:52

1 Answer

up vote 6 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
@MichaelJSwart yeah, 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. ;-) – Brent Ozar Mar 25 '12 at 11:56
Yeah, so turns out we're going to skip the view, and not delete from the original table. We get more stable plans. The trade off is that we need to hold essentially two copies of the table, but that's not bad. – Michael J Swart Mar 27 '12 at 12:24
1  
@usr Great point! – Brent Ozar Apr 16 '12 at 22:49
show 2 more comments

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.