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.
Reference: Pinal Dave (https://blog.sqlauthority.com)
6 Comments. Leave new
You can use update statement.
Update Table1
Set Col2 = Col1/10.
In this senario with out dropping the computed column we can change the formula . from the table desing computed column formul . i think by changing the computed column formula from the table desing in object explorer.
Yes in design of table, we can change the formula of a particular column
@Pinal Dave
You’ve bet a challenge and got three bullshit answers. Is this really an average knowledge of SQL Server? I mean one could not know the solution of your problem, but one of the answerers doesn’t understand what a computed column is (and does try to reply anyway) and two others don’t understand what does SSMS Designer usually do to a table… :)
@Pinal Dave, you’re right. I was thinking I could get around this problem by declaring a scalar function that provides the computed column’s value, then instead of altering the column you just change the function definition. But once I made the function (using the “WITH SCHEMABINDING” clause since that is needed for a function referenced by a computed column), I could no longer drop and re-create the function. In that situation SQL Server rejects it with a “Cannot DROP FUNCTION ‘dbo.SomeFunction’ because it is being referenced by object ‘SomeTable’. Just one more way it can’t be done unfortunately.
I expected ALTER TABLE SWITCH to maybe be able to alter the column without recreating it, but that is not possible. SWITCH requires swapped columns to have the same definition.