用户定义的函数SQL Server 2005错误地标记为不确定的吗？ [英] User-Defined Functions SQL Server 2005 flagged incorrectly as non-deterministic?

问题描述

与此问题有关，我决定检查数据仓库中的UDF（应该在很大程度上一直是确定性的），我发现了一些不应该的。

Related to this question, I decided to check the UDFs in my data warehouse (which should largely have been deterministic), and I found several which aren't which should be.

例如：

CREATE FUNCTION [udf_YearFromDataDtID]
(
    @DATA_DT_ID int
)
RETURNS int
AS
BEGIN
    RETURN @DATA_DT_ID / 10000
END

在此查询中显示：

SELECT  ROUTINE_NAME
FROM    INFORMATION_SCHEMA.ROUTINES
WHERE   IS_DETERMINISTIC = 'NO'
        AND ROUTINE_TYPE = 'FUNCTION'
ORDER BY ROUTINE_NAME

为什么？

推荐答案

很像-显然，它需要以其他方式指定SCHEMABINDING 可能会导致性能问题

Yikes - apparently, it REQUIRES SCHEMABINDING to be specified other wise it could cause performance problems

ALTER FUNCTION [udf_YearFromDataDtID]
(
    @DATA_DT_ID int
)
RETURNS int
WITH SCHEMABINDING
AS
BEGIN
    RETURN @DATA_DT_ID / 10000
END

看起来我可以通过解决这些问题来提高性能。很好。

Looks like I could be getting some performance boosts by fixing these. Nice.

这篇关于用户定义的函数SQL Server 2005错误地标记为不确定的吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！