Convert computed column to regular column

Question

I have a persisted computed column in a large table in in SQL Server 2005.

I want to convert it to a regular column, keeping current values.

Do I have to recreate the column and update the entire table in transaction, or is it possible to just alter a computed column specification, and how to do it?

Answer 1

-- Create a new Column (unpersisted):
ALTER TABLE MyTable
   ADD newColumn DatatypeOfPersistedColumn
GO

UPDATE myTable
SET newColumn = PersistedColumn
GO

-- Delete the persisted column
ALTER TABLE MyTable
   DROP COLUMN PersistedColumn
GO

-- Rename new column to old name
EXEC sp_rename 'MyTable.newColumn', 'PersistedColumn', 'COLUMN'
GO