Sql Server确定性的用户定义函数 [英] Sql Server deterministic user-defined function

问题描述

  create function [dbo]。[FullNameLastFirst] 
（ 
 @IsPerson位，
 @LastName nvarchar（100），
 @FirstName nvarchar（100）
）
返回nvarchar（201）
 as 
 begin 
 declare @Result nvarchar（201）
 set @Result =（@IsPerson = 0时的情况@LastName else @FirstName =''then @LastName else（@LastName +'' + @FirstName）end end）
 return @Result 
 end 
  

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

解决方案

您只需使用schemabinding c创建 >。然后，SQL Server将验证它是否符合被视为确定性的标准（它会这样做，因为它不访问任何外部表或使用非确定性函数如 getdate（））。

您可以验证它是否与

  SELECT OBJECTPROPERTY（OBJECT_ID（'[dbo]。[FullNameLastFirst]'），'IsDeterministic'）
  

将模式绑定选项添加到原始代码中可以正常工作，但版本会稍微简单一些。

（@ 100），
@FirstName NVARCHAR（100））
RETURNS NVARCHAR（100） 201）
WITH SCHEMABINDING
AS
BEGIN
RETURN CASE
当@IsPerson = 0
OR @FirstName =''THEN @LastName
ELSE @LastName +''+ @FirstName
END
END

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

解决方案

You just need to create it with 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()).

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.

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

这篇关于Sql Server确定性的用户定义函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！