CODEWITHSUNDEEP
sqlsql-serversql-server-2008calculated-columnsalter
ab_732
ab_732

Reputation: 3897

Alter a column to be calculated SQL SERVER

I have a table similar to this one:

CREATE TABLE [dbo].[test](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [action] [varchar](50),
    [id_source] [int] NULL,
    [id_dest] [int] NULL,
    [name] [varchar](255)
)

I am trying to convert the name column to be calculated. I tried something similar to this with no success

ALTER TABLE [dbo].[test] 
ALTER COLUMN [name] AS ([dbo].[f_get_name]([id_source],[id_dest],[action]))

The table at the moment is empty. Is it possible to achieve this with an ALTER command? Thanks

Upvotes: 2

Views: 3941

Answers (2)

Rahul Tripathi
Rahul Tripathi

Reputation: 172548

Is it possible to achieve this with an ALTER command?

No there is no way to achieve it by altering the table. The ALTER table command will restrict you to do so since your column involves computation.

Solution:

You need to drop the column and recreate it again like this:

alter table [dbo].[test] 
drop column [name];

alter table [dbo].[test] 
add [name] as ([dbo].[f_get_name]([id_source],[id_dest],[action]));

Upvotes: 2

Lukasz Szozda
Lukasz Szozda

Reputation: 175964

You can't alter it.

ALTER COLUMN

Specifies that the named column is to be changed or altered.

The modified column cannot be any one of the following:

A column with a timestamp data type.

The ROWGUIDCOL for the table.

A computed column or used in a computed column.

You need to drop and recreate:

ALTER TABLE [dbo].[test]
DROP COLUMN [name];

ALTER TABLE [dbo].[test]
ADD [name] AS ([dbo].[f_get_name]([id_source],[id_dest],[action]));

Upvotes: 3

Related Questions