计算列无法持久化。

Question

我有一个自定义函数，现在尝试使用这个函数创建一个持久化列。

但是出现以下错误：

表"SomeTable"中的计算列"FormattedSSN"无法持久化，因为该列是非确定性的。

这是我的函数：

ALTER FUNCTION [dbo].[FormatSSN]()
RETURNS VARCHAR(11)
AS
BEGIN
    return '';
END

以下是使用该函数添加列的查询：

ALTER TABLE SomeTable
ADD FormattedSSN as dbo.FormatSSN() PERSISTED

请建议是否有任何解决方法。谢谢。

- noob.spt

Answer 1

将WITH SCHEMABINDING 添加到函数中，像这样：

ALTER FUNCTION [dbo].[FormatSSN]
(
@SSN    VARCHAR(9)
)
RETURNS CHAR(11)
WITH SCHEMABINDING
AS
BEGIN
  your stuff here
END

然后运行以下代码以验证：

IF OBJECTPROPERTY (OBJECT_ID(N'[dbo].[FormatSSN]'),'IsDeterministic') = 1
   PRINT 'Function is detrministic.'
ELSE IF OBJECTPROPERTY (OBJECT_ID(N'[dbo].[FormatSSN]'),'IsDeterministic') = 0
   PRINT 'Function is NOT detrministic'
GO

在这里有效。

- Damir Sudarevic

---

太棒了。但是这个函数与普通函数有什么不同（行为）？ - noob.spt

谢谢。我在其他上下文中（例如视图）了解了SCHEMABINDING。它似乎在这里的工作方式基本相同。对表的任何更改都会引发错误。 - noob.spt

Answer 2

如何直接指定定义呢：

ALTER TABLE SomeTable
ADD FormattedSSN as
    case when len(EmployeeSSN) = 9 then
            substring(EmployeeSSN, 1, 3) + '-' +
            substring(EmployeeSSN, 4, 2) + '-' +
            substring(EmployeeSSN, 6, 4)
    else EmployeeSSN end
PERSISTED

- schinazi

---

太好了！它成功了 :)... 你能解释一下为什么吗？这个和我发布的那个有什么区别？它是否会以同样的方式工作？ - noob.spt

引擎无法确定UDF可能具有的副作用，因为它可能会在以后进行修改，并且每次运行时都会重新计算其缓存计划。当将计算列表示为SQL表达式时，它知道没有不确定性的副作用。 - Charles Bretana

Answer 3

不要调用UDF，而是将计算列表达式设置为

Case When Len(EmployeeSSN) = 0 Then 
      SUBSTRING(EmployeeSSN, 1, 3) + '-' + 
      SUBSTRING(EmployeeSSN, 4, 2) + '-' + 
      SUBSTRING(EmployeeSSN, 6, 4)
    Else EmployeeSSN End

在创建表的脚本中，您可以添加一列：

[NewColumnName]  As
   (Case When len([UpdateUserId])=(0) T
         Then (((substring([UpdateUserId],(1),(3))+'-')+
                 substring([UpdateUserId],(4),(2)))+'-')+
                 substring([UpdateUserId],(6),(4)) 
         Else [UpdateUserId] End) PERSISTED,

- Charles Bretana

Answer 4

创建一个适当数据类型的非计算列。创建一个插入触发器（如果数据将会更改，则还需创建更新触发器）。然后可以将函数输出持久化到列中。

ALTER TABLE SomeTable ADD FormattedSSN VARCHAR(11)
GO

CREATE TRIGGER dbo.TR_I_SomeTable ON  dbo.SomeTable AFTER INSERT
AS 
BEGIN
    SET NOCOUNT ON;

    update s
    set s.FormattedSSN = dbo.FormatSSN()
    from SomeTable AS s
        join inserted i on i.id = s.id

END
GO

- rayzinnz

---

这对于非确定性函数非常好，比如FORMAT。谢谢！ - WhatIsHeDoing