CREATE FUNCTION (Transact-SQL)

Consente di creare una funzione definita dall'utente in SQL Server 2012. Una funzione definita dall'utente è una routine Transact-SQL o Common Language Runtime (CLR) tramite cui vengono accettati parametri, viene effettuata un'azione, ad esempio un calcolo complesso, e viene restituito il risultato di tale azione sotto forma di valore. Il valore restituito può essere un valore scalare (singolo) o una tabella. Utilizzare questa istruzione per creare una routine riutilizzabile che può essere utilizzata in queste modalità:

• Nelle istruzioni Transact-SQL, ad esempio SELECT.

• Nelle applicazioni che chiamano la funzione.

• Nella definizione di un'altra funzione definita dall'utente.

• Per parametrizzare una vista o per migliorare le funzionalità di una vista indicizzata.

• Per definire una colonna di una tabella.

• Per definire un vincolo CHECK su una colonna.

• Per sostituire una stored procedure.

Convenzioni della sintassi Transact-SQL

Sintassi

--Transact-SQL Scalar Function Syntax
CREATE FUNCTION [ schema_name. ] function_name 
( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type 
    [ = default ] [ READONLY ] } 
    [ ,...n ]
  ]
)
RETURNS return_data_type
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    BEGIN 
        function_body 
        RETURN scalar_expression
    END
[ ; ]

--Transact-SQL Inline Table-Valued Function Syntax 
CREATE FUNCTION [ schema_name. ] function_name 
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type 
    [ = default ] [ READONLY ] } 
    [ ,...n ]
  ]
)
RETURNS TABLE
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    RETURN [ ( ] select_stmt [ ) ]
[ ; ]

--Transact-SQL Multistatement Table-valued Function Syntax
CREATE FUNCTION [ schema_name. ] function_name 
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type 
    [ = default ] [READONLY] } 
    [ ,...n ]
  ]
)
RETURNS @return_variable TABLE <table_type_definition>
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    BEGIN 
        function_body 
        RETURN
    END
[ ; ]

--Transact-SQL Function Clauses 
<function_option>::= 
{
    [ ENCRYPTION ]
  | [ SCHEMABINDING ]
  | [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
  | [ EXECUTE_AS_Clause ]
}

<table_type_definition>:: = 
( { <column_definition> <column_constraint> 
  | <computed_column_definition> } 
    [ <table_constraint> ] [ ,...n ]
) 

<column_definition>::=
{
    { column_name data_type }
    [ [ DEFAULT constant_expression ] 
      [ COLLATE collation_name ] | [ ROWGUIDCOL ]
    ]
    | [ IDENTITY [ (seed , increment ) ] ]
    [ <column_constraint> [ ...n ] ] 
}

<column_constraint>::= 
{
    [ NULL | NOT NULL ] 
    { PRIMARY KEY | UNIQUE }
      [ CLUSTERED | NONCLUSTERED ] 
      [ WITH FILLFACTOR = fillfactor 
        | WITH ( < index_option > [ , ...n ] )
      [ ON { filegroup | "default" } ]
  | [ CHECK ( logical_expression ) ] [ ,...n ]
}

<computed_column_definition>::=
column_name AS computed_column_expression 

<table_constraint>::=
{ 
    { PRIMARY KEY | UNIQUE }
      [ CLUSTERED | NONCLUSTERED ] 
      ( column_name [ ASC | DESC ] [ ,...n ] )
        [ WITH FILLFACTOR = fillfactor 
        | WITH ( <index_option> [ , ...n ] )
  | [ CHECK ( logical_expression ) ] [ ,...n ]
}

<index_option>::=
{ 
    PAD_INDEX = { ON | OFF } 
  | FILLFACTOR = fillfactor 
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF } 
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS ={ ON | OFF } 
}

--CLR Scalar Function Syntax
CREATE FUNCTION [ schema_name. ] function_name 
( { @parameter_name [AS] [ type_schema_name. ] parameter_data_type 
    [ = default ] } 
    [ ,...n ]
)
RETURNS { return_data_type }
    [ WITH <clr_function_option> [ ,...n ] ]
    [ AS ] EXTERNAL NAME <method_specifier>
[ ; ]

--CLR Table-Valued Function Syntax
CREATE FUNCTION [ schema_name. ] function_name 
( { @parameter_name [AS] [ type_schema_name. ] parameter_data_type 
    [ = default ] } 
    [ ,...n ]
)
RETURNS TABLE <clr_table_type_definition> 
    [ WITH <clr_function_option> [ ,...n ] ]
    [ ORDER ( <order_clause> ) ]
    [ AS ] EXTERNAL NAME <method_specifier>
[ ; ]

--CLR Function Clauses
<order_clause> ::= 
{
   <column_name_in_clr_table_type_definition>
   [ ASC | DESC ] 
} [ ,...n] 

<method_specifier>::=
    assembly_name.class_name.method_name

<clr_function_option>::=
}
    [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
  | [ EXECUTE_AS_Clause ]
}

<clr_table_type_definition>::= 
( { column_name data_type } [ ,...n ] )

Argomenti

• schema_name
  Nome dello schema a cui appartiene la funzione definita dall'utente.

• function_name
  Nome della funzione definita dall'utente. I nomi di funzione devono essere conformi alle regole per gli identificatori ed essere univoci all'interno del database e rispetto al relativo schema.

  [!NOTA]

  È necessario apporre le parentesi dopo il nome della funzione anche se non viene specificato alcun parametro.

• @parameter\_name
  Parametro della funzione definita dall'utente. È possibile dichiarare uno o più parametri.

  Una funzione può avere al massimo 2.100 parametri. Il valore di ciascun parametro dichiarato deve essere specificato dall'utente quando viene eseguita la funzione, a meno che non venga definito un valore predefinito per tale parametro.

  Specificare un nome di parametro utilizzando come primo carattere il simbolo di chiocciola (@). I nomi di parametro devono essere conformi alle regole per gli identificatori. I parametri sono locali rispetto alla funzione. È pertanto possibile utilizzare gli stessi nomi di parametro in altre funzioni. I parametri possono rappresentare solo costanti, non nomi di tabella, di colonna o di altri oggetti di database.

  [!NOTA]

  ANSI_WARNINGS non viene applicata quando vengono passati parametri a una stored procedure, una funzione definita dall'utente oppure in caso di dichiarazione e impostazione delle variabili in un'istruzione batch. Se, ad esempio, la variabile viene definita come char(3) e quindi impostata su un valore maggiore di tre caratteri, i dati verranno troncati alla dimensione definita e l'istruzione INSERT o UPDATE avrà esito positivo.

• [ type_schema_name. ] parameter_data_type
  Tipo di dati del parametro e, facoltativamente, lo schema a cui appartiene. Per le funzioni Transact-SQL sono consentiti tutti i tipi di dati, compresi i tipi CLR definiti dall'utente e i tipi di tabella definiti dall'utente, eccetto il tipo di dati timestamp. Per le funzioni CLR sono consentiti tutti i tipi di dati, inclusi i tipi CLR definiti dall'utente, eccetto i tipi di dati text, ntext, image e timestamp nonché i tipi di tabella definiti dall'utente. Non è possibile specificare i tipi non scalari, cursor e table, come tipo di dati del parametro nelle funzioni Transact-SQL o CLR.

  Se non si specifica type_schema_name, il Motore di database esegue la ricerca di scalar_parameter_data_type in base all'ordine seguente:

  • Schema contenente i nomi dei tipi di dati di sistema di SQL Server.

  • Schema predefinito dell'utente corrente nel database corrente.

  • Schema dbo nel database corrente.

• [ =default ]
  Valore predefinito per il parametro. Se un valore viene impostato su default, è possibile eseguire la funzione senza specificare un valore per il parametro.

  [!NOTA]

  È possibile specificare valori predefiniti per i parametri per le funzioni CLR, ad eccezione dei tipi di dati varchar(max) e varbinary(max).

  Se a un parametro della funzione è associato un valore predefinito, alla chiamata della funzione è necessario specificare la parola chiave DEFAULT per recuperare il valore predefinito. Questo comportamento risulta diverso dall'utilizzo di parametri con valori predefiniti nelle stored procedure in cui l'omissione del parametro implica l'utilizzo del valore predefinito. Tuttavia, la parola chiave DEFAULT non è richiesta in caso di richiamo di una funzione scalare tramite l'istruzione EXECUTE.

• READONLY
  Viene indicato che il parametro non può essere aggiornato o modificato all'interno della definizione della funzione. Se il tipo di parametro è un tipo di tabella definito dall'utente, deve essere specificata la parola chiave READONLY.

• return_data_type
  Valore restituito di una funzione scalare definita dall'utente. Per le funzioni Transact-SQL sono consentiti tutti i tipi di dati, compresi i tipi CLR definiti dall'utente, eccetto il tipo di dati timestamp. Per le funzioni CLR sono consentiti tutti i tipi di dati, compresi i tipi CLR definiti dall'utente, eccetto i tipi di dati text, ntext, image e timestamp. Non è possibile specificare i tipi non scalari, cursor e table come tipo di dati restituito nelle funzioni Transact-SQL o CLR.

• function_body
  Specifica che una serie di istruzioni Transact-SQL, che nel loro complesso non producono alcun effetto collaterale, ad esempio la modifica di una tabella, definisce il valore della funzione. function_body viene utilizzato solo in funzioni scalari e funzioni a istruzioni multiple con valori di tabella.

  Nelle funzioni scalari, function_body corrisponde a una serie di istruzioni Transact-SQL che in combinazione restituiscono un valore scalare.

  Nelle funzioni a istruzioni multiple con valori di tabella function_body corrisponde a una serie di istruzioni Transact-SQL che popolano una variabile TABLE restituita.

• scalar_expression
  Specifica il valore scalare restituito dalla funzione scalare.

• TABLE
  Specifica che il valore restituito della funzione con valori di tabella è una tabella. Alle funzioni con valori di tabella è possibile passare solo costanti e variabili @local\_variables.

  Nelle funzioni inline con valori di tabella, il valore restituito TABLE viene definito tramite una sola istruzione SELECT. Alle funzioni inline non sono associate variabili restituite.

  Nelle funzioni a istruzioni multiple con valori di tabella, @return\_variable è una variabile TABLE utilizzata per l'archiviazione e l'accumulo delle righe da restituire come valore della funzione. È possibile specificare @return\_variable solo per le funzioni Transact-SQL e non per le funzioni CLR.

  Attenzione

  La creazione di un join di una funzione con valori di tabella con istruzioni multiple in una clausola FROM è possibile, ma può comportare prestazioni insufficienti. In SQL Server non è possibile utilizzare tutte le tecniche ottimizzate con alcune istruzioni che possono essere incluse in una funzione con istruzioni multiple, comportando un piano di query non ottimale. Per ottenere le migliori prestazioni possibili, utilizzare i join tra le tabelle di base anziché tra le funzioni, quando possibile.

• select_stmt
  Istruzione SELECT che definisce il valore restituito di una funzione inline con valori di tabella.

• ORDER (<order_clause>)
  Specifica l'ordine in base a cui vengono restituiti i risultati dalla funzione con valori di tabella. Per ulteriori informazioni, vedere la sezione "Informazioni sull'utilizzo dell'ordinamento" più avanti in questo argomento.

• EXTERNAL NAME <method_specifier> assembly_name.class_name.method_name
  Specifica il metodo di un assembly da associare alla funzione. assembly_name deve corrispondere a un assembly esistente in SQL Server nel database corrente con visibilità attivata. class_name deve essere un identificatore di SQL Server valido e deve esistere come una classe nell'assembly. Se alla classe è stato assegnato un nome completo con lo spazio dei nomi le cui parti sono separate da un punto (.), il nome della classe deve essere delimitato tramite parentesi ([ ]) o virgolette (" "). method_name deve essere un identificatore SQL Server valido e deve esistere come metodo statico nella classe specificata.

  [!NOTA]

  Per impostazione predefinita, SQL Server non può eseguire il codice CLR. È possibile creare, modificare ed eliminare gli oggetti di database che fanno riferimento a moduli CLR; tuttavia non è possibile eseguire questi riferimenti in SQL Server finché non viene abilitata l'opzione clr enabled. Per abilitare questa opzione, utilizzare sp_configure.

  [!NOTA]

  Questa opzione non è disponibile in un database indipendente.

• <table_type_definition> ( { <column_definition> <column_constraint>   | <computed_column_definition> }   [ <table_constraint> ] [ ,...n ] )
  Definisce il tipo di dati della tabella per una funzione Transact-SQL. La dichiarazione di tabella include definizioni di colonna, nonché vincoli di colonna o tabella. La tabella viene sempre inserita nel filegroup primario.

• < clr_table_type_definition > ( { column_namedata_type } [ ,...n ] )
  Definisce i tipi di dati della tabella per una funzione CLR. La dichiarazione di tabella include solo nomi di colonna e tipi di dati. La tabella viene sempre inserita nel filegroup primario.

<function_option>::= e <clr_function_option>::=

Specifica che la funzione includerà una o più delle opzioni seguenti.

• ENCRYPTION
  Indica che il testo originale dell'istruzione CREATE FUNCTION verrà convertito da Motore di database in un formato offuscato. L'output dell'offuscamento non è visibile direttamente nelle viste del catalogo. Gli utenti che non hanno accesso a tabelle di sistema o file del database non possono recuperare il testo offuscato. Il testo, tuttavia, sarà disponibile per gli utenti con privilegi di accesso a tabelle di sistema attraverso la porta DAC oppure di accesso diretto a file del database. Gli utenti in grado di collegare un debugger al processo del server possono inoltre recuperare la procedura originale dalla memoria in fase di esecuzione. Per ulteriori informazioni sull'accesso ai metadati di sistema, vedere Configurazione della visibilità dei metadati.

  Tramite questa opzione è possibile evitare la pubblicazione della funzione come parte della replica di SQL Server. Questa opzione non può essere specificata per le funzioni CLR.

• SCHEMABINDING
  Specifica che la funzione è associata agli oggetti di database a cui fa riferimento. Quando la clausola SCHEMABINDING viene specificata, non è possibile apportare agli oggetti di base modifiche che hanno effetto sulla definizione della funzione. È necessario prima modificare o eliminare la definizione della funzione per rimuovere le dipendenze dall'oggetto da modificare.

  L'associazione della funzione agli oggetti cui fa riferimento viene rimossa solo quando viene eseguita una delle azioni seguenti:

  • La funzione viene eliminata.

  • La funzione viene modificata tramite l'istruzione ALTER senza specificare l'opzione SCHEMABINDING.

  Una funzione può essere associata a uno schema solo se vengono soddisfatte le condizioni seguenti:

  • Si tratta di una funzione Transact-SQL.

  • Le funzioni definite dall'utente e le viste a cui la funzione fa riferimento sono anch'esse associate a uno schema.

  • Per i riferimenti agli oggetti, nella funzione viene utilizzato un nome in due parti.

  • La funzione e gli oggetti a cui fa riferimento appartengono allo stesso database.

  • L'utente che ha eseguito l'istruzione CREATE FUNCTION dispone dell'autorizzazione REFERENCES per gli oggetti di database a cui la funzione fa riferimento.

• RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT
  Specifica l'attributo OnNULLCall di una funzione a valori scalari. Se omesso, viene utilizzata l'opzione CALLED ON NULL INPUT per impostazione predefinita. Ciò significa che viene eseguito il corpo della funzione anche se come argomento viene passato NULL.

  Se in una funzione CLR si specifica l'opzione RETURNS NULL ON NULL INPUT, SQL Server restituisce NULL se uno qualsiasi degli argomenti ricevuti è NULL senza effettivamente richiamare il corpo della funzione. Se per il metodo di una funzione CLR specificato in <method_specifier> è stato definito un attributo personalizzato impostato su RETURNS NULL ON NULL INPUT, ma l'istruzione CREATE FUNCTION include CALLED ON NULL INPUT, l'istruzione CREATE FUNCTION risulta prioritaria. Non è possibile specificare l'attributo OnNULLCall per le funzioni CLR con valori di tabella.

• Clausola EXECUTE AS
  Specifica il contesto di sicurezza nel quale viene eseguita la funzione definita dall'utente. Sarà pertanto possibile controllare l'account utente utilizzato da SQL Server per convalidare le autorizzazioni per qualsiasi oggetto di database a cui la funzione fa riferimento.

  [!NOTA]

  Non è possibile specificare la clausola EXECUTE AS per le funzioni inline definite dall'utente.

  Per ulteriori informazioni, vedere Clausola EXECUTE AS (Transact-SQL).

< column_definition >::=

Definisce il tipo di dati della tabella. La dichiarazione di tabella include definizioni di colonna e vincoli. Per le funzioni CLR è possibile specificare solo column_name e data_type.

• column_name
  Nome di una colonna della tabella. I nomi delle colonne devono essere conformi alle regole per gli identificatori e devono essere univoci nella tabella. column_name può essere costituito da un numero di caratteri compreso tra 1 e 128.

• data_type
  Specifica il tipo di dati della colonna. Per le funzioni Transact-SQL sono consentiti tutti i tipi di dati, compresi i tipi CLR definiti dall'utente, eccetto il tipo di dati timestamp. Per le funzioni CLR sono consentiti tutti i tipi di dati, compresi i tipi CLR definiti dall'utente, eccetto i tipi di dati text, ntext, image, char, varchar, varchar(max) e timestamp. Non è possibile specificare il tipo non scalare cursor come tipo di dati della colonna nelle funzioni Transact-SQL o CLR.

• DEFAULT constant_expression
  Specifica il valore assegnato alla colonna quando non viene specificato un valore in modo esplicito durante un inserimento. constant_expression è una costante, un valore NULL o un valore della funzione di sistema. Le definizioni dell'opzione DEFAULT possono essere applicate a qualsiasi colonna eccetto quelle che includono la proprietà IDENTITY. Non è possibile specificare l'opzione DEFAULT per le funzioni CLR con valori di tabella.

• COLLATE collation_name
  Specifica le regole di confronto per la colonna. Se omesso, alla colonna vengono assegnate le regole di confronto predefinite del database. È possibile utilizzare nomi di regole di confronto di Windows o SQL. Per ulteriori informazioni sulle regole di confronto e per un elenco di tali regole, vedere Windows_collation_name (Transact-SQL) e Nome delle regole di confronto di SQL Server (Transact-SQL).

  La clausola COLLATE consente di modificare le regole di confronto solo delle colonne con tipi di dati char, varchar, nchar e nvarchar.

  Non è possibile specificare l'opzione COLLATE per le funzioni CLR con valori di tabella.

• ROWGUIDCOL
  Indica che la nuova colonna è un identificatore univoco di riga globale. È possibile designare come colonna ROWGUIDCOL una sola colonna di tipo uniqueidentifier per ogni tabella. La proprietà ROWGUIDCOL può essere assegnata solo a una colonna uniqueidentifier.

  La proprietà ROWGUIDCOL non impone l'univocità dei valori archiviati nella colonna e non genera automaticamente valori per le nuove righe inserite nella tabella. Per generare valori univoci per ogni colonna, utilizzare la funzione NEWID nelle istruzioni INSERT. È possibile specificare un valore predefinito. Non è tuttavia possibile specificare l'opzione NEWID come valore predefinito.

• IDENTITY
  Indica che la nuova colonna è una colonna Identity. Quando si aggiunge una nuova riga alla tabella, SQL Server assegna alla colonna un valore univoco e incrementale. Le colonne Identity vengono comunemente utilizzate in combinazione con vincoli PRIMARY KEY e svolgono la funzione di identificatore di riga univoco per la tabella. È possibile assegnare la proprietà IDENTITY a colonne di tipo tinyint, smallint, int, bigint, decimal(p,0) o numeric(p,0). È possibile creare una sola colonna Identity per ogni tabella. Non è possibile associare valori predefiniti e vincoli DEFAULT alle colonne Identity. È necessario specificare sia il valore per seed sia il valore per increment oppure nessuno dei due valori. In questo secondo caso, il valore predefinito è (1,1).

  Non è possibile specificare l'opzione IDENTITY per le funzioni CLR con valori di tabella.

  • seed
    Valore integer da assegnare alla prima riga della tabella.

  • increment
    Valore integer da aggiungere al valore seed per le righe successive della tabella.

< column_constraint >::= e < table_constraint>::=

Definisce il vincolo per una colonna o tabella specificata. Per le funzioni CLR l'unico tipo di vincolo consentito è NULL. I vincoli denominati non sono consentiti.

• NULL | NOT NULL
  Determina se i valori NULL sono supportati nella colonna. L'opzione NULL non è esattamente un vincolo, ma può essere specificata allo stesso modo di NOT NULL. Non è possibile specificare l'opzione NOT NULL per le funzioni CLR con valori di tabella.

• PRIMARY KEY
  Vincolo che impone l'integrità di entità per una colonna specificata tramite un indice univoco. Nelle funzioni definite dall'utente con valori di tabella, il vincolo PRIMARY KEY può essere creato solo su una colonna per tabella. Non è possibile specificare il vincolo PRIMARY KEY per le funzioni CLR con valori di tabella.

• UNIQUE
  Vincolo che impone l'integrità di entità per una o più colonne specificate tramite un indice univoco. Una tabella può includere più vincoli UNIQUE. Non è possibile specificare il vincolo UNIQUE per le funzioni CLR con valori di tabella.

• CLUSTERED | NONCLUSTERED
  Definisce la creazione di un indice cluster o non cluster per il vincolo PRIMARY KEY o UNIQUE. I vincoli PRIMARY KEY utilizzano l'opzione CLUSTERED, mentre i vincoli UNIQUE utilizzano l'opzione NONCLUSTERED.

  L'opzione CLUSTERED può essere specificata solo per un vincolo. Se per un vincolo UNIQUE si specifica CLUSTERED e viene specificato anche un vincolo PRIMARY KEY, quest'ultimo utilizza l'opzione NONCLUSTERED.

  Non è possibile specificare le opzioni CLUSTERED e NON CLUSTERED per le funzioni CLR con valori di tabella.

• CHECK
  Vincolo che impone l'integrità di dominio tramite la limitazione dei valori che è possibile inserire in una o più colonne. Non è possibile specificare i vincoli CHECK per le funzioni CLR con valori di tabella.

  • logical_expression
    Espressione logica che restituisce TRUE o FALSE.

<computed_column_definition>::=

Specifica una colonna calcolata. Per ulteriori informazioni sulle colonne calcolate, vedere CREATE TABLE (Transact-SQL).

• column_name
  Nome della colonna calcolata.

• computed_column_expression
  Espressione che definisce il valore di una colonna calcolata.

<index_option>::=

Specifica le opzioni per l'indice PRIMARY KEY o UNIQUE. Per ulteriori informazioni sulle opzioni per gli indici, vedere CREATE INDEX (Transact-SQL).

• PAD_INDEX = { ON | OFF }
  Specifica il riempimento dell'indice. Il valore predefinito è OFF.

• FILLFACTOR = fillfactor
  Specifica una percentuale indicante il livello di riempimento del livello foglia di ogni pagina di indice applicato dal Motore di database durante la creazione o la modifica dell'indice. fillfactor deve essere un valore intero compreso tra 1 e 100. Il valore predefinito è 0.

• IGNORE_DUP_KEY = { ON | OFF }
  Specifica l'errore restituito quando un'operazione di inserimento tenta di inserire valori di chiave duplicati in un indice univoco. L'opzione IGNORE_DUP_KEY viene applicata solo alle operazioni di inserimento eseguite dopo la creazione o la ricompilazione dell'indice. Il valore predefinito è OFF.

• STATISTICS_NORECOMPUTE = { ON | OFF }
  Specifica se devono essere ricalcolate le statistiche di distribuzione. Il valore predefinito è OFF.

• ALLOW_ROW_LOCKS = { ON | OFF }
  Specifica se sono consentiti blocchi di riga. Il valore predefinito è ON.

• ALLOW_PAGE_LOCKS = { ON | OFF }
  Specifica se sono consentiti blocchi a livello di pagina. Il valore predefinito è ON.

Procedure consigliate

Se una funzione definita dall'utente non viene creata tramite la clausola SCHEMABINDING, le modifiche apportate agli oggetti sottostanti possono influire sulla definizione della funzione e produrre risultati imprevisti quando viene richiamata. È consigliabile implementare uno dei metodi seguenti per assicurarsi che la funzione non diventi obsoleta in seguito a modifiche degli oggetti sottostanti:

• Specificare la clausola WITH SCHEMABINDING quando si crea la funzione. In questo modo, gli oggetti a cui si fa riferimento nella definizione della funzione possono essere modificati solo se viene modificata anche la funzione.

• Eseguire la stored procedure sp_refreshsqlmodule dopo avere modificato qualsiasi oggetto specificato nella definizione della funzione.

Tipi di dati

Se si specificano parametri in una funzione CLR, essi dovranno essere di tipo SQL Server come precedentemente definito per scalar_parameter_data_type. Per informazioni sul confronto tra i tipi di dati di sistema di SQL Server e i tipi di dati di integrazione CLR oppure i tipi di dati CLR di .NET Framework, vedere Mapping dei dati dei parametri CLR.

Affinché SQL Server faccia riferimento al metodo corretto in caso di un sovraccarico a livello di classe, è necessario che il metodo specificato in <method_specifier>:

• Riceva lo stesso numero di parametri specificati in [ ,...n ].

• Riceva tutti i parametri in base al valore e non in base al riferimento.

• Utilizzi tipi di parametro compatibili con quelli specificati nella funzione SQL Server.

Se per il tipo di dati restituito della funzione CLR viene specificato un tipo tabella (RETURNS TABLE), il tipo di dati restituito del metodo specificato in <method_specifier> deve essere IEnumerator o IEnumerable. Si presuppone che l'interfaccia venga implementata dall'autore della funzione. A differenza delle funzioni Transact-SQL, le funzioni CLR non possono includere vincoli PRIMARY KEY, UNIQUE o CHECK in <table_type_definition>. I tipi di dati delle colonne specificati in <table_type_definition> devono corrispondere ai tipi delle rispettive colonne del set di risultati restituito dal metodo specificato in <method_specifier> in fase di esecuzione. Questa verifica del tipo non viene eseguita al momento della creazione della funzione.

Per ulteriori informazioni sulla programmazione delle funzioni CLR, vedere Funzioni CLR definite dall'utente.

Osservazioni generali

È possibile richiamare funzioni a valori scalari laddove vengono utilizzate espressioni scalari. Sono incluse le colonne calcolate e le definizioni dei vincoli CHECK. Le funzioni a valori scalari possono essere eseguite anche tramite l'utilizzo dell'istruzione EXECUTE. È necessario richiamare funzioni a valori scalari utilizzando almeno il nome in due parti della funzione. Per ulteriori informazioni sui nomi composti da più parti, vedere Convenzioni della sintassi Transact-SQL (Transact-SQL). Le funzioni con valori di tabella possono essere richiamate dove sono consentite le espressioni di tabella nella clausola FROM delle istruzioni SELECT, INSERT, UPDATE o DELETE. Per ulteriori informazioni, vedere Eseguire funzioni definite dall'utente.

Interoperabilità

In una funzione le istruzioni seguenti sono valide:

• Istruzioni di assegnazione.

• Istruzioni per il controllo di flusso, escluse le istruzioni TRY...CATCH.

• Istruzioni DECLARE che definiscono le variabili dati locali e i cursori locali.

• Istruzioni SELECT contenenti gli elenchi di selezione con espressioni che assegnano valori alle variabili locali.

• Operazioni di cursore che fanno riferimento a cursori locali dichiarati, aperti, chiusi e deallocati nella funzione. Sono consentite solo istruzioni FETCH che assegnano valori alle variabili locali tramite la clausola INTO. Non sono consentite istruzioni FETCH che restituiscono dati al client.

• Istruzioni INSERT, UPDATE e DELETE che modificano le variabili di tabella locali.

• Istruzioni EXECUTE che richiamano stored procedure estese.

• Per ulteriori informazioni, vedere Creazione di funzioni definite dall'utente (Motore di database).

Interoperabilità delle colonne calcolate

In SQL Server 2005 e versioni successive le funzioni includono le proprietà descritte di seguito. I valori di tali proprietà determinano se le funzioni sono utilizzabili nelle colonne calcolate che possono essere persistenti o indicizzate.

Proprietà	Descrizione	Note
IsDeterministic	La funzione è deterministica o non deterministica.	L'accesso ai dati locali è consentito nelle funzioni deterministiche. Ad esempio, le funzioni che restituiscono sempre lo stesso valore ogni volta che vengono richiamate utilizzando un set specifico di valori di input e con lo stesso stato del database vengono definite funzioni deterministiche.
IsPrecise	La funzione è precisa o imprecisa.	Le funzioni imprecise includono operazioni quali le operazioni a virgola mobile.
IsSystemVerified	Le proprietà relative alla precisione e le proprietà deterministiche della funzione possono essere verificate tramite SQL Server.
SystemDataAccess	La funzione accede ai dati di sistema (cataloghi di sistema o tabelle di sistema virtuali) nell'istanza locale di SQL Server.
UserDataAccess	La funzione accede ai dati utente nell'istanza locale di SQL Server.	Include le tabelle definite dall'utente e le tabelle temporanee, ma non le variabili di tabella.

Le proprietà relative alla precisione e le proprietà deterministiche delle funzioni Transact-SQL vengono definite automaticamente da SQL Server. Le proprietà relative all'accesso ai dati e le proprietà deterministiche delle funzioni CLR possono essere specificate dall'utente. Per ulteriori informazioni, vedere Panoramica degli attributi personalizzati dell'integrazione con CLR.

Le funzioni devono essere create con associazione allo schema per essere deterministiche.

Per visualizzare i valori correnti di queste proprietà, utilizzare OBJECTPROPERTYEX.

È possibile utilizzare una colonna calcolata che richiama una funzione definita dall'utente in un indice se per le proprietà della funzione definita dall'utente sono stati impostati i valori seguenti:

• IsDeterministic = true

• IsSystemVerified = true (a meno che la colonna calcolata non sia persistente)

• UserDataAccess = false

• SystemDataAccess = false

Per ulteriori informazioni, vedere Indici per le colonne calcolate.

Chiamata di stored procedure estese da funzioni

Una stored procedure estesa, quando viene richiamata dall'interno di una funzione, non può restituire set di risultati al client. Le API ODS che restituiscono set di risultati al client restituiscono FAIL. La stored procedure estesa può riconnettersi a un'istanza di SQL Server, tuttavia è necessario che non partecipi alla stessa transazione della funzione che l'ha richiamata.

In modo analogo a una chiamata eseguita dall'interno di un batch o di una stored procedure, la stored procedure estesa viene eseguita nel contesto dell'account di sicurezza di Windows in base a cui SQL Server è in esecuzione. È importante che il proprietario della stored procedure prenda in considerazione questo fattore quando concede l'autorizzazione EXECUTE agli utenti.

Limitazioni e restrizioni

Non è possibile utilizzare funzioni definite dall'utente per eseguire azioni che modificano lo stato del database.

Le funzioni definite dall'utente non possono contenere una clausola OUTPUT INTO che ha una tabella come destinazione.

Nella definizione di una funzione Transact-SQL definita dall'utente non è possibile includere le istruzioni di Service Broker seguenti:

• BEGIN DIALOG CONVERSATION

• END CONVERSATION

• GET CONVERSATION GROUP

• MOVE CONVERSATION

• RECEIVE

• SEND

È possibile nidificare le funzioni definite dall'utente, ovvero una funzione definita dall'utente ne può richiamare un'altra. Il livello di nidificazione aumenta all'avvio della funzione richiamata e diminuisce al termine dell'esecuzione della funzione. Le funzioni definite dall'utente possono essere nidificate fino a un massimo di 32 livelli. Se viene superato il livello massimo di nidificazioni, l'intera sequenza di funzioni chiamanti ha esito negativo. Qualsiasi riferimento al codice gestito presente in una funzione Transact-SQL definita dall'utente viene considerato come un livello nel contesto del limite di 32 livelli di nidificazione. I metodi richiamati da codice gestito non vengono inclusi nel conteggio per questo limite.

Utilizzo dell'ordinamento nelle funzioni CLR con valori di tabella

Quando si utilizza la clausola ORDER in funzioni CLR con valori di tabella, attenersi alle linee guida seguenti:

• È necessario assicurarsi che i risultati siano ordinati sempre in base all'ordine specificato. Se i risultati non sono nell'ordine specificato, in SQL Server verrà generato un messaggio di errore quando viene eseguita la query.

• Se è specificata una clausola ORDER, l'output della funzione con valori di tabella deve essere ordinato in base alle regole di confronto della colonna (esplicite o implicite). Se, ad esempio, le regole di confronto della colonna sono impostate per il cinese (in base a quanto specificato nella DDL per la funzione con valori di tabella o ottenuto dalle regole di confronto del database), i risultati restituiti devono essere ordinati in base alle regole di ordinamento per il cinese.

• La clausola ORDER, se specificata, viene sempre verificata da SQL Server quando vengono restituiti i risultati, indipendentemente dal fatto che venga utilizzata da Query Processor per eseguire ulteriori ottimizzazioni. Utilizzare la clausola ORDER solo se si è certi che sia utile per Query Processor.

• Query Processor di SQL Server utilizza automaticamente la clausola ORDER nei casi seguenti:

  • Query di inserimento in cui la clausola ORDER è compatibile con un indice.

  • Clausole ORDER BY compatibili con la clausola ORDER.

  • Aggregazioni in cui GROUP BY è compatibile con la clausola ORDER.

  • Aggregazioni DISTINCT in cui le colonne distinte sono compatibili con la clausola ORDER.

La clausola ORDER non garantisce risultati ordinati quando viene eseguita una query SELECT, a meno che nella query non venga specificata anche la clausola ORDER BY. Vedere sys.function_order_columns (Transact-SQL) per informazioni su come eseguire una query relativa alle colonne incluse nell'ordinamento per le funzioni con valori di tabella.

Metadati

Nella tabella seguente vengono elencate le viste del catalogo di sistema utilizzate per restituire i metadati sulle funzioni definite dall'utente.

Vista di sistema	Descrizione
sys.sql_modules	Visualizza la definizione delle funzioni Transact-SQL definite dall'utente. Ad esempio: USE AdventureWorks2012; GO SELECT definition, type FROM sys.sql_modules AS m JOIN sys.objects AS o ON m.object_id = o.object_id AND type IN ('FN', 'IF', 'TF'); GO La definizione delle funzioni create tramite l'opzione ENCRYPTION non possono essere visualizzate mediante la vista del catalogo sys.sql_modules. Vengono tuttavia visualizzate altre informazioni sulle funzioni crittografate.
sys.assembly_modules	Visualizza le informazioni sulle funzioni CLR definite dall'utente.
sys.parameters	Visualizza le informazioni sui parametri definiti nelle funzioni definite dall'utente.
sys.sql_expression_dependencies	Visualizza gli oggetti sottostanti a cui fa riferimento una funzione.

Autorizzazioni

È necessario disporre dell'autorizzazione CREATE FUNCTION nel database e dell'autorizzazione ALTER per lo schema in cui la funzione è in fase di creazione. Se per la funzione viene specificato un tipo definito dall'utente, è necessario disporre dell'autorizzazione EXECUTE per tale tipo.

Esempi

A.Utilizzo di una funzione definita dall'utente a valori scalari per il calcolo della settimana ISO

Nell'esempio seguente viene creata la funzione definita dall'utente ISOweek. Questa funzione calcola il numero di settimana ISO in base a un argomento di data specificato. Per consentire alla funzione di eseguire il calcolo correttamente, è necessario richiamare SET DATEFIRST 1 prima della funzione.

Nell'esempio viene inoltre illustrato l'utilizzo della clausola EXECUTE AS per specificare il contesto di sicurezza in cui è possibile eseguire una stored procedure. Nell'esempio, l'opzione CALLER specifica che la procedura verrà eseguita nel contesto dell'utente che l'ha richiamata. Le altre opzioni che è possibile specificare sono SELF, OWNER e user_name.

Di seguito è riportata la chiamata della funzione, in cui DATEFIRST è impostato su 1.

USE AdventureWorks2012;
GO
IF OBJECT_ID (N'dbo.ISOweek', N'FN') IS NOT NULL
    DROP FUNCTION dbo.ISOweek;
GO
CREATE FUNCTION dbo.ISOweek (@DATE datetime)
RETURNS int
WITH EXECUTE AS CALLER
AS
BEGIN
     DECLARE @ISOweek int;
     SET @ISOweek= DATEPART(wk,@DATE)+1
          -DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104');
--Special cases: Jan 1-3 may belong to the previous year
     IF (@ISOweek=0) 
          SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1 
               AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1;
--Special case: Dec 29-31 may belong to the next year
     IF ((DATEPART(mm,@DATE)=12) AND 
          ((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))
          SET @ISOweek=1;
     RETURN(@ISOweek);
END;
GO
SET DATEFIRST 1;
SELECT dbo.ISOweek(CONVERT(DATETIME,'12/26/2004',101)) AS 'ISO Week';

Set di risultati:

ISO Week

----------------

52

B.Creazione di una funzione inline con valori di tabella

Nell'esempio seguente viene restituita una funzione inline con valori di tabella. Vengono restituite tre colonne ProductID, Name e il valore aggregato dei totali dell'anno in corso per negozio come YTD Total per ogni prodotto venduto al negozio.

USE AdventureWorks2012;
GO
IF OBJECT_ID (N'Sales.ufn_SalesByStore', N'IF') IS NOT NULL
    DROP FUNCTION Sales.ufn_SalesByStore;
GO
CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int)
RETURNS TABLE
AS
RETURN 
(
    SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'Total'
    FROM Production.Product AS P 
    JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID
    JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID
    JOIN Sales.Customer AS C ON SH.CustomerID = C.CustomerID
    WHERE C.StoreID = @storeid
    GROUP BY P.ProductID, P.Name
);
GO

Per richiamare la funzione, eseguire la query seguente.

SELECT * FROM Sales.ufn_SalesByStore (602);

C.Creazione di una funzione a istruzioni multiple con valori di tabella

Nell'esempio seguente viene creata la funzione con valori di tabella fn_FindReports(InEmpID). Se si specifica un ID dipendente valido, la funzione restituisce una tabella che include tutti i dipendenti che hanno rapporti diretti o indiretti con il dipendente specificato. La funzione utilizza un'espressione di tabella comune ricorsiva (CTE, Common Table Expression) per restituire l'elenco gerarchico dei dipendenti. Per ulteriori informazioni sulle espressioni CTE ricorsive, vedere WITH common_table_expression (Transact-SQL).

USE AdventureWorks2012;
GO
IF OBJECT_ID (N'dbo.ufn_FindReports', N'TF') IS NOT NULL
    DROP FUNCTION dbo.ufn_FindReports;
GO
CREATE FUNCTION dbo.ufn_FindReports (@InEmpID INTEGER)
RETURNS @retFindReports TABLE 
(
    EmployeeID int primary key NOT NULL,
    FirstName nvarchar(255) NOT NULL,
    LastName nvarchar(255) NOT NULL,
    JobTitle nvarchar(50) NOT NULL,
    RecursionLevel int NOT NULL
)
--Returns a result set that lists all the employees who report to the 
--specific employee directly or indirectly.*/
AS
BEGIN
WITH EMP_cte(EmployeeID, OrganizationNode, FirstName, LastName, JobTitle, RecursionLevel) -- CTE name and columns
    AS (
        SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, 0 -- Get the initial list of Employees for Manager n
        FROM HumanResources.Employee e 
            INNER JOIN Person.Person p 
            ON p.BusinessEntityID = e.BusinessEntityID
        WHERE e.BusinessEntityID = @InEmpID
        UNION ALL
        SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, RecursionLevel + 1 -- Join recursive member to anchor
        FROM HumanResources.Employee e 
            INNER JOIN EMP_cte
            ON e.OrganizationNode.GetAncestor(1) = EMP_cte.OrganizationNode
            INNER JOIN Person.Person p 
            ON p.BusinessEntityID = e.BusinessEntityID
        )
-- copy the required columns to the result of the function 
   INSERT @retFindReports
   SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel
   FROM EMP_cte 
   RETURN
END;
GO
-- Example invocation
SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel
FROM dbo.ufn_FindReports(1); 

GO

D.Creazione di una funzione CLR

Nell'esempio viene creata la funzione CLR len_s, ma prima che venga effettivamente creata la funzione, l'assembly SurrogateStringFunction.dll viene registrato nel database locale.

DECLARE @SamplesPath nvarchar(1024);
-- You may have to modify the value of this variable if you have
-- installed the sample in a location other than the default location.
SELECT @SamplesPath = REPLACE(physical_name, 'Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\master.mdf', 'Microsoft SQL Server\100\Samples\Engine\Programmability\CLR\') 
    FROM master.sys.database_files 
    WHERE name = 'master';

CREATE ASSEMBLY [SurrogateStringFunction]
FROM @SamplesPath + 'StringManipulate\CS\StringManipulate\bin\debug\SurrogateStringFunction.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO

CREATE FUNCTION [dbo].[len_s] (@str nvarchar(4000))
RETURNS bigint
AS EXTERNAL NAME [SurrogateStringFunction].[Microsoft.Samples.SqlServer.SurrogateStringFunction].[LenS];
GO

Per un esempio relativo alla creazione di una funzione CLR con valori di tabella, vedere Funzioni CLR con valori di tabella.

Vedere anche

Riferimento

ALTER FUNCTION (Transact-SQL)

DROP FUNCTION (Transact-SQL)

OBJECTPROPERTYEX (Transact-SQL)

sys.sql_modules (Transact-SQL)

sys.assembly_modules (Transact-SQL)

EXECUTE (Transact-SQL)

EVENTDATA (Transact-SQL)

Altre risorse

Funzioni CLR definite dall'utente