SQL Server 确定性用户定义函数

Question

我有以下自定义函数：

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

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

- opaera

Answer 1

你只需要使用 schemabinding 创建它。

SQL Server 将验证它是否符合被视为确定性的标准（由于它不访问任何外部表或使用非确定性函数，如 getdate()，因此符合条件）。

你可以使用以下方法验证已经成功创建：

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

在您的原始代码中添加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

- Martin Smith

---

我创建了指向计算列的索引。我还有一个引用我的表的视图。我认为我也必须在视图上指定SchemaBinding来在同一列上创建索引。关于这一点，如果我的基本表在计算列上有索引，则在视图上创建另一个索引是否冗余？ - opaera

@opaera - 是的 - 在视图中，您也不需要对该列进行索引。 - Martin Smith

最后一个问题，如果可以的话。我有一个存储过程，它在视图上进行查询（该视图引用了我的索引表）。我可以/应该在查询from子句中指定索引名称吗？例如：Select * From MyView (With MyTableIndex) ...也许这样做会有一些好处？ - opaera

@opaera - 不，只要启用ARITHABORT或者ANSI_WARNINGS中的任意一个选项，它应该会自动使用已持久化的列值。你可以检查执行计划来确定这一点。 - Martin Smith

Answer 2

您需要声明使用WITH SCHEMABINDING的用户定义函数，以满足计算列上索引的"确定性"要求。

声明为WITH SCHEMABINDING的函数将保留有关函数中使用的对象依赖关系（例如表中的列）的附加信息，并且除非在删除函数本身之前，否则将防止对这些列进行任何更改。

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

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

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

- StuartLC