Search code examples
sqlsql-serveralter-tablealter

SQL Server : alter column with fix value

I have a table with this column

ALTER TABLE TestTable ADD TestColumn AS '1'

How do I change it to 2?

ALTER TABLE TestTable ALTER COLUMN TestColumn AS '2'

doesn't work.

Incorrect syntax near the keyword 'AS'.

Removing and adding the column is not an option.

Solution

  • Since it is constant column, you should recreate it:

    ALTER TABLE TestTable DROP COLUMN TestColumn;
ALTER TABLE TestTable ADD TestColumn AS '2';

    ALTER TABLE ... ALTER COLUMN ... syntax is not allowed with <computed_column_definition> - see specification.

    ALTER COLUMN allows only following:

    ALTER COLUMN column_name   
{   
    [ type_schema_name. ] type_name   
        [ (   
            {   
               precision [ , scale ]   
            }   
        ) ]   
    [ COLLATE collation_name ]   
    [ NULL | NOT NULL ] 
}