主页 / dba / 问题 / 325814
Accepted
Charlieface
Asked: 2023-04-10 05:05:02 +0800 CST

具有递归标量 UDF 的模式绑定

  • 772

长话短说；博士； SQL Server 允许标量 UDF 在模式绑定时递归调用自身，但仅当使用语法更改为这样做时，这是一个错误吗CREATE OR ALTER？还是不允许其他语法的错误？

一个简单的递归标量 UDF 可以构造如下

CREATE FUNCTION dbo.Try1 (@i int)
RETURNS int
AS BEGIN
  RETURN IIF(@i = 0, 0, @i + dbo.Try1(@i - 1));
END;

只要这不是模式绑定的，那么这是允许的。

让我们尝试对其进行模式绑定，我们会做

CREATE FUNCTION dbo.Try2 (@i int)
RETURNS int
WITH SCHEMABINDING
AS BEGIN
  RETURN IIF(@i = 0, 0, @i + dbo.Try2(@i - 1));
END;

没有。

Cannot find either column "dbo" or the user-defined function
or aggregate "dbo.Try2", or the name is ambiguous

在没有递归的情况下创建它然后改变它

CREATE OR ALTER FUNCTION dbo.Try3 (@i int)
RETURNS int
WITH SCHEMABINDING
AS BEGIN RETURN NULL; END;

ALTER FUNCTION dbo.Try3 (@i int)
RETURNS int
WITH SCHEMABINDING
AS BEGIN
  RETURN IIF(@i = 0, 0, @i + dbo.Try3(@i - 1));
END;

这次是一个不同的错误：

Cannot schema bind function 'dbo.Try3' because name 'dbo.Try3' is invalid
for schema binding. Names must be in two-part format
and an object cannot reference itself.

嗯，an object cannot reference itself这个规则是谁发明的？它不在文档中。

让我们试试CREATE OR ALTER

CREATE OR ALTER FUNCTION dbo.Try4 (@i int)
RETURNS int
WITH SCHEMABINDING
AS BEGIN
  RETURN IIF(@i = 0, 0, @i + dbo.Try4(@i - 1));
END;

还没。

但是，如果我们首先CREATE 不使用递归，然后使用CREATE OR ALTER（不 ALTER）使用递归，那么它就可以工作

CREATE OR ALTER FUNCTION dbo.Try5 (@i int)
RETURNS int
WITH SCHEMABINDING
AS BEGIN RETURN NULL; END;

CREATE OR ALTER FUNCTION dbo.Try5 (@i int)
RETURNS int
WITH SCHEMABINDING
AS BEGIN
  RETURN IIF(@i = 0, 0, @i + dbo.Try5(@i - 1));
END;

CREATE OR ALTER奇怪的是：无论哪种方式在下面起作用，这都没有意义。如果它真的丢弃并重新创建，那么为什么我们不得到第一个错误？如果它改变了，那么为什么最后一个选项有效？

最有趣的是：如果我们先在没有模式绑定的情况下创建它，那么CREATE OR ALTER WITH SCHEMABINDING我们就会得到

Cannot schema bind function 'dbo.Try6'. 'dbo.Try6' is not schema bound.

这完全是胡说八道。

数据库<>小提琴

sql-server

1 个回答

  1. Best Answer

    自引用函数通常没有很好的文档记录，但对此类函数的模式绑定的限制一直存在。并非每个错误条件都在父语法页面中提及。

    问题中概述的导致模式绑定自引用函数的特定事件序列不应被允许。（它也适用于多语句表值函数）。

    允许自引用函数的模式绑定引入了许多必须考虑和测试的新可能性。例如，以下使用您的函数会导致DBCC CHECKTABLE失败：

    CREATE TABLE dbo.Test 
(
    n integer NOT NULL,
    -- Can be persisted because the function is deterministic
    -- Checks for determinism require schemabinding
    sn AS dbo.Try5(n) PERSISTED, 
        CHECK (sn > 0)
);

-- Succeeds
INSERT dbo.Test (n) 
VALUES (31);

DBCC CHECKTABLE (N'dbo.Test') 
    WITH EXTENDED_LOGICAL_CHECKS;

    Msg 2537, Level 16, State 0, Line 76
Table error: object ID 1137439126, index ID 0, partition ID 72057594050772992,
alloc unit ID 72057594110541824 (type In-row data), page (1:568), row 0.
The record check (valid computed column) failed. The values are 2 and 0.
DBCC results for 'Test'.
There are 1 rows in 1 pages for object "Test".
CHECKTABLE found 0 allocation errors and 1 consistency errors in table 'Test'
 (object ID 1137439126).
repair_allow_data_loss is the minimum repair level for the errors found by
 DBCC CHECKTABLE (Sandpit.dbo.Test).
DBCC execution completed. 
If DBCC printed error messages, contact your system administrator.

    我没有调查失败的原因，但我想验证CHECKTABLE计算列值的活动最终超过了最大嵌套级别 32。

    许多事情需要测试和/或调整以支持模式绑定自引用功能。这种用法不够流行，不足以证明投资的合理性。SQL Server 中存在递归的替代方案。

    • 7

相关问题