sql-server – SCHEMABINDING万圣节保护之外的功能有什么好处？

众所周知,SCHEMABINDING函数可以在更新计划中使用 avoid an unnecessary spool：

If you are using simple T-SQL UDFs that do not touch any tables (i.e. do not access data),make sure you specify the SCHEMABINDING option during creation of the UDFs. This will make the UDFs schema-bound and ensure that the query optimizer does not generate any unnecessary spool operators for query plans involving these UDFs.

即使它不访问数据,SCHEMABINDING函数还有其他优点吗？

解决方法

未指定WITH SCHEMABINDING意味着SQL Server会跳过它通常在函数体上进行的详细检查.它只是将函数标记为访问数据(如问题中给出的链接中所述).

这是性能优化.如果它没有做出这个假设,SQL Server将不得不对每个函数调用执行详细检查(因为未绑定的函数可能随时更改).

有五个重要的功能属性：

>决定论
>精度
>数据访问
>系统数据访问
>系统验证

例如,采用以下未绑定的标量函数：

CREATE FUNCTION dbo.F
(
    @i integer
)
RETURNS datetime
AS
BEGIN
    RETURN '19000101';
END;

我们可以使用元数据函数查看五个属性：

SELECT 
    IsDeterministic = OBJECTPROPERTYEX(Func.ID,'IsDeterministic'),IsPrecise = OBJECTPROPERTYEX(Func.ID,'IsPrecise'),IsSystemVerified = OBJECTPROPERTYEX(Func.ID,'IsSystemVerified'),UserDataAccess = OBJECTPROPERTYEX(Func.ID,'UserDataAccess'),SystemDataAccess = OBJECTPROPERTYEX(Func.ID,'SystemDataAccess')
FROM (VALUES(OBJECT_ID(N'dbo.F',N'FN'))) AS Func (ID);

两个数据访问属性已设置为true,其他三个设置为false.

这超出了可能预期的含义(例如,在索引视图或索引计算列中使用).

对查询优化器的影响

Determinism属性尤其会影响查询优化器.它有关于允许执行的重写和操作类型的详细规则,并且这些规则对于非确定性元素非常有限.副作用可能非常微妙.

例如,请考虑以下两个表：

CREATE TABLE dbo.T1
(
    SomeInteger integer PRIMARY KEY
);
GO
CREATE TABLE dbo.T2
(
    SomeDate datetime PRIMARY KEY
);

…以及使用该函数的查询(如前所述)：

SELECT * 
FROM dbo.T1 AS T1
JOIN dbo.T2 AS T2
    ON T2.SomeDate = dbo.F(T1.SomeInteger);

查询计划符合预期,其特征是搜索表T2：

但是,如果使用派生表或公用表表达式编写相同的逻辑查询：

WITH CTE AS
(
    SELECT *,dt = dbo.F(T1.SomeInteger) 
    FROM dbo.T1 AS T1
)
SELECT * 
FROM CTE
JOIN dbo.T2 AS T2
    ON T2.SomeDate = CTE.dt;

-- Derived table
SELECT
    *
FROM 
(
    SELECT *,dt = dbo.F(T1.SomeInteger)
    FROM dbo.T1 AS T1
) AS T1
JOIN dbo.T2 AS T2
    ON T2.SomeDate = T1.dt;

执行计划现在具有扫描功能,其中谓词涉及功能卡在过滤器中：

如果派生表或公用表表达式被视图或内联函数替换,也会发生这种情况. FORCESEEK提示(和其他类似的尝试)将不会成功：

根本问题是查询优化器无法自由地重新排序非确定性查询元素.

要生成搜索,需要将Filter谓词向下移动到T2数据访问.当函数是非确定性时,将阻止此移动.

固定

此示例的修复包括两个步骤：

>添加WITH SCHEMABINDING
>使功能具有确定性

第一步是微不足道的.第二个涉及从字符串到日期时间删除非确定性隐式转换;用确定的CONVERT替换它.它本身都不够.

ALTER FUNCTION dbo.F
(
    @i integer
)
RETURNS datetime
WITH SCHEMABINDING
AS
BEGIN
    -- Convert with a deterministic style
    RETURN CONVERT(datetime,'19000101',112);
END;

功能属性现在是：

随着优化器的释放,所有示例现在都可以生成所需的搜索计划.

请注意,在函数中使用CAST到datetime不起作用,因为无法在该语法中指定转换样式：

ALTER FUNCTION dbo.F
(
    @i integer
)
RETURNS datetime
WITH SCHEMABINDING
AS
BEGIN
    -- Convert with a deterministic style
    RETURN CAST('19000101' AS datetime);
END;

此函数定义生成扫描计划,属性显示它仍然是不确定的：

（编辑：安卓应用网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!