Funzioni definite dall'utente

Articolo
08/23/2023

Si applica a:SQL Server Database SQL di Azure Istanza gestita di SQL di Azure Endpoint di analisi SQL in Microsoft Fabric Warehouse in Microsoft Fabric

In modo analogo alle funzioni dei linguaggi di programmazione, le funzioni definite dall'utente di SQL Server sono routine che accettano parametri, eseguono un'azione (ad esempio un calcolo complesso) e restituiscono il risultato dell'azione sotto forma di valore. Il valore restituito può essere un valore scalare singolo o un set di risultati.

Vantaggi delle funzioni definite dall'utente

Programmazione modulare. È possibile creare la funzione una sola volta, archiviarla nel database e richiamarla nel programma tutte le volte che si desidera. Le funzioni definite dall'utente possono essere modificate in maniera indipendente dal codice sorgente del programma.
Esecuzione più rapida In maniera simile alle stored procedure, le funzioni definite dall'utente in Transact-SQL riducono le risorse necessarie per la compilazione di codice memorizzando nella cache e riutilizzando i piani per esecuzioni ripetute. Ciò significa che una funzione definita dall'utente non deve essere analizzata e ottimizzata nuovamente ogni volta che viene utilizzata, con una conseguente e sensibile riduzione dei tempi di esecuzione.

Le funzioni CLR offrono un significativo vantaggio nelle prestazioni rispetto alle funzioni Transact-SQL per le attività di calcolo, modifica delle stringhe e logiche di business. Le funzioni Transact-SQL sono più adatte per logica intensiva di accesso ai dati.
Ridurre il traffico di rete. È possibile esprimere in una funzione un'operazione che filtra i dati sulla base di un vincolo complesso che non è possibile esprimere in una singola espressione scalare. È possibile richiamare la funzione nella clausola WHERE per ridurre il numero di righe inviate al client.

Importante

Le funzioni definite dall'utente di Transact-SQL nelle query possono essere eseguite solo su un solo thread (piano di esecuzione seriale). Non è quindi possibile un'elaborazione parallela delle query se si usano le funzioni definite dall'utente. Per altre informazioni sull'elaborazione parallela delle query, vedere Guida sull'architettura di elaborazione delle query.

Tipi di funzione

Funzioni scalari

Le funzioni scalari definite dall'utente restituiscono un singolo valore di dati del tipo definito nella clausola RETURNS. Per una funzione scalare inline, il valore scalare restituito corrisponde al risultato di una singola istruzione. Per una funzione scalare con istruzioni multiple, il corpo della funzione può contenere una serie di istruzioni Transact-SQL che restituiscono un solo valore. Il tipo restituito può essere qualsiasi tipo di dati ad eccezione di text, ntext, image, cursore timestamp. Per gli esempi vedere Creare funzioni definite dall'utente (motore di database).

Funzioni con valori di tabella

Le funzioni con valori di tabella definite dall'utente restituiscono un tipo di dati table. Per una funzione inline con valori di tabella non è disponibile alcun corpo della funzione. La tabella corrisponde al set di risultati di una singola istruzione SELECT. Per gli esempi vedere Creare funzioni definite dall'utente (motore di database).

Funzioni di sistema

SQL Server offre numerose funzioni di sistema che consentono di eseguire diverse operazioni. Non possono essere modificati. Per altre informazioni, vedere Funzioni predefinite (Transact-SQL), Funzioni archiviate di sistema (Transact-SQL) e Funzioni e viste a gestione dinamica (Transact-SQL).

Linee guida

Gli errori Transact-SQL che causano l'annullamento di un'istruzione e l'esecuzione dell'istruzione successiva nel modulo (ad esempio trigger o stored procedure) vengono trattati in modo diverso all'interno di una funzione. Nelle funzioni tali errori arrestano l'esecuzione della funzione, che a sua volta comporta l'interruzione dell'istruzione che ha richiamato la funzione.

Le istruzioni in un blocco BEGIN...END non possono avere effetti collaterali. Gli effetti collaterali di una funzione sono le modifiche permanenti allo stato di una risorsa il cui ambito è al di fuori della funzione, ad esempio la modifica di una tabella di database. Le uniche modifiche che possono essere apportate dalle istruzioni nella funzione sono le modifiche agli oggetti locali rispetto alla funzione, ad esempio variabili o cursori locali. Modifiche a tabelle di database, operazioni su cursori che non sono locali rispetto alla funzione, invio di messaggi di posta elettronica, tentativi di modifica del catalogo e generazione di un set di risultati da restituire all'utente sono esempi di azioni che non possono essere eseguite in una funzione.

Se un'istruzione CREATE FUNCTION produce effetti collaterali su risorse che non esistono quando viene eseguita l'istruzione CREATE FUNCTION, SQL Server esegue l'istruzione. Tuttavia, SQL Server non esegue la funzione quando viene richiamata.

Il numero di volte in cui viene effettivamente eseguita una funzione specificata in una query varia in base ai piani di esecuzione compilati da Query Optimizer. Si consideri ad esempio una funzione richiamata da una sottoquery in una clausola WHERE. Il numero di volte in cui la sottoquery e la relativa funzione vengono eseguite varia in base ai diversi percorsi di accesso scelti da Query Optimizer.

Le funzioni deterministiche devono essere associate allo schema. Usare la clausola SCHEMABINDING durante la creazione di una funzione deterministica.

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

Istruzioni valide in una funzione

I tipi di istruzioni valide in una funzione sono i seguenti:

Istruzioni DECLARE per definire variabili di dati e cursori locali rispetto alla funzione.
Assegnazioni di valori a oggetti locali rispetto alla funzione, ad esempio usando SET per assegnare valori a variabili locali scalari e di tabella.
Operazioni su cursori che fanno riferimento a cursori locali dichiarati, aperti, chiusi e deallocati nella funzione. Non sono consentite istruzioni FETCH che restituiscono dati al client. Sono consentite solo istruzioni FETCH che assegnano valori a variabili locali tramite la clausola INTO.
Istruzioni per il controllo di flusso, ad eccezione delle istruzioni TRY...CATCH.
Istruzioni SELECT che includono elenchi di selezione con espressioni che assegnano valori a variabili locali rispetto alla funzione.
Istruzioni UPDATE, INSERT e DELETE che modificano le variabili di tabella locali rispetto alla funzione.
Istruzioni EXECUTE che chiamano stored procedure estese.

Funzioni di sistema predefinite

Le funzioni predefinite non deterministiche seguenti possono essere utilizzate nelle funzioni Transact-SQL definite dall'utente.

CURRENT_TIMESTAMP
GET_TRANSMISSION_STATUS
GETDATE
GETUTCDATE
@@CONNECTIONS
@@CPU_BUSY
@@DBTS
@@IDLE
@@IO_BUSY
@@MAX_CONNECTIONS
@@PACK_RECEIVED
@@PACK_SENT
@@PACKET_ERRORS
@@TIMETICKS
@@TOTAL_ERRORS
@@TOTAL_READ
@@TOTAL_WRITE

Le funzioni predefinite non deterministiche seguenti non possono essere usate in funzioni Transact-SQL definite dall'utente.

NEWID
NEWSEQUENTIALID
RAND
TEXTPTR

Per un elenco delle funzioni di sistema predefinite deterministiche e non deterministiche, vedere Funzioni deterministiche e non deterministiche.

Funzioni associate a schema

L'istruzione CREATE FUNCTION supporta la clausola SCHEMABINDING che associa la funzione allo schema degli oggetti a cui fa riferimento, ad esempio tabelle, viste e altre funzioni definite dall'utente. I tentativi di modifica o eliminazione degli oggetti a cui fa riferimento una funzione associata a schema hanno esito negativo.

Per poter specificare SCHEMABINDING in un'istruzione CREATE FUNCTION devono essere soddisfatte le condizioni seguenti:

Tutte le viste e le funzioni definite dall'utente a cui la funzione fa riferimento devono essere associate a uno schema.
Tutti gli oggetti a cui fa riferimento la funzione devono essere inclusi nello stesso database in cui si trova la funzione. Per fare riferimento agli oggetti utilizzare nomi in una parte o in due parti.
È necessario disporre di autorizzazione REFERENCES per tutti gli oggetti (tabelle, viste e funzioni definite dall'utente) a cui la funzione fa riferimento.

È possibile usare l'istruzione ALTER FUNCTION per rimuovere l'associazione allo schema. L'istruzione ALTER FUNCTION deve ridefinire la funzione senza specificare WITH SCHEMABINDING.

Specificare i parametri

Le funzioni definite dall'utente accettano zero o più parametri di input e restituiscono tabelle o valori scalari. Una funzione può avere al massimo 1024 parametri di input. Quando a un parametro della funzione è associato un valore predefinito, quando si chiama la funzione è necessario specificare la parola chiave DEFAULT per ottenere il valore predefinito. Questa funzionalità risulta diversa per i parametri con valore predefinito nelle stored procedure definite dall'utente in cui l'omissione del parametro implica l'utilizzo del valore predefinito. Le funzioni definite dall'utente non supportano parametri di output.