CODEWITHSUNDEEP
sqlsql-server
Bill Heine
Bill Heine

Reputation: 161

SQL - How to ALTER COLUMN on a computed column

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.

Upvotes: 16

Views: 20482

Answers (5)

jack.mike.info
jack.mike.info

Reputation: 128

its easy to overcome divide by zero error

use

SELECT
( 100 / NULLIF( 0, 0 ) ) AS value

it will return a null, if 0 is in that column,

instead of alter go for update by using the above example

Also read the 3rd normalization for computed column

Upvotes: -5

Jason
Jason

Reputation: 11

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.

Upvotes: 1

Manas Sahu
Manas Sahu

Reputation: 41

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.

Upvotes: 0

KM.
KM.

Reputation: 103637

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?

Upvotes: 0

Nick Craver
Nick Craver

Reputation: 630569

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.

Upvotes: 22

Related Questions