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

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)

Previous Post
SQL SERVER – Finding Last Backup Time for All Database – Last Full, Differential and Log Backup
Next Post
SQL SERVER – Fix – Error 1759 Computed column in table is not allowed to be used in another computed-column definition

Related Posts

No results found.

5 Comments. Leave new

  • You can use update statement.

    Update Table1
    Set Col2 = Col1/10.

    Reply
  • 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.

    Reply
  • Yes in design of table, we can change the formula of a particular column

    Reply
  • @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… :)

    Reply
  • @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.

    Reply

Leave a Reply

Menu