15

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.

David Oneill
  • 12,502
  • 16
  • 58
  • 70
Bill Heine
  • 151
  • 1
  • 1
  • 3
  • 2
    column order is totally irrelevant in SQL Server anyway - why bother preserving it?? Drop and re-create is the only way to go. – marc_s Feb 16 '10 at 16:50
  • 1
    If you are writing your queries such that they depend on column order, please stop. – Alison R. Feb 16 '10 at 18:32
  • 3
    Sometimes colum order needs to be preserved. for example if your customer uses bcp to import data and uses a format file. Then the format file needs to be changed too and that needs to be remembered all the time if you make any further update on the customer site. – endo64 Mar 05 '13 at 18:18
  • @marc_s Column order is _not_ irrelevant. SQL Server handles table updates very differently if you are adding/dropping a column in the middle of the column list or at the end: it could mean hours of delay. Stupid, but intensely irritating behavior. Definitely impedes production updates even for minor corrections. – Suncat2000 Oct 22 '19 at 19:01

5 Answers5

22

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.
Nick Craver
  • 623,446
  • 136
  • 1,297
  • 1,155
  • You can however preserve the calculate column data by using a temporary table [as demonstrated in this answer here](http://stackoverflow.com/a/23134891/1193596). – Amicable Apr 17 '14 at 13:47
  • @Amicable that's not a *computed* column anymore, so it's a totally different case and set of restrictions. – Nick Craver Apr 17 '14 at 13:50
1

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.

Jason
  • 11
  • 1
0

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?

KM.
  • 101,727
  • 34
  • 178
  • 212
0

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.

Bobby
  • 11,419
  • 5
  • 44
  • 69
Manas Sahu
  • 41
  • 2
-5

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

ste2425
  • 4,656
  • 2
  • 22
  • 37
jack.mike.info
  • 128
  • 3