How can I alter this computed column in SQL Server 2008?
SqlSql Server-2008EditAlterCalculated ColumnsSql Problem Overview
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!
Sql Solutions
Solution 1 - Sql
Something like this:
ALTER TABLE dbo.MyTable
DROP COLUMN OldComputedColumn
ALTER TABLE dbo.MyTable
ADD OldComputedColumn AS OtherColumn + 10
Solution 2 - Sql
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.
Solution 3 - Sql
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.
Solution 4 - Sql
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:
-
Parameters can be other columns from the table
-
You may not be able to run all these queries at once, I had trouble with this. Run them one at a time
-
SQL automatically populates calculated columns, so dropping and adding won't affect data (I was unaware of this)