Funzione deterministica definita dall’utente del server Sql

Ho la seguente funzione definita dall’utente:

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 

Non riesco a creare un indice su una colonna calcasting utilizzando questa funzione perché non è deterministico. Qualcuno potrebbe spiegare perché non è deterministico e alla fine come modificarlo per renderlo deterministico? Grazie

Hai solo bisogno di crearlo with schemabinding .

SQL Server verificherà quindi se soddisfa o meno i criteri da considerare come deterministici (cosa che fa in quanto non accede a nessuna tabella esterna o utilizza funzioni non deterministiche come getdate() ).

Puoi verificare che abbia funzionato

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

L’aggiunta dell’opzione schemabinding al codice originale funziona bene, ma una versione leggermente più semplice sarebbe.

 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 

È necessario dichiarare la funzione definita dall’utente WITH SCHEMABINDING per soddisfare il requisito ‘deterministico’ di un indice nella colonna calcasting.

Una funzione dichiarata WITH SCHEMABINDING manterrà ulteriori informazioni sulle dipendenze object utilizzate nella funzione (ad es. Colonne nella tabella) e impedirà qualsiasi modifica a queste colonne, a meno che la funzione stessa non venga rilasciata in anticipo.

Le funzioni deterministiche possono anche aiutare il server Sql ad ottimizzare i suoi piani di esecuzione, in particolare il problema della protezione di Halloween .

Ecco un esempio di creazione di un indice su una colonna calcasting utilizzando una funzione associata allo schema:

 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