有什么方法可以使此UDF具有确定性吗?

本文介绍了有什么方法可以使此UDF具有确定性吗?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我仅仅因为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.