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)
4 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… :)