Is there any way to make this UDF deterministic?

Question

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

 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 you to use the same code in any database and removes the hard encoding of the database name (which also allows me to remove another system security exception encoding the database name)

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

FYI This is a piece of code in a self-service system that 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 

Answer 1

Of course, I can think of one way to make it deterministic. Deploy this function in your production database:

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

And deploy it to the test database:

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

This may sound silly, but the IMO database name should not be hardcoded, not the return value of some UDF.

Even better, just put this information in a configuration table somewhere.

Answer 2

Could you rewrite your function not to define DB_NAME () internally, but to get it as a parameter

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

Should not be determinate, right?

When you call it, you can use DB_NAME() as a function to determine the database name

Answer 3

A deterministic function, by definition, is a function whose return value is uniquely identified by the values ​​of its agents.

Now, given the arguments of DB_NAME() (which are not), can you say what it will return?

Answer 4

In the strict sense of determinism, the result is not based on input parameters, but on the state of an external object that is not under your control.

The name may be changed, etc.,

 Alter Database Modify Name = new_name 

In 2005, SQL did not prevent the creation of the function, although when trying to use it by default. If you encounter a situation where she refuses to accept a function based on non-determinism, and you need to get around it (with risks, etc.), the route around it consists in creating a view that uses this function, and then select from the view inside functions.