Sql-server – Alternate ways to increase column size

alter-tabledeploymentsql server

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?

Best Answer

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.

Related Question