Alternate ways to increase column size

Question

I have a database that is in production, and in some instances will have millions of records. It has been requested that we increase the sizes of several columns, which is for the most part pretty easy. But in one instance, we have a computed column that depends on the column I need to resize. I've come up with this SQL script to make the change:

/**** Drop the index so we can drop and recreate the column ****/
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[my_table]') AND name = N'column_index')
DROP INDEX [column_index] ON [dbo].[my_table] WITH ( ONLINE = OFF )
GO

ALTER TABLE my_table DROP COLUMN computed_column
ALTER TABLE my_table ALTER COLUMN main_column NVARCHAR(100)
ALTER TABLE my_table ADD computed_column AS (dbo.ComputeColumn(main_column)) PERSISTED
GO

/**** Recreate the index ****/
CREATE NONCLUSTERED INDEX [column_index] ON [dbo].[my_table] ([computed_column] ASC)
GO

The problem is, besides instances where there are millions of rows and it will likely create a heavy load on the DB server; I have to be able to run this script while the code is still being used. If I drop the computed column, then any code that references that column will fail until it's recreated.

So I've been asked to find an alternate way to increase the column size of main_column without impacting computed_column. Can that be done?

Answer 1

If the queries that are accessing the table that need to work while you're making this change are read-only, you could create a view that has the original column and uses the function in place of the computed column, then in a transaction rename the table and create a synonym with the original name that points at the view, then make your changes, start another transaction where you drop the synonym and rename the table, then finally drop the view. If the table needs to be read-write in this time, this may not be sufficient.

This isn't strictly doing what you asked (changing main without affecting computed), but I still think it has the desired effect - minimal impact on users.

Answer 2

The following approach has low risk:

• Make sure that all the modules accessing the table are well covered with automated tests.
• Create a new table. Alter all procedures that modify the old table, so that they modify both old and new tables.
• Migrate existing data into new structure. Do it in smallish batches, so that it does not seriously impact the overall performance on the server.
• Verify that the migration of data succeeded.
• Redirect some of the selecting procedures from the old table to the new ones. Use automated tests to make sure that the changed modules are still correct. Make sure their performance is acceptable. Deploy the altered procedures.
• Repeat the previous step until all the reports use the new table.
• Change the procedures that modify the tables, so that they only access the new table.
• Archive the old table and remove it from the system.

We have used this approach many times to change large live production tables without downtime, with no issues at all.