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

if you must maintain order, copy the data into a duplicate table, then rebuild the table to keep your column order, then copy the data from the duplicate table back in.

Just be sure to do this when there is no activity going on.