CODEWITHSUNDEEP
sql-serveralter-table
YonahW
YonahW

Reputation: 16540

SQL Server Alter Computed Column

Does anyone know of a way to alter a computed column without dropping the column in SQL Server. I want to stop using the column as a computed column and start storing data directly in the column, but would like to retain the current values.

Is this even possible?

Upvotes: 8

Views: 11272

Answers (3)

harpo
harpo

Reputation: 43188

If you need to maintain the name of the column (so as not to break client code), you will need to drop the column and add back a stored column with the same name. You can do this without downtime by making the changes (along the lines of SQLMenace's solution) in a single transaction. Here's some pseudo-code:

begin transaction
   drop computed colum X
   add stored column X
   populate column using the old formula
commit transaction

Upvotes: 2

SQLMenace
SQLMenace

Reputation: 135111

Not that I know of but here is something you can do

add another column to the table update that column with the values of the computed column then drop the computed column

Upvotes: 10

Craig
Craig

Reputation: 12032

Ok, so let me see if I got this straight. You want to take a column that is currently computed and make it a plain-jane data column. Normally this would drop the column but you want to keep the data in the column.

  1. Make a new table with the primary key columns from your source table and the generated column.
  2. Copy the data from your source table into the new table.
  3. Change the column on your source table.
  4. Copy the data back.

No matter what you do I am pretty sure changing the column will drop it. This way is a bit more complex but not that bad and it saves your data.

[Edit: @SqlMenace's answer is much easier. :) Curse you Menace!! :)]

Upvotes: 1

Related Questions