Sql Server deterministische benutzerdefinierte Funktion

Habe ich folgende benutzerdefinierte Funktion:

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

Ich kann nicht erstellen Sie einen Index für eine berechnete Spalte mit dieser Funktion, weil es nicht deterministisch ist.
Könnte jemand erklären, warum ist es nicht deterministisch, und schließlich, wie zu ändern, um es deterministisch?
Dank

InformationsquelleAutor der Frage opaera | 2010-09-06

  1. 43

    Brauchen Sie nur, um es zu schaffen with schemabinding.

    SQL-Server wird dann prüfen, ob oder nicht es erfüllt die Kriterien berücksichtigt werden als deterministisch (das tut es wie es nicht den Zugriff auf alle externen Tabellen oder die Verwendung von nicht deterministischen Funktionen wie getdate()).

    Können Sie überprüfen, dass es geklappt hat mit 

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

    Hinzufügen der option schemabinding zu Ihrem ursprünglichen code funktioniert gut, aber eine etwas einfachere version wäre.

    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

    InformationsquelleAutor der Antwort Martin Smith

  2. 5

    Müssen Sie deklarieren Sie die Benutzer-Definierte Funktion WITH SCHEMABINDING zu beschwichtigen, das 'deterministische' Anforderung eines Indexes für die berechnete Spalte.

    Einer Funktion deklariert WITH SCHEMABINDING behalten zusätzliche Kenntnisse über die Abhängigkeiten des Objekts verwendet in der Funktion (z.B. Spalten in der Tabelle), und verhindert jegliche änderungen, die diesen Spalten Hinzugefügt werden, es sei denn, die Funktion selbst ist zuvor verworfen.

    Deterministische Funktionen können auch helfen, die Sql Server in der Optimierung Ihrer Ausführung plant, vor allem die Halloween-Schutz problem.

    Hier ist ein Beispiel zum erstellen eines Indexes für eine berechnete Spalte mithilfe eines schema-gebunden-Funktion:

    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

    InformationsquelleAutor der Antwort StuartLC

Schreibe einen Kommentar