如何在 SQL Server 2008 中更改此计算列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2372655/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I alter this computed column in SQL Server 2008?
提问by AndreMiranda
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.
但它不起作用。我正在尝试向 case 语句输入另一个条件，但它不起作用。
Thanks a lot!
非常感谢！
回答by Leniel Maccaferri
回答by Michael Todd
If you're trying to change an existing column, you can't use ADD. Instead, try this:
如果您尝试更改现有列，则不能使用 ADD。相反，试试这个：
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))
更改表 tbPedidos 将列限制更改为（强制转换（当 restricaoLicenca = 1 或 restricaoLote = 1 或 restricaoValor = 1 然后 1 else 0 作为位结束））
EDIT: The above is incorrect. When altering a computedcolumn the only thing you cando is drop it and re-add it.
编辑：以上是不正确的。当修改一个计算列，你唯一可以做的就是删除它并重新添加它。
回答by Tim Lehner
This is one of those situations where it can be easier and faster to just use the diagramfeature of SQL Server Management Studio.
这是仅使用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...addwill not guarantee. This may be important to some.
在 SSMS 中以这种方式执行此操作将保留表中列的顺序，这是简单的drop...add不能保证的。这对某些人来说可能很重要。
回答by Alec Zorn
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):
另一件可能对某人有帮助的事情是如何修改作为表中计算列的函数（以下查询适用于 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)
参数可以是表中的其他列
您可能无法一次运行所有这些查询，我遇到了这个问题。一次运行一个
- SQL 自动填充计算列，因此删除和添加不会影响数据（我不知道这一点）
