Sql Server确定性用户定义函数

I have the following user-defined function:

我有以下用户定义的函数：

create function [dbo].[FullNameLastFirst]
(
    @IsPerson bit,
    @LastName nvarchar(100),
    @FirstName nvarchar(100)
)
returns nvarchar(201)
as
begin
    declare @Result nvarchar(201)
    set @Result = (case when @IsPerson = 0 then @LastName else case when @FirstName = '' then @LastName else (@LastName + ' ' + @FirstName) end end)
    return @Result
end

I can't create an Index on a computed column using this function cause it's not deterministic. Someone could explain why is it not deterministic and eventually how to modify to make it deterministic? Thanks

我不能使用此函数在计算列上创建索引，因为它不是确定性的。有人可以解释为什么它不是确定性的，最终如何修改以使其具有确定性？谢谢

2 个解决方案

#1

You just need to create it with schemabinding.

您只需要使用schemabinding创建它。

SQL Server will then verify whether or not it meets the criteria to be considered as deterministic (which it does as it doesn't access any external tables or use non deterministic functions such as getdate()).

然后，SQL Server将验证它是否符合要被视为确定性的条件（它执行的操作是因为它不访问任何外部表或使用非确定性函数，如getdate（））。

You can verify that it worked with

您可以验证它是否有效

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

Adding the schemabinding option to your original code works fine but a slightly simpler version would be.

将schemabinding选项添加到原始代码可以正常工作，但稍微简单一点的版本。

CREATE FUNCTION [dbo].[FullNameLastFirst] (@IsPerson  BIT,
                                           @LastName  NVARCHAR(100),
                                           @FirstName NVARCHAR(100))
RETURNS NVARCHAR(201)
WITH SCHEMABINDING
AS
  BEGIN
      RETURN CASE
               WHEN @IsPerson = 0
                     OR @FirstName = '' THEN @LastName
               ELSE @LastName + ' ' + @FirstName
             END
  END

#2

You need to declare the User Defined Function WITH SCHEMABINDING to appease the 'deterministic' requirement of an index on the computed column.

您需要声明用户定义函数WITH SCHEMABINDING来安抚计算列上索引的“确定性”要求。

A function declared WITH SCHEMABINDING will retain additional knowledge about the object dependencies used in the function (e.g. columns in the table), and will prevent any changes to these columns, unless the function itself is dropped beforehand.

声明为WITH SCHEMABINDING的函数将保留有关函数中使用的对象依赖项的其他知识（例如表中的列），并将阻止对这些列的任何更改，除非事先删除函数本身。

Deterministic functions can also assist Sql Server in optimizing its execution plans, most notably the Halloween Protection problem.

确定性函数还可以帮助Sql Server优化其执行计划，最明显的是万圣节保护问题。

Here's an example of creating an index on a computed column using a schema bound function:

以下是使用模式绑定函数在计算列上创建索引的示例：

create function [dbo].[FullNameLastFirst] 
( 
    @IsPerson bit, 
    @LastName nvarchar(100), 
    @FirstName nvarchar(100) 
) 
returns nvarchar(201) 
with schemabinding
as 
begin 
    declare @Result nvarchar(201) 
    set @Result = (case when @IsPerson = 0 then @LastName 
                        else case when @FirstName = '' then @LastName 
                                  else (@LastName + ' ' + @FirstName) end end) 
    return @Result 
end 


create table Person
(
  isperson bit,
  lastname nvarchar(100),
  firstname nvarchar(100),
  fullname as [dbo].[FullNameLastFirst] (isperson, lastname, firstname)
)
go
insert into person(isperson, lastname, firstname) values (1,'Firstname', 'Surname')
go

create index ix1_person on person(fullname)
go

select fullname from Person with (index=ix1_person) where fullname = 'Firstname Surname'
go

#1