71

I have a computed column created with the following line:

alter table tbPedidos 
add restricoes as (cast(case when restricaoLicenca = 1 or restricaoLote = 1 then 1 else 0 end as bit))

But, now I need to change this column for something like:

alter table tbPedidos 
alter column restricoes as (cast(case when restricaoLicenca = 1 or restricaoLote = 1 or restricaoValor = 1 then 1 else 0 end as bit))

But it's not working. I'm trying to input another condition to the case statement, but it's not working.

Thanks a lot!

Leniel Maccaferri
  • 100,159
  • 46
  • 371
  • 480
André Miranda
  • 6,420
  • 20
  • 70
  • 94
  • 2
    _What_ is not working? Are you getting an error? Is it not doing what you are expecting? If so, what _are_ you expecting? – Oded Mar 03 '10 at 15:47

4 Answers4

104

Something like this:

ALTER TABLE dbo.MyTable
DROP COLUMN OldComputedColumn

ALTER TABLE dbo.MyTable
ADD OldComputedColumn AS OtherColumn + 10

Source

Leniel Maccaferri
  • 100,159
  • 46
  • 371
  • 480
  • 2
    What if you have a computed column in the middle of all columns? Won't this `DROP + ADD` cause the column to be added at the end of the table (and edit all other columns' `ORDINAL` position?) – Dai Jun 18 '22 at 08:57
82

If you're trying to change an existing column, you can't use ADD. Instead, try this:

alter table tbPedidos alter column restricoes as (cast(case when restricaoLicenca = 1 or restricaoLote = 1 or restricaoValor = 1 then 1 else 0 end as bit))

EDIT: The above is incorrect. When altering a computed column the only thing you can do is drop it and re-add it.

Michael Todd
  • 16,679
  • 4
  • 49
  • 69
5

This is one of those situations where it can be easier and faster to just use the diagram feature of SQL Server Management Studio.

  1. Create a new diagram, add your table, and choose to show the formula column in the diagram's table view.
  2. Change the columns formula to an empty string ('') or something equally innocuous (probably such that you don't change the column's datatype).
  3. Save the diagram (which should save the table).
  4. Alter your function.
  5. Put the function back in the formula for that column.
  6. Save once again.

Doing it this way in SSMS will retain the ordering of the columns in your table, which a simple drop...add will not guarantee. This may be important to some.

Tim Lehner
  • 14,813
  • 4
  • 59
  • 76
  • 2
    This is a useful solution. I want to qualify that when working with very large tables, millions to billions of rows, this will cause a table rebuild. This could take HOURs to perform, depending on the width of the table and types of data stored (MAX, for example). In order to avoid a table rebuild, it's necessary to drop and add; with the premise that all SQL queries to the table are not done with queries that exact field order is specific (ie. INSERT INTO, without field names) – SnapJag Oct 09 '18 at 16:38
  • @SnapJag - something very similar happened at my company because they had a bad setting in their deploy add adding a column in the table definition not at the end cause a full rebuild. The important thing here is that you should NEVER care about column ordinality. If you want to display in a certain order, then modify your select. You should NEVER be using SELECT * anyway. This answer is supporting bad development habits (although it is technically correct) – Anthony Hancock Jun 20 '22 at 15:03
1

Another thing that might be helpful to someone is how to modify a function that's a calculated column in a table (Following query is for SQL):

ALTER <table>
DROP COLUMN <column>

ALTER FUNCTION <function>
(
<parameters>
)
RETURNS <type>
BEGIN
...
END

ALTER <table>
ADD <column> as dbo.<function>(parameters)

Notes:

  1. Parameters can be other columns from the table

  2. You may not be able to run all these queries at once, I had trouble with this. Run them one at a time

  3. SQL automatically populates calculated columns, so dropping and adding won't affect data (I was unaware of this)
Alec Zorn
  • 43
  • 7