Online Schema Modification

Question

I have a table called BigData on SQL Server. BigData is modified by an app continuously and can't be taken offline even for a second. Another requirement is that the table can't be locked exclusively by some other process other than the app accessing it. The question is; how can I change one of the columns of BigData from Bigint to int? any suggestions?

Answer 1

I also recently had a question very similar to this during a PayPal interview. This situation is common with a bank or e-commerce company which has a live database which is constantly in use.

The answer I gave was to:

1. Create a new table based on the BigData table which looks identical except that the column has the new type.
2. Create a trigger which will fire every time an update or insert is made to the old table. The trigger will update/insert appropriately to the new table
3. Start copying all records from the old table to the new table which are older than the time the trigger goes live. This step will be overlapping as the trigger covers the new records.
4. Once all the old records have successfully been copied to the new table, we can expect that the new table now has all legacy records, plus any new records which were inserted/updated since we started the migration process.

After doing this, the new and old tables should have identical content, and both should be in sync with the running application. Now, the old table can be dropped, and the new table can be renamed to the old one. It may be necessary for a brief outage (say a few minutes) to achieve this, but this should be acceptable.