6

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?

lcerni
  • 61
  • 1
  • 1
  • 3

2 Answers2

6

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
Martin Smith
  • 84,644
  • 15
  • 245
  • 333
2

I believe what they are talking about is a partition SWITCH. Not really what you are looking for here. It's great for loading data into a table quickly (from the point of view of the user) but not really for an entire table.

If I were you I'd do a re-name swap.

  1. Create a new table TableName_New where the computed column is just a regular column. Make sure you keep any indexes, constraints, foreign keys, permissions etc. You may want to apply any non-clustered indexes after step 2.
  2. Load the data from the old table to the new one. If it's an active table you'll have to make a note of where you stop so you can load the remaining data after the swap.

  3. Do a rename swap

    EXEC sp_rename 'TableName','TableName_Old';
EXEC sp_rename 'TableName_New','TableName';

  4. Move the remaining rows (created between the start of step 2 and the end of step 3.) into TableName.

Kenneth Fisher
  • 24,127
  • 12
  • 61
  • 114