CREATE FUNCTION (Transact-SQL)

Articolo
04/27/2024

Si applica a: SQL Server Database SQL di Azure Istanza gestita di SQL di Azure

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 come un calcolo complesso e restituisce il risultato di tale azione come 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 CHECK vincolo in una colonna
Per sostituire una stored procedure.
Usare una funzione inline come predicato di filtro per un criterio di sicurezza

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

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

Convenzioni relative alla sintassi Transact-SQL

Sintassi

Sintassi per le funzioni scalari Transact-SQL.

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 le funzioni inline con valori di tabella Transact-SQL.

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 le funzioni con valori di tabella con istruzioni Transact-SQL.

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 le clausole di funzione Transact-SQL.

<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 le 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) SP 1 e versioni successive e database SQL di Azure.

Modifica la funzione in modo condizionale 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 di funzione devono essere conformi alle regole per gli identificatori e devono essere univoci all'interno del database e rispetto al relativo schema.

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

@parameter_name

Parametro nella 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 assumere il posto solo delle costanti; non possono essere usati anziché nomi di tabella, nomi di colonna o nomi di altri oggetti di database.

ANSI_WARNINGS non viene rispettato quando si passano parametri in una stored procedure, una funzione definita dall'utente o quando si dichiarano e si impostano le 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

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, tutti i tipi di dati, inclusi i tipi CLR definiti dall'utente, sono consentiti ad eccezione di text, ntext, image, tipi di tabella definiti dall'utente e tipi di dati timestamp. I tipi non scalabili, il cursore e la tabella non possono essere specificati come tipo di dati dei parametri nelle funzioni Transact-SQL o CLR.

Se type_schema_name non viene specificato, il motore di database cerca il scalar_parameter_data_type nell'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 viene definito un valore default, è possibile eseguire la funzione senza specificare un valore per il parametro corrispondente a tale valore.

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, è necessario specificare la parola chiave DEFAULT quando viene chiamata la funzione per recuperare il valore predefinito. Questo comportamento risulta diverso dall'utilizzo di parametri con valore predefinito nelle stored procedure in cui l'omissione del parametro implica l'utilizzo del valore predefinito. Tuttavia, la DEFAULT parola chiave non è necessaria quando si richiama una funzione scalare usando l'istruzione EXECUTE .

READONLY

Indica che il parametro non può essere aggiornato o modificato all'interno della definizione della funzione. READONLY è obbligatorio per i parametri di tipo di tabella definiti dall'utente e non può essere usato per qualsiasi altro tipo 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, 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. I tipi non scalabili, il cursore e la tabella 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 usato solo in funzioni scalari e funzioni con valori di tabella con istruzioni multiple.

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

Nelle funzioni ADF function_body è una serie di istruzioni Transact-SQL che popolano una TABLE variabile 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 @local_variables.

Nelle funzioni CONF inline, il TABLE valore restituito viene definito tramite una singola SELECT istruzione. Le funzioni inline non hanno variabili restituite associate.

Nei file CONF @return_variable è una TABLE variabile, usata per archiviare e accumulare le righe che devono essere restituite come valore della funzione. @È possibile specificare return_variable solo per le funzioni Transact-SQL e non per le funzioni CLR.

select_stmt

Istruzione singola SELECT che definisce il valore restituito di una funzione con valori di tabella inline (TVF).

ORDER (<order_clause>)

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

EXTERNAL NAME <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 dovrà fare riferimento il nome della funzione creata.

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

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

Spesso il valore contiene un punto incorporato. In questi casi, la sintassi Transact-SQL richiede che il valore sia associato a una coppia di parentesi quadre ([]) o con una coppia di virgolette doppie ("").
method_name deve corrispondere a un valore nella colonna method_name 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 spazio dei MyFood nomi , il EXTERNAL NAME valore potrebbe essere MyFood.[MyFood.MyClass].MyStaticMethod.

Per impostazione predefinita, SQL Server non può eseguire il 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 finché non si abilita l'opzione clr enabled. Per abilitare questa opzione, usare 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 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 ] )

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 di tabella include solo nomi di colonna e tipi di dati. La tabella viene sempre inserita nel filegroup primario.

NULL | NOT NULL

Supportato solo per funzioni definite dall'utente scalari compilate in modo nativo. Per altre informazioni, vedere Funzioni scalari definite dall'utente per OLTP in memoria.

NATIVE_COMPILATION

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

BEGIN ATOMIC WITH

Obbligatorio e supportato solo per le funzioni scalari scalari compilate in modo nativo. Per altre informazioni, vedere Blocchi atomici nelle procedure native.

SCHEMABINDING

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

EXECUTE AS

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.

ENCRYPTION

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

Indica che il motore di database converte il testo originale dell'istruzione CREATE FUNCTION in un formato offuscato. L'output dell'offuscamento non è direttamente visibile in alcuna 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. 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 altre 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 SCHEMABINDING viene specificato, gli oggetti di base non possono essere modificati in modo da influire 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:

La funzione è 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 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 specificato 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 specificata in <method_specifier> ha già un attributo personalizzato che indica RETURNS NULL ON NULL INPUT, ma l'istruzione indica CALLED ON NULL INPUT, l'istruzione CREATE FUNCTION ha la CREATE FUNCTION precedenza. Non è possibile specificare l'attributo OnNULLCall per le funzioni CLR con valori di tabella.

EXECUTE AS

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

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

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

INLINE = { ON | OFF }

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

Specifica se questa funzione scalare definita dall'utente deve essere impostata come inline. Questa clausola è applicabile solo alle funzioni scalari definite dall'utente. La clausola INLINE non è obbligatoria. Se la INLINE clausola non viene specificata, viene impostata automaticamente su o OFF in base al ON fatto che la funzione definita dall'utente sia inline. Se INLINE = ON viene specificato ma la funzione definita dall'utente non è inline, viene generato un errore. Per altre informazioni, vedere 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 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, tutti i tipi di dati, inclusi i tipi CLR definiti dall'utente, sono consentiti ad eccezione di text, ntext, image, char, 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.

DEFAULT constant_expression

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

COLLATE collation_name

Specifica le regole di confronto per la colonna. Se viene omesso, 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 di regole di confronto e altre informazioni su queste, 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 usata 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 è un identificatore univoco di riga globale. Una sola colonna uniqueidentifier per tabella può essere designata come ROWGUIDCOL colonna. 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, usare la NEWID funzione nelle INSERT istruzioni . È possibile specificare un valore predefinito; Non è tuttavia NEWID possibile specificare come valore predefinito.

IDENTITY

Indica che la nuova colonna è una colonna Identity. Quando si aggiunge una nuova riga alla tabella, SQL Server assegna un valore univoco e incrementale alla colonna. Le colonne Identity vengono in genere usate insieme PRIMARY KEY ai vincoli per fungere da identificatore di riga univoco per la tabella. La proprietà IDENTITY può essere assegnata a colonne tinyint, smallint, int, bigint, decimal(p,0) o numeric(p,0). Ogni tabella può includere una sola colonna Identity. Le impostazioni predefinite associate e DEFAULT i vincoli non possono essere usati con una colonna Identity. È necessario specificare sia il valore di seed che di increment oppure è possibile omettere entrambi questi valori. In questo secondo caso, il valore predefinito è (1,1).

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

seed

Valore intero da assegnare alla prima riga della tabella.

increment

Valore intero da aggiungere al valore di inizializzazione per le righe successive nella 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. NULL non è strettamente un vincolo, ma può essere specificato esattamente come NOT NULL. NOT NULL non può essere specificato per le funzioni CLR con valori di tabella.

PRIMARY KEY

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 in una sola colonna per tabella. PRIMARY KEY non può essere specificato per le funzioni CLR con valori di tabella.

UNIQUE

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

Indicare che viene creato un indice cluster o non cluster per il PRIMARY KEY vincolo o UNIQUE . PRIMARY KEYI vincoli usano i vincoli e UNIQUE usano CLUSTEREDNONCLUSTERED.

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

CLUSTERED e NONCLUSTERED non possono essere specificati 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. CHECK Non è possibile specificare vincoli 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 altre 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 di indice per l'indice PRIMARY KEY o UNIQUE . Per altre 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 che indica il livello di riempimento del livello foglia di ogni pagina di indice da parte del 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 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 = { 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 con la SCHEMABINDING clausola , 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 WITH SCHEMABINDING clausola durante la creazione della funzione. Questa opzione garantisce che gli oggetti a cui viene fatto riferimento nella definizione della funzione non possano essere modificati, a meno che la funzione non venga modificata.
Eseguire la stored procedure sp_refreshsqlmodule dopo avere modificato qualsiasi oggetto specificato nella definizione della funzione.

Per altre informazioni e considerazioni sulle prestazioni sulle funzioni inline con valori di tabella (TVFS inline) e sulle funzioni con valori di tabella a istruzioni multiple (MSTVFs), vedere Creare funzioni definite dall'utente (motore di database).

Tipo 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 altre 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 ].
Riceva tutti i parametri in base al valore e non in base al riferimento.
Usare i tipi di parametro compatibili con i tipi specificati nella funzione di 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 IEnumerablee presuppone che l'interfaccia venga implementata dall'autore della funzione. A differenza delle funzioni Transact-SQL, le funzioni CLR non possono includere PRIMARY KEYvincoli , UNIQUEo 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. Questo controllo dei tipi non viene eseguito al momento della creazione della funzione.

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

Osservazioni:

Le funzioni scalari possono essere richiamate in cui vengono usate espressioni scalari, che includono colonne calcolate e CHECK definizioni di vincoli. Le funzioni scalari possono essere eseguite anche usando l'istruzione EXECUTE (Transact-SQL). È necessario richiamare funzioni scalari usando almeno il nome in due parti della funzione (<schema>.<function>). Per altre informazioni sui nomi a più parti, vedere Convenzioni della sintassi Transact-SQL (Transact-SQL). Le funzioni con valori di tabella possono essere richiamate laddove sono consentite le espressioni di tabella nella clausola FROM delle istruzioni SELECT, INSERT, UPDATE, o DELETE. Per altre 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, ad eccezione delle 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 FETCH istruzioni che assegnano valori alle variabili locali usando la INTO clausola . FETCH Le istruzioni che restituiscono dati al client non sono consentite.
Istruzioni INSERT, UPDATE e DELETE che modificano le variabili di tabella locali.
Istruzioni EXECUTE che chiamano stored procedure estese.

Per altre informazioni, vedere Creare funzioni definite dall'utente (motore di database).

Interoperabilità delle colonne calcolate

Le funzioni presentano le proprietà seguenti. 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 risultato ogni volta che vengono chiamate usando un set specifico di valori di input e con lo stesso stato del database vengono etichettate come 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 altre informazioni, vedere Integrazione con CLR: attributi personalizzati per routine CLR.

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

Importante

Le funzioni devono essere create con SCHEMABINDING per essere deterministiche.

È 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 altre informazioni, vedere Indici per le colonne calcolate.

Chiamata di stored procedure estese da funzioni

La stored procedure estesa, quando viene chiamata 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 in cui è in esecuzione SQL Server. Il proprietario della stored procedure deve considerare questo scenario quando si concede EXECUTE l'autorizzazione agli utenti.

Limiti

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 funzione definita dall'utente Transact-SQL:

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 definita dall'utente Transact-SQL viene considerato come un livello nel contesto del limite di 32 livelli di nidificazione. 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 usa 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, SQL Server genera 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). 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 risultati, indipendentemente dal fatto che Query Processor lo usi per eseguire ulteriori ottimizzazioni. Usare la ORDER clausola solo se si sa che è utile per Query Processor.
Query Processor di SQL Server usa 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 ORDER clausola non garantisce risultati ordinati quando viene eseguita una SELECT query, a meno che ORDER BY non venga specificata anche nella query. 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 UFX

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	Vedere l'esempio E nella sezione Esempi.
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

Per altri esempi e considerazioni sulle prestazioni sulle funzioni definite dall'utente, vedere Creare funzioni definite dall'utente (motore di database).For more examples and performance considerations about UDFs, see Create user-defined functions (motore di database).

R. Usare una funzione scalare-valued definita dall'utente che calcola la 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.

L'esempio mostra anche l'uso 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 routine viene eseguita nel contesto dell'utente che lo chiama. Le altre opzioni che è possibile specificare sono SELF, OWNER e user_name.

Ecco la chiamata di 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. L'esempio restituisce tre colonne ProductID, Name e l'aggregazione dei totali da inizio anno per negozio, come YTD Total per ogni prodotto venduto al negozio.

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 la query seguente.

SELECT * FROM Sales.ufn_SalesByStore (602);

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

Nell'esempio seguente viene creata la funzione fn_FindReports(InEmpID) con valori di tabella nel AdventureWorks2022 database. 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 altre informazioni sulle espressioni 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

L'esempio crea la funzione CLR len_s, ma prima che venga effettivamente creata la 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 relativo alla creazione di una funzione CLR con valori di tabella, vedere Funzioni CLR con valori di tabella.

E. Visualizzare la 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

Non è possibile visualizzare la definizione delle funzioni create usando l'opzione ENCRYPTION . sys.sql_modulesTuttavia, vengono visualizzate altre informazioni sulle funzioni crittografate.

Condividi tramite

CREATE FUNCTION (Transact-SQL)

Sintassi

Argomenti

OR ALTER

schema_name

function_name

@parameter_name

[ type_schema_name. ] parameter_data_type

[ = default ]

READONLY

return_data_type

function_body

scalar_expression

TABLE

select_stmt

ORDER (<order_clause>)

EXTERNAL NAME <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

NATIVE_COMPILATION

BEGIN ATOMIC WITH

SCHEMABINDING

EXECUTE AS

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

ENCRYPTION

SCHEMABINDING

RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT

EXECUTE AS

INLINE = { ON | OFF }

<> column_definition ::=

column_name

data_type

DEFAULT constant_expression

COLLATE collation_name

ROWGUIDCOL

IDENTITY

seed

increment

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

NULL | NOT NULL

PRIMARY KEY

UNIQUE

CLUSTERED | NONCLUSTERED

CHECK

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 = { ON | OFF }

Procedure consigliate

Tipo di dati

Osservazioni:

Interoperabilità

Interoperabilità delle colonne calcolate

Chiamata di stored procedure estese da funzioni

Limiti

Usare l'ordinamento nelle funzioni CLR con valori di tabella

Metadati UFX

Autorizzazioni

Esempi

R. Usare una funzione scalare-valued definita dall'utente 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. Visualizzare la definizione delle funzioni definite dall'utente

Contenuto correlato

Commenti e suggerimenti

Risorse aggiuntive