更改计算列引用的 SQL 函数

Question

如果将表的列设置为计算列，其公式调用函数，则更改该基础函数将变得很痛苦。 每次更改时，您都必须找到其公式引用函数的每一列，删除引用，保存表，更改函数，将所有内容添加回来，然后再次保存。 即使是很小的变化也是噩梦。

您能否告诉 SQL Server 您不关心公式正在引用该函数，而是继续更改基础函数？

额外细节： 计算列不会被 FK 约束保留或引用，因为它是不确定的。 该函数考虑当前时间。 它处理记录是否过期的问题。

Answer 1

不，据我所知，您不能执行此操作 - 您必须首先删除引用函数的所有计算列，更改函数，然后重新创建计算列。

也许微软会在 SQL Server 2010/2011 中给我们一个“CREATE OR ALTER FUNCTION”命令？ :-)

马克

Answer 2

很抱歉这么晚才回答，但它可能很有用。

您可以为每个将调用实际函数的计算列使用虚拟函数。

示例：
计算列使用公式：dbo.link_comp('123')
该函数转发参数并调用并返回函数 dbo.link('123') （您的真实函数）
两个函数只需要使用相同的参数并返回相同的类型。

然后，被锁定的函数是 dbo.link_comp，您仍然可以更改 dbo.link。
另外，如果您的函数是从其他 SQL 调用的，您仍然可以使用真实函数名称 dbo.link，虚拟函数 dbo.link_comp 仅适用于计算列。

Answer 3

ALTER 的后果可能是巨大的。

您是否对列建立了索引？ 在具有模式绑定的视图中使用它吗？ 坚持下来了吗？ 外键有关系吗？

如果 ALTER 更改了数据类型、可空性或确定性怎么办？

停止具有依赖关系的 ALTER FUNCTION 比处理这么多场景更容易。

Answer 4

假设表 T1 包含列 C1、C2、C3、C4、C5，其中 C4 是计算列

还假设 C4 引用函数 OldFunc，您希望将其替换为 NewFunc

首先，将所有行中的非计算列移至临时表

Select C1, C2, C3, C5 into TmpT1 from T1
Go

接下来，删除 T1 中的所有行

Delete From T1
go

现在您可以修改列 C4

Alter table T1 alter column C4 as dbo.NewFunc()
Go

现在将保存的数据放回到原始表中

Insert Into T1 (C1,C2,C3,C5) select C1,C2,C3,C5 from TmpT1

现在删除临时表

Drop Table TmpT1

Answer 5

我知道这已经晚了，但我今天也遇到了同样的问题，但没有找到任何真正解决问题的东西，所以我很快就编写了一个脚本。

本质上，它使用该函数创建一个临时表，保存每个计算列的列信息，然后从表中删除列。 然后，您更新函数并让它使用其定义再次重新创建所有列。

如果您必须更改定义中的参数（就像我需要的那样），您可以简单地将该部分编写到再次创建定义的位置。

如果您在索引中计算了列或有其他需求，您可以轻松地扩展代码，但这超出了我的需求范围。

希望它对其他人有用。

/* Create temporary table to hold definitions */
CREATE TABLE [#FUNCTION]
(
    [TABLE_NAME] nvarchar(255) NOT NULL,
    [COLUMN_NAME] nvarchar(255) NOT NULL,
    [DEFINITION] nvarchar(255) NOT NULL
)
GO

/* Add data to temp table */
INSERT INTO [#FUNCTION] ( [TABLE_NAME], [COLUMN_NAME], [DEFINITION] )
SELECT TABLE_NAME, COLUMN_NAME, definition FROM INFORMATION_SCHEMA.COLUMNS
INNER JOIN sys.computed_columns ON ( object_id = object_id( TABLE_NAME ) AND name = COLUMN_NAME )
WHERE definition LIKE '%MyFunctionName%'
GO

/* Remove columns */
DECLARE @TABLE_NAME nvarchar(255)
DECLARE @COLUMN_NAME nvarchar(255)

DECLARE c_CursorName CURSOR LOCAL FOR SELECT [TABLE_NAME], [COLUMN_NAME] FROM [#FUNCTION]
OPEN c_CursorName

FETCH NEXT FROM c_CursorName INTO @TABLE_NAME, @COLUMN_NAME

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC( 'ALTER TABLE [' + @TABLE_NAME + '] DROP COLUMN [' + @COLUMN_NAME + ']' )

    FETCH NEXT FROM c_CursorName INTO @TABLE_NAME, @COLUMN_NAME
END

CLOSE c_CursorName
DEALLOCATE c_CursorName
GO

/* Update function */
-- Update function here
GO

/* Recreate computed columns */
DECLARE @TABLE_NAME nvarchar(255)
DECLARE @COLUMN_NAME nvarchar(255)
DECLARE @DEFINITION nvarchar(255)

DECLARE c_CursorName CURSOR LOCAL FOR SELECT [TABLE_NAME], [COLUMN_NAME], [DEFINITION] FROM [#FUNCTION]
OPEN c_CursorName

FETCH NEXT FROM c_CursorName INTO @TABLE_NAME, @COLUMN_NAME, @DEFINITION

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC( 'ALTER TABLE [' + @TABLE_NAME + '] ADD [' + @COLUMN_NAME + '] AS ' + @DEFINITION )

    FETCH NEXT FROM c_CursorName INTO @TABLE_NAME, @COLUMN_NAME, @DEFINITION
END

CLOSE c_CursorName
DEALLOCATE c_CursorName
GO

/* Remove temp table */
DROP TABLE [#FUNCTION]
GO

Answer 6

您可以尝试使用一些好的模式比较工具，为您创建脚本:)

Answer 7

您可以将列更改为不计算，并通过 TRIGGER 更新它。

或者您可以将表重命名为其他名称，删除计算列，然后创建一个 VIEW 来代替原始表（即使用原始表名称）并包括您需要的“计算”列。

编辑：请注意，这可能会扰乱您对原始表名称（现在是视图）的插入。 显然，您可以保留旧表，删除计算列，然后创建一个包含计算列的单独视图。

我们已经对计算列进行了足够多的研究，最终发现它们带来的麻烦比得到的要多。 故障安全插入（1），尝试将视图插入到具有计算列的表中，需要与 SET ARITHABORT 打乱等等。

(1) 我们有故障安全插入，例如：

INSERT INTO MyTable SELECT * FROM MyOtherTable WHERE ...，

如果在一个表中添加新列而不是在另一个表中添加新列，这些插入就会失败。 对于计算列，我们必须显式命名所有列，这使我们失去了安全网。