为什么SQL Server说此函数是不确定的? [英] Why does SQL Server say this function is nondeterministic?

问题描述

在T-SQL中执行此功能:

CREATE FUNCTION [dbo].[Parse_URI_For_Scheme](
         @URI nvarchar(4000))
RETURNS nvarchar(250)
WITH SCHEMABINDING
AS
BEGIN

   DECLARE @temp_string varchar(4000)
   DECLARE @return_string nvarchar(250)
   DECLARE @pos int

   SET @pos = CHARINDEX('://', @URI);
   --select @pos
   IF @pos > 0
      BEGIN
         SET @temp_string = SUBSTRING(@URI, 0, @pos);

         -- SET @pos = CHARINDEX('/', @temp_string)

         IF @pos > 0
            BEGIN
               SET @temp_string = LEFT(@temp_string, @pos - 1);

               SET @pos = CHARINDEX('@', @temp_string);

               IF @pos > 0
                  SET @return_string = SUBSTRING(@temp_string, @pos + 1, 250);
               ELSE
                  SET @return_string = @temp_string;
            END
         ELSE
            SET @return_string = '';
      END
   ELSE
      SET @return_string = '';

   RETURN @return_string;

END;

然后执行此命令，该命令返回0:

SELECT OBJECTPROPERTY(OBJECT_ID('[dbo].Parse_URI_For_Scheme'), 'IsDeterministic')

有人可以告诉我为什么这不是确定性函数吗?

SchemaBinding功能是SQL Server将功能标记为确定性的关键点之一.为了使函数具有确定性，您需要使用With SchemaBinding定义函数.

在您的示例中，如果删除了With SchemaBinding，则ObjectProperty函数将为IsDeterministic属性返回0，因此通过添加With SchemaBinding可以为您解决问题

@Paul在这里有关于此问题的详细说明

Execute this function in T-SQL:

CREATE FUNCTION [dbo].[Parse_URI_For_Scheme](
         @URI nvarchar(4000))
RETURNS nvarchar(250)
WITH SCHEMABINDING
AS
BEGIN

   DECLARE @temp_string varchar(4000)
   DECLARE @return_string nvarchar(250)
   DECLARE @pos int

   SET @pos = CHARINDEX('://', @URI);
   --select @pos
   IF @pos > 0
      BEGIN
         SET @temp_string = SUBSTRING(@URI, 0, @pos);

         -- SET @pos = CHARINDEX('/', @temp_string)

         IF @pos > 0
            BEGIN
               SET @temp_string = LEFT(@temp_string, @pos - 1);

               SET @pos = CHARINDEX('@', @temp_string);

               IF @pos > 0
                  SET @return_string = SUBSTRING(@temp_string, @pos + 1, 250);
               ELSE
                  SET @return_string = @temp_string;
            END
         ELSE
            SET @return_string = '';
      END
   ELSE
      SET @return_string = '';

   RETURN @return_string;

END;

Then execute this command which returns 0:

SELECT OBJECTPROPERTY(OBJECT_ID('[dbo].Parse_URI_For_Scheme'), 'IsDeterministic')

Can someone please tell me why this is not a deterministic function?

解决方案

One of the key points for SQL Server to mark a function as Deterministic is the SchemaBinding feature. For your function to be deterministic you need to define the function using With SchemaBinding.

In Your example if you remove the With SchemaBinding, the ObjectProperty function will return 0 for the IsDeterministic attribute, so by adding the With SchemaBinding the problem will be resolved for you

@Paul has detailed explanation around this issue, here

这篇关于为什么SQL Server说此函数是不确定的?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！