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?
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.
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!! :)]
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
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