用户定义的函数SQL Server 2005错误地标记为非确定性？

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.

与这个问题相关,我决定检查我的数据仓库中的UDF(这应该在很大程度上是确定性的),并且我发现了几个不应该存在的UDF。

For instance:

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

Shows up in this query:

显示在此查询中:

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

Why is this?

为什么是这样?

1 个解决方案

#1

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

哎呀 - 显然,它要求SCHEMABINDING被指定为其他方面它可能会导致性能问题

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.

看起来我可以通过修复它们来获得一些性能提升。尼斯。

#1