SQL - How to ALTER COLUMN on a computed column

Question

I'm working with SQL Server 2008. Is it possible to alter a computed column without actually dropping the column and then adding it again (which I can get to work)? For example, I have this table:

CREATE TABLE [dbo].[Prices](
[Price] [numeric](8,3) NOT NULL,
[AdjPrice] AS [Price] / [AdjFactor],
[AdjFactor] [numeric](8,3) NOT NULL)

Later realizing that I have a potential divide by zero error I want to alter the [Adjprice] column to handle this, but if I just drop the column and add it again, I lose the column order.

I want to do something like:

ALTER TABLE dbo.[Prices]
ALTER COLUMN [AdjPrice] AS (CASE WHEN [AdjFactor] = 0 THEN 0 ELSE [Price] / [AdjFactor] END)

But this isn't correct. If this is possible, or there is another solution, I would appreciate the help.

Answer 1

Unfortunately, you cannot do this without dropping the column first.

From MSDN:

ALTER COLUMN
Specifies that the named column is to be changed or altered. ALTER COLUMN is not allowed if the compatibility level is 65 or lower. For more information, see sp_dbcmptlevel (Transact-SQL).

• The modified column cannot be any one of the following:

  • A computed column or used in a computed column.

Answer 2

NO

if it is computed, what is the big deal dropping it and adding it again? is it PERSISTED and there are million of rows?

Answer 3

I do not think you can alter this column with out dropping. So drop the colum then add new column.

If you find out any other way to do this please tell me..

My blog: http://expertdevelopersblog.blogspot.com/