Can we alter the computed column in SQL Server

Can we alter a computed column in SQL Server?

The answer is- NO.

A calculated column is basically a virtual column that isn’t physically stored in the table, and its underlying expression cannot be changed. You’ll have to start anew and throw it out.

CREATE TABLE dbo.Products (
ProductID int IDENTITY (1,1) NOT NULL ,
QtyAvailable smallint ,
UnitPrice money ,
InventoryValue AS QtyAvailable * UnitPrice );

Now you cannot modify the above specified computed column using ALTER command. It will throw an error. Hence you’ll need to drop the column and recreate it.

ALTER TABLE dbo.Products DROP COLUMN InventoryValue;
ALTER TABLE dbo.Products ADD InventoryValue AS QtyAvailable * (UnitPrice -10);

Hope you find this article helpful.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s