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.

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

    Reply

Leave a Reply

Menu