有什么方法可以使此UDF具有确定性吗? [英] Is there any way to make this UDF deterministic?

问题描述

我仅仅因为DB_NAME()不是确定性的，所以我认为这不是确定性的吗?如果DB_NAME()不是确定性的，为什么它不是确定性的?

I assume this is not deterministic simply because DB_NAME() is not deterministic? If DB_NAME() is not deterministic, why is it not deterministic?

ALTER FUNCTION [TheSchema].[udf_IS_PRODUCTION] ()
RETURNS bit
    WITH SCHEMABINDING
AS 
    BEGIN
        RETURN CASE WHEN DB_NAME() = 'PRODUCTION' THEN CONVERT(bit, 1) ELSE CONVERT(bit, 0) END
    END

更新:该版本有效，具有确定性，允许在任何数据库中使用相同的代码并删除数据库名称的硬编码(这还使我可以删除有关以下内容的自动系统运行状况例外:数据库名称编码)

Update: This version works, is deterministic, allows the same code to be used in any database and removes the hardcoding of the database name (which also allows me to remove another automatic system health exception about database name coding)

ALTER FUNCTION [TheSchema].[udf_IS_PRODUCTION] ()
RETURNS bit
    WITH SCHEMABINDING
AS 
    BEGIN
        RETURN (SELECT IS_PRODUCTION FROM TheSchema.IS_PRODUCTION)
    END

仅供参考，这是我的系统健康状况自报告系统中的代码段，用于监视潜在问题.

FYI This is the code snippet in my system health self-reporting system which I use to monitor potential problems.

    SELECT  'Non-deterministic Scalar UDF' AS Problem
           ,QUOTENAME(ROUTINE_SCHEMA) + '.' + QUOTENAME(ROUTINE_NAME) AS ROUTINE_NAME
    FROM    INFORMATION_SCHEMA.ROUTINES WITH (NOLOCK)
    WHERE   IS_DETERMINISTIC = 'NO'
            AND ROUTINE_TYPE = 'FUNCTION'
            AND DATA_TYPE <> 'TABLE'
    ORDER BY ROUTINE_SCHEMA
           ,ROUTINE_NAME

推荐答案

当然，我可以想到一种确定性的方法.在您的生产数据库上部署此功能:

Sure, I can think of one way to make it deterministic. Deploy this function on your production database:

ALTER FUNCTION [TheSchema].[udf_IS_PRODUCTION] ()
RETURNS bit
    WITH SCHEMABINDING
AS 
BEGIN
    RETURN CONVERT(bit, 1)
END

并将其部署到您的测试数据库:

And deploy this one to your test database:

ALTER FUNCTION [TheSchema].[udf_IS_PRODUCTION] ()
RETURNS bit
    WITH SCHEMABINDING
AS 
BEGIN
    RETURN CONVERT(bit, 0)
END

这似乎很愚蠢，但是除了某些UDF的返回值之外，IMO不应再对数据库名称进行硬编码".

This may seem silly but IMO the database name should not be "hard coded" any more so than the return value of some UDF.

更好的是，只需将此信息放在配置表中的某个位置即可.

Better yet, just put this information in a configuration table somewhere.

这篇关于有什么方法可以使此UDF具有确定性吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！