Binaryworld - Working with Computed column in SQL Server 2000 ... [ SQL Server -> T-SQL ]

There are restriction for ALTER COLUMN, documented in Books Online (see end
of this post). Depending on your particular situation, you need to DROP
INDEX, ALTER TABLE... DROP CONSTRAINT etc.

The altered column cannot be:

a) A column with a text, image, ntext, or timestamp data type.

b) The ROWGUIDCOL for the table.

c) A computed column or used in a computed column.

d) A replicated column.

e) Used in an index, unless the column is a varchar, nvarchar, or
varbinary data type, the data type is not changed, and the new size is equal
to or larger than the old size.

f) Used in statistics generated by the CREATE STATISTICS statement. First
remove the statistics using the DROP STATISTICS statement. Statistics
automatically generated by the query optimizer are automatically dropped by
ALTER COLUMN.

g) Used in a PRIMARY KEY or [FOREIGN KEY] REFERENCES constraint.

h) Used in a CHECK or UNIQUE constraint, except that altering the length
of a variable-length column used in a CHECK or UNIQUE constraint is allowed.

i) Associated with a default, except that changing the length, precision,
or scale of a column is allowed if the data type is not changed.

Click here to copy the following block

--Drop table Orders
--go

Create table Orders
(
OrderId int NOT NULL PRIMARY KEY
,CustomerId int NOT NULL
,ProductId int NOT NULL
,Quantity int DEFAULT(1)
,UnitPrice money
--, Total as (Quantity * UnitPrice)
)

go

--// Add new Computed Column
Alter table Orders
ADD Total as (Quantity * UnitPrice)/100
go

--//Note: To alter a computed column drop the column and add again

--// ****** Error *************************
--Alter table Orders
-- Alter Column Total (Quantity * UnitPrice)
--/////////////////////////////////////////

Alter table Orders
Drop column Total

go

Alter table Orders
ADD Total as (Quantity * UnitPrice)
go