uso - sql like multiple values

Función determinista definida por el usuario del servidor Sql

Question

Tengo la siguiente función definida por el usuario:

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

No puedo crear un índice en una columna calculada usando esta función porque no es determinista. Alguien podría explicar por qué no es determinista y, finalmente, cómo modificar para que sea determinista? Gracias

Answer 1

Solo necesita crearlo with schemabinding .

SQL Server verificará si cumple o no los criterios para ser considerado como determinista (lo cual hace ya que no accede a ninguna tabla externa ni utiliza funciones no determinísticas como getdate() ).

Puede verificar que funcionó con

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

Agregar la opción schemabinding a su código original funciona bien, pero sería una versión un poco más simple.

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

Answer 2

WITH SCHEMABINDING declarar la función definida por el usuario WITH SCHEMABINDING para aplacar el requisito "determinista" de un índice en la columna calculada.

Una función declarada WITH SCHEMABINDING retendrá conocimientos adicionales sobre las dependencias de objeto utilizadas en la función (por ejemplo, columnas en la tabla), y evitará cualquier cambio en estas columnas, a menos que la función en sí se elimine de antemano.

Las funciones deterministas también pueden ayudar al servidor Sql a optimizar sus planes de ejecución, especialmente el problema de protección de Halloween .

Aquí hay un ejemplo de cómo crear un índice en una columna calculada usando una función de límite de esquema:

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