CREA FUNZIONE (Transact-SQL)

2025-06-29

Si applica a:SQL Server Database SQL di Azure Istanza Gestita di SQL di Azure Database SQL in Microsoft Fabric

Crea una funzione definita dall'utente, ovvero una routine Transact-SQL o Common Language Runtime (CLR). Una funzione definita dall'utente accetta parametri, esegue un'azione, ad esempio un calcolo complesso, e restituisce il risultato di tale azione come valore. Il valore restituito può essere un valore scalare (singolo) o una tabella.

Usare CREATE FUNCTION per creare una routine T-SQL riutilizzabile che può essere usata in questi modi:

In Transact-SQL dichiarazioni quali SELECT
Nelle applicazioni che chiamano la funzione
Nella definizione di un'altra funzione definita dall'utente
Per parametrizzare una vista o migliorare la funzionalità di una vista indicizzata
Per definire una colonna in una tabella
Per definire un CHECK vincolo su una colonna
Per sostituire una stored procedure
Utilizzare una funzione inline come predicato di filtro per un criterio di sicurezza

L'integrazione di .NET Framework CLR in SQL Server viene illustrata in questo articolo. L'integrazione con CLR non si applica al database SQL di Azure.

Annotazioni

Per Microsoft Fabric Data Warehouse o Azure Synapse Analytics, vedere CREATE FUNCTION (Azure Synapse Analytics e Microsoft Fabric).

Suggerimento

È possibile specificare CREATE OR ALTER FUNCTION di creare una nuova funzione se non esiste con tale nome o modificare una funzione esistente in una singola istruzione.

Convenzioni relative alla sintassi Transact-SQL

Sintassi

Sintassi per Transact-SQL funzioni scalari.

CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ NULL ]
 [ = default ] [ READONLY ] }
    [ , ...n ]
  ]
)
RETURNS return_data_type
    [ WITH <function_option> [ , ...n ] ]
    [ AS ]
    BEGIN
        function_body
        RETURN scalar_expression
    END
[ ; ]

Sintassi per Transact-SQL funzioni inline con valori di tabella.

CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ NULL ]
    [ = default ] [ READONLY ] }
    [ , ...n ]
  ]
)
RETURNS TABLE
    [ WITH <function_option> [ , ...n ] ]
    [ AS ]
    RETURN [ ( ] select_stmt [ ) ]
[ ; ]

Sintassi per Transact-SQL funzioni con valori di tabella con più istruzioni.

CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ NULL ]
    [ = default ] [ READONLY ] }
    [ , ...n ]
  ]
)
RETURNS @return_variable TABLE <table_type_definition>
    [ WITH <function_option> [ , ...n ] ]
    [ AS ]
    BEGIN
        function_body
        RETURN
    END
[ ; ]

Sintassi per Transact-SQL le clausole di funzione.

<function_option> ::=
{
    [ ENCRYPTION ]
  | [ SCHEMABINDING ]
  | [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
  | [ EXECUTE_AS_Clause ]
  | [ INLINE = { ON | OFF } ]
}

<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 }
}

Sintassi per le funzioni scalari CLR.

CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ NULL ]
    [ = default ] }
    [ , ...n ]
)
RETURNS { return_data_type }
    [ WITH <clr_function_option> [ , ...n ] ]
    [ AS ] EXTERNAL NAME <method_specifier>
[ ; ]

Sintassi per le funzioni CLR con valori di tabella.

CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ NULL ]
    [ = default ] }
    [ , ...n ]
)
RETURNS TABLE <clr_table_type_definition>
    [ WITH <clr_function_option> [ , ...n ] ]
    [ ORDER ( <order_clause> ) ]
    [ AS ] EXTERNAL NAME <method_specifier>
[ ; ]

Sintassi per le clausole di funzione CLR.

<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 ] )

Sintassi OLTP in memoria per funzioni scalari definite dall'utente compilate in modo nativo.

CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
 ( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type
    [ NULL | NOT NULL ] [ = default ] [ READONLY ] }
    [ , ...n ]
  ]
)
RETURNS return_data_type
     WITH <function_option> [ , ...n ]
    [ AS ]
    BEGIN ATOMIC WITH (set_option [ , ... n ] )
        function_body
        RETURN scalar_expression
    END

<function_option> ::=
{
  |  NATIVE_COMPILATION
  |  SCHEMABINDING
  | [ EXECUTE_AS_Clause ]
  | [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
}

Argomenti

OR ALTER

Si applica a: SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL SP 1 e versioni successive e database SQL di Azure.

Modifica in modo condizionale la funzione solo se esiste già.

La sintassi facoltativa OR ALTER è disponibile per CLR, a partire da SQL Server 2016 (13.x) SP 1 CU 1.

schema_name

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

function_name

Nome della funzione definita dall'utente. I nomi delle funzioni devono essere conformi alle regole per gli identificatori e devono essere univoci all'interno del database e del relativo schema.

Le parentesi sono obbligatorie dopo il nome della funzione, anche se non è specificato un parametro.

@parameter_name

Parametro nella funzione definita dall'utente. È possibile dichiarare uno o più parametri.

Una funzione può avere un massimo di 2.100 parametri. Il valore di ogni parametro dichiarato deve essere fornito dall'utente quando la funzione viene eseguita, a meno che non sia definito un valore predefinito per il parametro.

Specificare un nome di parametro utilizzando un segno di chiocciola (@) come primo carattere. Il nome del parametro deve essere conforme alle regole per gli identificatori. I parametri sono locali alla funzione; Gli stessi nomi di parametro possono essere utilizzati in altre funzioni. I parametri possono prendere il posto solo delle costanti; Non possono essere utilizzati al posto dei nomi delle tabelle, delle colonne o dei nomi di altri oggetti del database.

ANSI_WARNINGS non viene rispettato quando si passano parametri in una stored procedure, in una funzione definita dall'utente o quando si dichiarano e si impostano 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 alle dimensioni definite e l'istruzione INSERT o UPDATE avrà esito positivo.

[ type_schema_name. ] parameter_data_type

Il tipo di dati del parametro e, facoltativamente, lo schema a cui appartiene. Per le funzioni Transact-SQL, sono consentiti tutti i tipi di dati, inclusi i tipi CLR definiti dall'utente e i tipi di tabella definiti dall'utente, ad eccezione del tipo di dati timestamp . Per le funzioni CLR, sono consentiti tutti i tipi di dati, inclusi i tipi CLR definiti dall'utente, ad eccezione dei tipi di dati text, ntext, image, tabelle definite dall'utente e timestamp . I tipi non scalari, cursor e table, non possono essere specificati come tipo di dati di parametro nelle funzioni Transact-SQL o CLR.

Se type_schema_name non viene specificato, il motore di database cerca nell'ordine scalar_parameter_data_type seguente:

Schema che contiene i nomi dei tipi di dati del sistema SQL Server.
Schema predefinito dell'utente corrente nel database corrente.
Schema dbo nel database corrente.

[ = predefinito ]

Valore predefinito per il parametro. Se viene definito un valore predefinito , la funzione può essere eseguita senza specificare un valore per tale parametro.

I valori dei parametri predefiniti possono essere specificati per le funzioni CLR, ad eccezione dei tipi di dati varchar(max) e varbinary(max).

Quando un parametro della funzione ha un valore predefinito, la parola DEFAULT chiave deve essere specificata quando la funzione viene chiamata per recuperare il valore predefinito. Questo comportamento è diverso dall'utilizzo di parametri con valori predefiniti nelle stored procedure, in cui l'omissione del parametro implica anche il valore predefinito. Tuttavia, la DEFAULT parola chiave non è obbligatoria quando si richiama una funzione scalare usando l'istruzione EXECUTE .

SOLA LETTURA

Indica che il parametro non può essere aggiornato o modificato all'interno della definizione della funzione. READONLY è necessario per i parametri del tipo di tabella definiti dall'utente e non può essere usato per altri tipi di parametro.

return_data_type

Valore restituito di una funzione scalare definita dall'utente. Per le funzioni Transact-SQL, sono consentiti tutti i tipi di dati, inclusi i tipi CLR definiti dall'utente, ad eccezione del tipo di dati timestamp . Per le funzioni CLR, sono consentiti tutti i tipi di dati, inclusi i tipi CLR definiti dall'utente, ad eccezione dei tipi di dati text, ntext, image e timestamp . I tipi non scalari, cursor e table, non possono essere specificati come tipo di dati restituito nelle funzioni Transact-SQL o CLR.

function_body

Specifica che una serie di istruzioni Transact-SQL, che insieme non producono un effetto collaterale, ad esempio la modifica di una tabella, definiscono il valore della funzione. function_body viene utilizzato solo nelle funzioni scalari e nelle funzioni con valori di tabella multi-istruzione.

Nelle funzioni scalari, function_body è una serie di istruzioni Transact-SQL che insieme restituiscono un valore scalare.

Nelle funzioni MSTVF, function_body è una serie di istruzioni Transact-SQL che popolano una TABLE variabile restituita.

scalar_expression

Specifica il valore scalare restituito dalla funzione scalare.

TABELLA

Specifica che il valore restituito della funzione con valori di tabella (TVF) è una tabella. Solo le costanti e @local_variables possono essere passate alle TVF.

Nelle funzioni con valori di tabella inline, il TABLE valore restituito viene definito tramite una singola SELECT istruzione. Le funzioni inline non hanno variabili di ritorno associate.

Nelle funzioni con valori di tabella con più istruzioni (MSTVFs), @return_variable è una TABLE variabile, usata per archiviare e accumulare le righe che devono essere restituite come valore della funzione. @ return_variable può essere specificato solo per le funzioni Transact-SQL e non per le funzioni CLR.

select_stmt

Singola SELECT istruzione che definisce il valore restituito di una funzione inline con valori di tabella.

ORDINE (<order_clause>)

Specifica l'ordine in cui i risultati vengono restituiti dalla funzione con valori di tabella. Per ulteriori informazioni, vedere la sezione Utilizzo dell'ordinamento nelle funzioni con valori di tabella CLR più avanti in questo articolo.

NOME ESTERNO <method_specifier>assembly_name.class_name. method_name

Si applica a: SQL Server 2008 (10.0.x) SP 1 e versioni successive.

Specifica l'assembly e il metodo a cui deve fare riferimento il nome della funzione creata.

assembly_name - deve corrispondere a un valore nella name colonna di SELECT * FROM sys.assemblies;.

Nome utilizzato nell'istruzione CREATE ASSEMBLY .
class_name - deve corrispondere a un valore nella assembly_name colonna di SELECT * FROM sys.assembly_modules;.

Spesso il valore contiene un punto o un punto incorporato. In questi casi, la sintassi Transact-SQL richiede che il valore sia delimitato da una coppia di parentesi quadre ([]) o da una coppia di virgolette doppie ("").
method_name - deve corrispondere a un valore nella method_name colonna di SELECT * FROM sys.assembly_modules;.

Il metodo deve essere statico.

In un esempio tipico per MyFood.dll, in cui tutti i tipi si trovano nello MyFood spazio dei nomi, il EXTERNAL NAME valore potrebbe essere MyFood.[MyFood.MyClass].MyStaticMethod.

Per impostazione predefinita, SQL Server non è in grado di eseguire codice CLR. È possibile creare, modificare ed eliminare oggetti di database che fanno riferimento a moduli Common Language Runtime. Tuttavia, non è possibile eseguire questi riferimenti in SQL Server fino a quando non si abilita l'opzione clr enabled. Per abilitare questa opzione, utilizzare sp_configure. 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 le definizioni di colonna e i vincoli di colonna o tabella. La tabella viene sempre inserita nel filegroup primario.

< > clr_table_type_definition ( { column_namedata_type } [ , ... n ] )

Si applica a: SQL Server 2008 (10.0.x) SP 1 e versioni successive e database SQL di Azure (anteprima in alcune aree).

Definisce i tipi di dati della tabella per una funzione CLR. La dichiarazione della tabella include solo i nomi delle colonne e i tipi di dati. La tabella viene sempre inserita nel filegroup primario.

NULL | NOT NULL

Supportato solo per funzioni scalari definite dall'utente compilate in modo nativo. Per ulteriori informazioni, vedere Funzioni di User-Defined scalare per In-Memory OLTP.

Compilazione Nativa

Indica se una funzione definita dall'utente è compilata in modo nativo. Questo argomento è necessario per le funzioni scalari definite dall'utente compilate in modo nativo.

INIZIA ATOMIC CON

Obbligatorio e supportato solo per le funzioni scalari definite dall'utente compilate in modo nativo. Per ulteriori informazioni, vedere Blocchi atomici nelle procedure native.

SCHEMABINDING

L'argomento SCHEMABINDING è obbligatorio per le funzioni scalari definite dall'utente compilate in modo nativo.

ESEGUI COME

EXECUTE AS è necessario per le funzioni scalari definite dall'utente compilate in modo nativo.

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

Specifica che la funzione dispone di una o più delle opzioni seguenti.

CRIPTAZIONE

Si applica a: SQL Server 2008 (10.0.x) SP 1 e versioni successive.

Indica che Motore di database converte il testo originale dell'istruzione CREATE FUNCTION in un formato offuscato. L'output dell'offuscamento non è direttamente visibile in nessuna vista del catalogo. Gli utenti che non hanno accesso alle tabelle di sistema o ai file di database non possono recuperare il testo offuscato. Tuttavia, il testo è disponibile per gli utenti con privilegi che possono accedere alle tabelle di sistema tramite la connessione di diagnostica per gli amministratori di database o accedere direttamente ai file di database. Inoltre, gli utenti che possono collegare un debugger al processo server possono recuperare la procedura originale dalla memoria in fase di esecuzione. Per ulteriori informazioni sull'accesso ai metadati del sistema, vedere Configurazione della visibilità dei metadati.

L'utilizzo di questa opzione impedisce 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 SCHEMABINDING viene specificato, gli oggetti di base non possono essere modificati in modo da influire sulla definizione della funzione. La definizione della funzione stessa deve prima essere modificata o eliminata per rimuovere le dipendenze dall'oggetto che deve essere modificato.

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

La funzione viene eliminata.
La funzione viene modificata utilizzando l'istruzione ALTER con l'opzione SCHEMABINDING non specificata.

Una funzione può essere associata allo schema solo se si verificano le condizioni seguenti:

La funzione è una funzione Transact-SQL.
Anche le funzioni definite dall'utente e le viste a cui fa riferimento la funzione sono associate a schema.
Agli oggetti a cui fa riferimento la funzione viene fatto riferimento utilizzando 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 dispone CREATE FUNCTION dell'autorizzazione REFERENCES per gli oggetti di database a cui fa riferimento la funzione.

RESTITUISCE NULL SU INPUT NULL | CHIAMATO SU INPUT NULL

Specifica l'attributo OnNULLCall di una funzione scalare. Se non specificato, CALLED ON NULL INPUT è implicito per impostazione predefinita. In altre parole, il corpo della funzione viene eseguito anche se NULL viene passato come argomento.

Se RETURNS NULL ON NULL INPUT viene specificata in una funzione CLR, indica che SQL Server può restituire NULL quando uno degli argomenti ricevuti è NULL, senza richiamare effettivamente il corpo della funzione. Se il metodo di una funzione CLR specificato in <method_specifier> dispone già di un attributo personalizzato che indica RETURNS NULL ON NULL INPUT, ma l'istruzione CREATE FUNCTION indica CALLED ON NULL INPUT, l'istruzione ha la CREATE FUNCTION precedenza. L'attributo OnNULLCall non può essere specificato per le funzioni CLR con valori di tabella.

ESEGUI COME

Specifica il contesto di sicurezza in cui viene eseguita la funzione definita dall'utente. Pertanto, è possibile controllare l'account utente utilizzato da SQL Server per convalidare le autorizzazioni per gli oggetti di database a cui fa riferimento la funzione.

EXECUTE AS Non può essere specificato per le funzioni inline con valori di tabella.

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

INLINE = { ACCESO | SPENTO }

Si applica a: SQL Server 2019 (15.x) e versioni successive e database SQL di Azure.

Specifica se la funzione definita dall'utente scalare deve essere inline o meno. Questa clausola si applica solo alle funzioni scalari definite dall'utente. La INLINE clausola non è obbligatoria. Se la INLINE clausola non viene specificata, viene impostata automaticamente su o ON in base al OFF fatto che la funzione definita dall'utente sia inlineable. Se INLINE = ON viene specificato, ma la funzione definita dall'utente risulta non inline, viene generato un errore. Per altre informazioni, vedere L'inlining di funzioni definite dall'utente scalari.

< > 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 nella tabella. I nomi delle colonne devono essere conformi alle regole per gli identificatori e devono essere univoci nella tabella. column_name può essere composto da 1 a 128 caratteri.

data_type

Specifica il tipo di dati della colonna. Per le funzioni Transact-SQL, sono consentiti tutti i tipi di dati, inclusi i tipi CLR definiti dall'utente, ad eccezione del timestamp. Per le funzioni CLR, sono consentiti tutti i tipi di dati, inclusi i tipi CLR definiti dall'utente, ad eccezione di text, ntext, image, char, varchar, varchar(max) e timestamp. Il cursore di tipo non scalare non può essere specificato come tipo di dati di colonna nelle funzioni Transact-SQL o CLR.

PREDEFINITO constant_expression

Specifica il valore assegnato alla colonna quando non viene specificato un valore in modo esplicito durante un inserimento. constant_expression è una costante, NULLo un valore di funzione di sistema. DEFAULT Le definizioni possono essere applicate a qualsiasi colonna, ad eccezione di quelle che hanno la IDENTITY proprietà. DEFAULT non può essere specificato per le funzioni CLR con valori di tabella.

FASCICOLAZIONE collation_name

Specifica le regole di confronto per la colonna. Se non viene specificata, alla colonna vengono assegnate le regole di confronto predefinite del database. È possibile usare nomi di regole di confronto di Windows o SQL. Per un elenco e ulteriori informazioni sulle regole di confronto, vedere Nome delle regole di confronto di Windows (Transact-SQL) e Nome delle regole di confronto di SQL Server (Transact-SQL).

La COLLATE clausola può essere utilizzata per modificare le regole di confronto solo delle colonne dei tipi di dati char, varchar, nchar e nvarchar . COLLATE non può essere specificato per le funzioni CLR con valori di tabella.

ROWGUIDCOL

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

La ROWGUIDCOL proprietà non applica l'univocità dei valori archiviati nella colonna. Inoltre, non genera automaticamente valori per le nuove righe inserite nella tabella. Per generare valori univoci per ogni colonna, utilizzare la NEWID funzione sulle INSERT istruzioni. È possibile specificare un valore predefinito; Tuttavia, NEWID non può essere specificato come predefinito.

IDENTITÀ

Indica che la nuova colonna è una colonna Identity. Quando viene aggiunta una nuova riga alla tabella, SQL Server fornisce un valore incrementale univoco per la colonna. Le colonne Identity vengono in genere utilizzate insieme PRIMARY KEY ai vincoli per fungere da identificatore di riga univoco per la tabella. La IDENTITY proprietà può essere assegnata alle colonne tinyint, smallint, int, bigint, decimal(p,0) o numeric(p,0). Ogni tabella può includere una sola colonna Identity. Le impostazioni predefinite e DEFAULT i vincoli associati non possono essere usati con una colonna Identity. È necessario specificare sia il valore di inizializzazione che l'incremento o nessuno dei due. In questo secondo caso, il valore predefinito è (1,1).

IDENTITY non può essere specificato per le funzioni CLR con valori di tabella.

seme

Il valore intero da assegnare alla prima riga della tabella.

Aumento

Valore intero da aggiungere al valore di inizializzazione per le righe successive della tabella.

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

Definisce il vincolo per una colonna o una 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 consentiti nella colonna. NULL non è strettamente un vincolo, ma può essere specificato proprio come NOT NULL. NOT NULL non può essere specificato per le funzioni CLR con valori di tabella.

CHIAVE PRIMARIA

Vincolo che applica l'integrità dell'entità per una colonna specificata tramite un indice univoco. Nelle funzioni definite dall'utente con valori di tabella, il PRIMARY KEY vincolo può essere creato su una sola colonna per tabella. PRIMARY KEY non può essere specificato per le funzioni CLR con valori di tabella.

UNICO

Vincolo che fornisce l'integrità dell'entità per una colonna o una colonna specificata tramite un indice univoco. Una tabella può avere più UNIQUE vincoli. UNIQUE non può essere specificato per le funzioni CLR con valori di tabella.

CLUSTERED | NON CLUSTER

Indica che è stato creato un indice cluster o non cluster per il PRIMARY KEY vincolo or UNIQUE . PRIMARY KEY i vincoli usano CLUSTERED, e UNIQUE i vincoli usano NONCLUSTERED.

CLUSTERED può essere specificato per un solo vincolo. Se CLUSTERED viene specificato per un UNIQUE vincolo e viene specificato anche un PRIMARY KEY vincolo, viene PRIMARY KEY utilizzato NONCLUSTERED.

CLUSTERED e NONCLUSTERED non può essere specificato per le funzioni CLR con valori di tabella.

VERIFICA

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

logical_expression

Un'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 dell'indice per l'indice PRIMARY KEY or UNIQUE . Per ulteriori informazioni sulle opzioni relative agli indici, vedere CREATE INDEX (Transact-SQL).

PAD_INDEX = { ON | OFF }

Specifica il riempimento dell'indice. Il valore predefinito è OFF.

FILLFACTOR = fillfactor

Specifica una percentuale che indica il livello foglia di ogni pagina di indice durante la creazione o la modifica dell'indice da parte di Motore di database. 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 le statistiche di distribuzione vengono ricalcolate. Il valore predefinito è OFF.

ALLOW_ROW_LOCKS = { ON | OFF }

Specifica se sono consentiti blocchi di riga. Il valore predefinito è ON.

ALLOW_PAGE_LOCKS = { ATTIVO | DISATTIVO }

Specifica se sono consentiti blocchi di pagina. Il valore predefinito è ON.

Procedure consigliate

Se una funzione definita dall'utente non viene creata con la clausola, le SCHEMABINDING 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 WITH SCHEMABINDING clausola durante la creazione della funzione. Questa opzione garantisce che gli oggetti a cui si fa riferimento nella definizione della funzione non possano essere modificati, a meno che non venga modificata anche la funzione.
Eseguire la stored procedure sp_refreshsqlmodule dopo aver modificato qualsiasi oggetto specificato nella definizione della funzione.

Per ulteriori informazioni e considerazioni sulle prestazioni relative alle funzioni inline con valori di tabella (con valori di tabella inline) e alle funzioni con valori di tabella multi-istruzione, vedere Creare funzioni definite dall'utente (Motore di database).

Tipi di dati

Se i parametri vengono specificati in una funzione CLR, devono essere tipi di SQL Server come definito in precedenza per scalar_parameter_data_type. Per ulteriori informazioni sul confronto tra i tipi di dati di sistema di SQL Server e i tipi di dati di integrazione CLR o i tipi di dati Common Language Runtime di .NET Framework, vedere Mapping dei dati dei parametri CLR.

Affinché SQL Server faccia riferimento al metodo corretto quando è sottoposto a overload in una classe, il metodo indicato in <method_specifier> deve avere le caratteristiche seguenti:

Ricevere lo stesso numero di parametri specificato in [ , ...n ].
Ricevi tutti i parametri per valore, non per riferimento.
Utilizzare tipi di parametro compatibili con i tipi specificati nella funzione SQL Server.

Se il tipo di dati restituito della funzione CLR specifica un tipo di tabella (RETURNS TABLE), il tipo di dati restituito del metodo in <method_specifier> deve essere di tipo IEnumerator o IEnumerable, e presuppone che l'interfaccia sia implementata dal creatore della funzione. A differenza di Transact-SQL funzioni, le funzioni CLR non possono includere PRIMARY KEY, , o UNIQUE vincoli in CHECK<table_type_definition>. I tipi di dati delle colonne specificati in <table_type_definition> devono corrispondere ai tipi delle colonne corrispondenti del set di risultati restituito dal metodo in <method_specifier> fase di esecuzione. Questo controllo del tipo non viene eseguito al momento della creazione della funzione.

Per ulteriori informazioni sulla programmazione delle funzioni CLR, vedere Funzioni User-Defined CLR.

Osservazioni:

Le funzioni scalari possono essere richiamate quando vengono utilizzate espressioni scalari, incluse le colonne calcolate e CHECK le definizioni dei vincoli. Le funzioni scalari possono essere eseguite anche utilizzando l'istruzione EXECUTE (Transact-SQL). È necessario richiamare funzioni scalari usando almeno il nome in due parti della funzione (<schema>.<function>). Per ulteriori informazioni sui nomi multiparte, vedere Transact-SQL Convenzioni di sintassi (Transact-SQL). Le funzioni con valori di tabella possono essere richiamate quando le FROM espressioni di tabella sono consentite nella clausola di SELECT, INSERT, UPDATE, o DELETE nelle istruzioni. Per altre informazioni, vedere Eseguire funzioni definite dall'utente.

Interoperabilità

Le istruzioni seguenti sono valide in una funzione:

Dichiarazioni di assegnazione.
Istruzioni di controllo del flusso, ad eccezione TRY...CATCH delle istruzioni.
DECLARE Istruzioni che definiscono variabili di dati locali e cursori locali.
SELECT Istruzioni che contengono elenchi di selezione con espressioni che assegnano valori a variabili locali.
Operazioni del cursore che fanno riferimento a cursori locali dichiarati, aperti, chiusi e deallocati nella funzione. Sono consentite solo FETCH le istruzioni che assegnano valori alle variabili locali utilizzando la INTO clausola, mentre FETCH le istruzioni che restituiscono dati al client non sono consentite.
INSERT, UPDATE, e le istruzioni che DELETE modificano le variabili della tabella locale.
EXECUTE Istruzioni che chiamano stored procedure estese.

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

Interoperabilità delle colonne calcolate

Le funzioni hanno le proprietà seguenti. I valori di queste proprietà determinano se le funzioni possono essere utilizzate in colonne calcolate che possono essere salvate in modo permanente 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 risultato ogni volta che vengono chiamate utilizzando un set specifico di valori di input e con lo stesso stato del database verrebbero etichettate come deterministiche.
`IsPrecise`	La funzione è precisa o imprecisa.	Le funzioni imprecise contengono operazioni come le operazioni a virgola mobile.
`IsSystemVerified`	Le proprietà di precisione e determinismo della funzione possono essere verificate da 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à di precisione e determinismo delle funzioni Transact-SQL vengono determinate automaticamente da SQL Server. Le proprietà di accesso ai dati e determinismo delle funzioni CLR possono essere specificate dall'utente. Per ulteriori informazioni, vedere Integrazione con CLR: attributi personalizzati per le routine CLR.

Per visualizzare i valori correnti di queste proprietà, utilizzare OBJECTPROPERTYEX (Transact-SQL).

Importante

Le funzioni devono essere create con SCHEMABINDING per essere deterministiche.

Una colonna calcolata che richiama una funzione definita dall'utente può essere utilizzata in un indice quando la funzione definita dall'utente ha i valori di proprietà seguenti:

IsDeterministic è true
IsSystemVerified is true (a meno che la colonna calcolata non sia persistente)
UserDataAccess è false
SystemDataAccess è false

Per altre informazioni, vedere Indici per le colonne calcolate.

Chiamare stored procedure estese dalle funzioni

La stored procedure estesa, quando la chiama dall'interno di una funzione, non può restituire set di risultati al client. Tutte le API ODS che restituiscono set di risultati al client, restituiscono FAIL. La stored procedure estesa potrebbe riconnettersi a un'istanza di SQL Server. Tuttavia, non deve tentare di unire la stessa transazione della funzione che ha richiamato la stored procedure estesa.

Analogamente alle chiamate da un batch o da una stored procedure, la stored procedure estesa viene eseguita nel contesto dell'account di sicurezza di Windows con cui è in esecuzione SQL Server. Il proprietario della stored procedure deve prendere in considerazione questo scenario quando concede EXECUTE l'autorizzazione agli utenti.

Limitazioni

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.

Le istruzioni di Service Broker seguenti non possono essere incluse nella definizione di una Transact-SQL funzione definita dall'utente:

BEGIN DIALOG CONVERSATION
END CONVERSATION
GET CONVERSATION GROUP
MOVE CONVERSATION
RECEIVE
SEND

Le funzioni definite dall'utente possono essere annidate; ovvero una funzione definita dall'utente può chiamare un'altra. Il livello di annidamento viene incrementato all'avvio dell'esecuzione della funzione chiamata e decrementato al termine dell'esecuzione della funzione chiamata. Le funzioni definite dall'utente possono essere annidate fino a 32 livelli. Il superamento dei livelli massimi di annidamento causa l'esito negativo dell'intera catena di funzioni chiamanti. Qualsiasi riferimento al codice gestito da una funzione definita dall'utente Transact-SQL viene conteggiato come un livello rispetto al limite di annidamento di 32 livelli. I metodi richiamati dal codice gestito non vengono inclusi nel conteggio per questo limite.

Usare l'ordinamento nelle funzioni CLR con valori di tabella

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

È necessario assicurarsi che i risultati siano sempre ordinati nell'ordine specificato. Se i risultati non sono nell'ordine specificato, SQL Server genera un messaggio di errore quando viene eseguita la query.
Se viene specificata una ORDER clausola, l'output della funzione con valori di tabella deve essere ordinato in base alle regole di confronto della colonna (esplicite o implicite). Ad esempio, se le regole di confronto della colonna sono cinesi, i risultati restituiti devono essere ordinati in base alle regole di ordinamento cinesi. Le regole di confronto vengono specificate nel DDL per la funzione con valori di tabella o ottenute dalle regole di confronto del database.
SQL Server verifica sempre la ORDER clausola, se specificata, restituendo i risultati, indipendentemente dal fatto che Query Processor la utilizzi o meno per eseguire ulteriori ottimizzazioni. Utilizzare la ORDER clausola solo se si sa che è utile per l'elaboratore di query.
Query Processor di SQL Server sfrutta automaticamente la ORDER clausola nei casi seguenti:
- Inserire query in cui la ORDER clausola è compatibile con un indice.
- ORDER BY clausole compatibili con la ORDER clausola.
- Aggregati, dove GROUP BY è compatibile con ORDER la clausola.
- DISTINCT Aggregazioni in cui le colonne distinte sono compatibili con la ORDER clausola.

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

Metadati

Nella tabella seguente sono elencate le viste del catalogo di sistema che è possibile utilizzare per restituire i metadati relativi alle funzioni definite dall'utente.

Vista del sistema	Descrizione
sys.sql_moduli	Vedere l'esempio E nella sezione Esempi.
sys.assembly_modules	Visualizza informazioni sulle funzioni CLR definite dall'utente.
sys.parameters	Visualizza informazioni sui parametri definiti nelle funzioni definite dall'utente.
sys.sql_dipendenze_espressione	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

Per altri esempi e considerazioni sulle prestazioni delle funzioni definite dall'utente, vedere Creare funzioni definite dall'utente (Motore di database).

Un. Utilizzare una funzione definita dall'utente con valori scalari che calcola la settimana ISO

Nell'esempio seguente viene creata la funzione ISOweekdefinita dall'utente. Questa funzione accetta un argomento date e calcola il numero della settimana ISO. Affinché questa funzione venga calcolata correttamente, SET DATEFIRST 1 deve essere richiamata prima che la funzione venga chiamata.

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

Ecco la chiamata alla funzione. DATEFIRST è impostato su 1.

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';

Il set di risultati è il seguente.

ISO Week
----------------
52

B. Creare una funzione inline con valori di tabella

Nell'esempio seguente viene restituita una funzione inline con valori di tabella nel database AdventureWorks2022. Restituisce tre colonne ProductID, Name, e l'aggregato dei totali da inizio anno per punto vendita YTD Total come per ogni prodotto venduto al punto vendita.

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
    INNER JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID
    INNER JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID
    INNER 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 questa query.

SELECT * FROM Sales.ufn_SalesByStore (602);

C. Creare una funzione con valori di tabella con più istruzioni

Nell'esempio seguente viene creata la funzione ufn_FindReports(InEmpID) con valori di AdventureWorks2022 tabella nel database. Se viene fornito un ID dipendente valido, la funzione restituisce una tabella che corrisponde a tutti i dipendenti che fanno riferimento al dipendente direttamente o indirettamente. La funzione utilizza un'espressione di tabella comune ricorsiva (CTE) per produrre l'elenco gerarchico dei dipendenti. Per ulteriori informazioni sulle CTE ricorsive, vedere WITH common_table_expression (Transact-SQL).

CREATE FUNCTION dbo.ufn_FindReports (@InEmpID INT)
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 (
        -- Get the initial list of Employees for Manager n
        SELECT e.BusinessEntityID,
            OrganizationNode = ISNULL(e.OrganizationNode, CAST('/' AS HIERARCHYID)),
            p.FirstName,
            p.LastName,
            e.JobTitle,
            0
        FROM HumanResources.Employee e
        INNER JOIN Person.Person p
            ON p.BusinessEntityID = e.BusinessEntityID
        WHERE e.BusinessEntityID = @InEmpID
        
        UNION ALL
        
        -- Join recursive member to anchor
        SELECT e.BusinessEntityID,
            e.OrganizationNode,
            p.FirstName,
            p.LastName,
            e.JobTitle,
            RecursionLevel + 1
        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. Creare una funzione CLR

Nell'esempio viene creata la funzione len_sCLR. Prima della creazione della funzione, l'assembly SurrogateStringFunction.dll viene registrato nel database locale.

Si applica a: SQL Server 2008 (10.0.x) SP 1 e versioni successive.

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\MSSQL13.MSSQLSERVER\MSSQL\DATA\master.mdf',
    'Microsoft SQL Server\130\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 di come creare una funzione CLR con valori di tabella, vedere Funzioni Table-Valued CLR.

E. Visualizzazione della definizione delle funzioni definite dall'utente

SELECT DEFINITION,
    type
FROM sys.sql_modules AS m
INNER JOIN sys.objects AS o
    ON m.object_id = o.object_id
    AND type IN ('FN', 'IF', 'TF');
GO

La definizione delle funzioni create utilizzando l'opzione ENCRYPTION non può essere visualizzata utilizzando sys.sql_modules; tuttavia, vengono visualizzate altre informazioni sulle funzioni crittografate.

Condividi tramite

CREA FUNZIONE (Transact-SQL)

Sintassi

Argomenti

OR ALTER

schema_name

function_name

@parameter_name

[ type_schema_name. ] parameter_data_type

[ = predefinito ]

SOLA LETTURA

return_data_type

function_body

scalar_expression

TABELLA

select_stmt

ORDINE (<order_clause>)

NOME ESTERNO <method_specifier>assembly_name.class_name. method_name

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

< > clr_table_type_definition ( { column_namedata_type } [ , ... n ] )

NULL | NOT NULL

Compilazione Nativa

INIZIA ATOMIC CON

SCHEMABINDING

ESEGUI COME

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

CRIPTAZIONE

SCHEMABINDING

RESTITUISCE NULL SU INPUT NULL | CHIAMATO SU INPUT NULL

ESEGUI COME

INLINE = { ACCESO | SPENTO }

< > column_definition ::=

column_name

data_type

PREDEFINITO constant_expression

FASCICOLAZIONE collation_name

ROWGUIDCOL

IDENTITÀ

seme

Aumento

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

NULL | NOT NULL

CHIAVE PRIMARIA

UNICO

CLUSTERED | NON CLUSTER

VERIFICA

logical_expression

< > computed_column_definition ::=

column_name

computed_column_expression

< > 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 = { ATTIVO | DISATTIVO }

Procedure consigliate

Tipi di dati

Osservazioni:

Interoperabilità

Interoperabilità delle colonne calcolate

Chiamare stored procedure estese dalle funzioni

Limitazioni

Usare l'ordinamento nelle funzioni CLR con valori di tabella

Metadati

Autorizzazioni

Esempi

Un. Utilizzare una funzione definita dall'utente con valori scalari che calcola la settimana ISO

B. Creare una funzione inline con valori di tabella

C. Creare una funzione con valori di tabella con più istruzioni

D. Creare una funzione CLR

E. Visualizzazione della definizione delle funzioni definite dall'utente

Contenuti correlati

Commenti e suggerimenti

Risorse aggiuntive