19

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?

Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
George Polevoy
  • 7,450
  • 3
  • 36
  • 61

5 Answers5

22
-- 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
MJS
  • 71
  • 6
Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
7

The poster wants to keep the name of the column. I have added a line at the end of Mitch's code to do a rename after dropping the PersistedColumn

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

UPDATE myTable
SET newColumn = PersistedColumn

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

-- Rename the new column to the old name
EXEC sp_rename 'MyTable.newColumn', 'PersistedColumn', 'COLUMN'
Machavity
  • 30,841
  • 27
  • 92
  • 100
IDisposable
  • 1,858
  • 22
  • 22
3

@Mitch Wheat's solution works great. However, occasionally this will throw an error with 'Invalid Column name: newColumn' because the table has not been updated before it tries to run the update.

To fix this, add a GO statement to separate the two into batches:

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

GO

UPDATE myTable
SET newColumn = PersistedColumn

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

-- Rename new column to old name
EXEC sp_rename 'MyTable.newColumn', 'PersistedColumn', 'COLUMN'
Varun Mathur
  • 898
  • 8
  • 11
2

Assuming that the reason for converting the computed column into a "real" column is that you want to keep the existing values/functionality, but add the ability to override it where desired, you could add a new column (to be populated only where the existing derived value is to be overridden), and change the definition of the computed column to be COALESCE(NewColumn, Old Calculation Definition ) .

-1

Just remove the formula from "Computed Column Specifications" in desing mode of the table in SSMS. The values will stay in column as it is.

subhash
  • 276
  • 1
  • 1
  • This way management studio actually recreates the tables, and reinserts the values. It's a large database in production, so it does not work for me. – George Polevoy Nov 10 '10 at 12:24