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)
7 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.
it is easy to give out easy examples see if you can fix this: then i will call you sql expert.
CREATE TABLE SalesCalculations
(
RepID INT PRIMARY KEY NOT NULL,
RepName nvarchar(50) NULL,
City nvarchar(50) NULL,
Sales FLOAT NULL,
ComRatePercnt FLOAT NULL,
Commission AS (ComRatePercnt*Sales)/100,
TaxRate FLOAT NULL,
SubTotal AS (TaxRate*Subtotal)/100, —
Tax FLOAT,
TotalPay AS SubTotal-Tax,
);