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?