How can I alter this computed column in SQL Server 2008?
Something like this:
ALTER TABLE dbo.MyTable
DROP COLUMN OldComputedColumn
ALTER TABLE dbo.MyTable
ADD OldComputedColumn AS OtherColumn + 10
Source
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.
This is one of those situations where it can be easier and faster to just use the diagram feature of SQL Server Management Studio.
- Create a new diagram, add your table, and choose to show the formula column in the diagram's table view.
- Change the columns formula to an empty string
('')
or something equally innocuous (probably such that you don't change the column's datatype). - Save the diagram (which should save the table).
- Alter your function.
- Put the function back in the formula for that column.
- 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.