ALTER TABLE SWITCH

I have two columns that I want to convert from computed to non computed. I have the code for that. However, it was suggested to me to use the ALTER TABLE SWITCH. I believe the individual who suggested it, didn't want to put the database in downtime. I am thinking they wanted something like this
1) Create new file group
2) Create new duplicate table which has computed columns on new file group
3) Alter the computed columns to non computed columns
4) Switch the database to use this new file group/table
5) Drop the old file group/table with the computed columns

However, when I research the ALTER TABLE SWITCH command it seems to me that this scenario isn't the correct usage of this.

Is there a way to use ALTER TABLE SWITCH to change computed columns to non computed columns?

ALTER TABLE ... SWITCH can sometimes be useful for this type of thing.

Specifically it can allow a large table with an IDENTITY column to be switched into a different table with the same structure but no IDENTITY column thus (after a drop and a rename) effectively allowing the IDENTITY property be toggled as a metadata change (example).

With the tables below

CREATE TABLE T1
(
Foo int,
Bar as CAST(''  AS CHAR(100)) PERSISTED
)

CREATE TABLE T2
(
Foo int,
Bar  CHAR(100) NULL
)

The T2 table metadata would be compatible with the physical layout of the T1 table so it would be nice to do a metadata only switch but it doesn't work in reality.

ALTER TABLE T1 SWITCH TO T2

Msg 4965, Level 16, State 1, Line 17 ALTER TABLE SWITCH statement
failed. Column 'Bar' in table 'tempdb.dbo.T1' is computed column but
the same column in 'tempdb.dbo.T2' is not computed.

Nor does the following work

ALTER TABLE T1
  ALTER COLUMN Bar CHAR(100) NULL

Cannot alter column 'Bar' because it is 'COMPUTED'.

If keeping the table online is the main priority then you might want to consider.

• Adding two nullable columns that will eventually replace the computed columns (metadata only change).

• Updating the new columns in small batches (to avoid long held or escalated locks) until the entire table is done.

• Begin Transaction at repeatable read

• Take shared table lock

• Final synchronisation for any rows where the new columns' value needs updating as it is already out of date since the batch processing.

• Drop computed columns

• Rename replacements

• Commit transaction

StackExchange Database Administrators Q#144218, answer score: 6