SQL SERVER – Unable to ALTER Computed Column in SQL Server – How to ALTER Computed Column

SQL Server gives you no direct way to ALTER computed column definitions in a table. A Very common question, I often receive is

Q: “How to ALTER computed columns?”

A: There is NO way to alter computed column. You will have to drop and recreate it.

Here is a demonstration of it.

If you try to alter the computed column it will throw following error.

-- Create table
CREATE TABLE Table1 (Col1 INT, Col2 AS Col1*10);
-- Failed attempt to alter column
ALTER TABLE Table1
ALTER COLUMN Col2 AS Col1/10;

Above script will give following error:

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword ‘AS’.

The best way to fix this is to drop the column and recreate it.

-- Drop Column
ALTER TABLE Table1
DROP COLUMN Col2;
-- Create Column
ALTER TABLE Table1
ADD Col2 AS Col1/10; 

Let me know if there is any other way of altering the column without dropping it.

Steps I Follow When I Need to ALTER Computed Column Logic

Since the only route is to drop the column and add it again, I treat it as a small change script, not two quick statements. First, I look at the current formula in sys.computed_columns, which shows the definition and whether the column is persisted. I save that text somewhere, just in case I need to go back.

Next, I check what depends on the column. SQL Server will not let you drop it while an index, a constraint, or a schema bound view or function still uses it. Those objects have to be dropped first and created again once the new column is in place.

Then I run the drop and the add inside one transaction, so nobody sees the table without the column. Two more things to remember:

• The recreated column goes to the end of the table, so code that depends on column order, or uses SELECT *, may see the columns in a different order.
• A computed column that is not persisted is only a definition, so it is quick to add. A persisted one has to be calculated and stored for every row, which can take time and log space on a big table.

Finally, run a quick SELECT on a few rows to confirm the new formula returns what you expect. If the column had an index, make sure you created it again, because the queries that used it will slow down without it.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.