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

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

ALTER TABLE [Tablename]


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.

-

migrated from stackoverflow.comMar 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