Lavorare con le UDF in SQL Server 2000

Dopo circa due anni di assenza, torno a scrivere di SQL Server su HTML.it affrontando un argomento interessante per il programmatore: le Funzioni Definite dall'Utente.

Se siete un po' a digiuno in materia e volete prendere confidenza con il mondo di SQL Server vi dò un consiglio: leggete la guida alla programmazione in Transact-SQL pubblicata sul nostro sito.

Una panoramica sulle UDF's

In SQL Server esistono oggetti quali le viste e Le stored procedure in grado di offrire molto al programmatore Transact-SQL sia in termini di potenza che di estensibilità, sicurezza e performance. Nonostante gli indubbi vantaggi questi oggetti hanno alcune limitazioni:

1. Le viste non supportano la parametrizzazione, infatti non è possibile passare alcun parametro di ingresso ad una vista (cosa che potrebbe essere utile per creare filtri particolari sulle righe selezionate dalla vista stessa)
2. Le procedure memorizzate non possono essere incorporate in modo naturale all'interno di istruzioni DML, tipo una SELECT, dal momento che ritornano valori scalari o set di righe.

Per essere chiari non potremmo mai avere situazioni simili, che pure farebbero molto comodo:

/*
Chiamare una procedura memorizzata come fosse una tabella qualsiasi del nostro
database, IMPOSSIBILE!!!
*/
SELECT * FROM P_MIA_PROCEDURA
/*
Chiamare una vista in funzioni di parametri di ingresso personalizzati, IMPOSSIBILE!!!
*/
SELECT * FROM V_MIA_VISTA('PARAMETRO')

Allo scopo di superare queste limitazioni nella versione 2000 di SQL Server sono state introdotte le Funzioni definite dall'Utente Funzioni definite dal Sistema

Le UDFs vengono incorporate in istruzioni SELECT, colonne calcolate, vincoli CHECK e DEFAULT e per scrittura di codice sono molto simili alle procedure memorizzate anche se, essendo oggetti differenti, hanno una propria definizione a livello di sintassi Transact-SQL.  

In questo primo articolo presentiamo una panoramica generale sulle funzionalità delle UDFs ed in seguito vedremo come integrarle attivamente nelle nostre applicazioni.

La sintassi per creare una UDF

Una UDF viene creata tramite l'istruzione CREATE FUNCTION, modificata tramite ALTER FUNCTION ed eliminata tramite DROP FUNCTION. La sintassi generale per la creazione di una UDF:

Nella dichiarazione di una UDF possiamo distinguere una testata e un corpo.

La testata contiene il nome della funzione, la dichiarazione dei parametri di ingresso con il tipo dati associato e la parola chiave RETURNS che identifica il tipo dati restituito dalla funzione.

Il corpo invece contiene il codice della funzione ed è compreso tra un blocco di istruzioni BEGIN ed END e termina con una istruzione RETURN.

Caratteristiche, tipi e limiti delle UDF

Per poter implementare ed utilizzare una UDF dobbiamo prima conoscerne le caratteristiche,
in sintesi ecco le principali:

1. Ha sempre un identificatore sia per il sia per il nome proprietario
2. Deve restituire un singolo risultato che deve essere di natura scalare tabellare
3. Può avere zero o più parametri di ingresso ognuno dei quali accetta tutti i tipi di dati di SQL Server 2000, fatta eccezione per timestamp, cursor e table.  Per questi parametri sono ammessi valori di DEFAULT.
4. Non supporta l'uso di parametri in uscita.

Le UDF si dividono in due categorie Utente di Sistema

Le prime ovviamente sono quelle create all'interno dei vari database utente:

mentre le seconde sono predefinite durante l'installazione di SQL Server 2000 all'interno del database di sistema master.

Per vedere l'elenco di tutte le funzioni presenti su SQL Server eseguite la query indicata qui sotto:

go
select routine_definition AS [Sorgente], routine_name AS [Nome Funzione] from
information_schema.routines
where routine_type='Function' and routine_schema = 'system_function_schema'

È importante sottolineare che anche noi possiamo creare delle nostre funzioni utente di sistema, ma questo sarà argomento di un prossimo articolo!

Oltre alle caratteristiche di base è necessario conoscere le  restrizioni legate alle UDF: il determinismo effetti collaterali

Il determinismo

Tutte le funzioni possono dividersi in due gruppi: deterministiche e non-deterministiche.

Le prime producono sempre lo stesso risultato per gli stessi parametri di ingresso, le seconde ovviamente no. Alcuni esempi: GETDATE() è una funzione non deterministica perché ogni volta che viene invocata produce un risultato differente; DATEADD invece no, perché ogni volta che viene invocata, se i parametri di ingresso non variano, restituisce sempre lo stesso risultato.   

Nella panoramica sull'uso delle UDF abbiamo accennato a come possono essere utilizzate per valorizzare le colonne calcolate all'interno di tabelle, ebbene in questo specifico caso potremmo anche aggiungere un INDICE o un vincolo PRIMARY KEY sulla stessa colonna calcolata.

Questa operazione potrebbe esserci vietata, se la UDF a cui la colonna calcolata fa riferimento non risultasse di natura deterministica. Per capire se una UDF può dirsi deterministica devono essere soddisfatte le seguenti condizioni:

1. La funzione è vincolata allo schema, ciò significa che non è più possibile cambiare lo struttura degli oggetti a cui la funzione fa riferimento.
2. Tutte le funzioni chiamate all'interno dell'UDF devono essere deterministiche.
3. Il corpo della funzione non deve fare riferimento a nessun oggetto del database al di fuori del campo d'azione della funzione.

Nel prossimo articolo vedremo un esempio pratico di come creare un INDICE sul una colonna calcolata tramite una UDF.

Effetti collaterali

All'interno di una UDF non è possibile scrivere codice che in qualche modo alteri in modo permanente lo stato globale del sistema, perché così facendo si rischierebbe di modificare l'integrità del sistema stesso.

Le funzioni non devono avere "effetti collaterali" sul database; non devono cioè modificare nulla (scrivere su tabelle, creare oggetti, eliminare dati, ecc...) al di fuori del loro raggio d'azione. Questo significa che non possiamo utilizzare nel corpo della funzione istruzioni DML come INSERT, UPDATE, DELETE a meno che non riguardino cursori o variabili di tipo tabella usati esclusivamente all'interno della funzione (cioè locali e quindi distrutti una volta abbandonata la funzione).

Non è altresì possibile usare l'istruzione CREATE per creare oggetti anche temporanei o globali, richiamare procedure memorizzate, eseguire stringhe SQL dinamiche, utilizzare il comando RAISERROR per inviare messaggi al client oppure eseguire SELECT con la clausola FOR XML.

La regola primaria è questa: tutto ciò che avviene nella funzione non deve MAI modificare nulla all'esterno della stessa (dati, tabelle, ecc..).

Le istruzioni permesse all'interno di una UDF

Tenendo bene a mente le considerazioni fatte precedentemente ecco le istruzioni che possiamo utilizzare:

1. DECLARE per definire variabili e cursori locali alla funzione
2. SET per assegnare valori agli oggetti locali alla funzione
3. OPEN, FETCH, CLOSE per aprire, scorrere e chiudere cursori locali alla funzione, l'importante è che le operazioni di FETCH non inviino dati al client
4. IF, ELSE, WHILE, GOTO per il controllo di flusso
5. SELECT per assegnare espressioni e variabili locali alla funzione
6. UPDATE, INSERT, DELETE per modificare variabili di tipo tabella che sono locali alla funzione
7. EXECUTE per eseguire procedure memorizzate di tipo Extended

Dopo questa panoramica tra limiti e potenzialità delle UDF siamo pronti per scrivere la nostra prima UDF.

Nel precedente articolo abbiamo introdotto l'argomento e specificato il campo d'azione delle UDFs, evidenziandone limiti e potenzialità. Ora invece ci concentreremo sul loro utilizzo pratico.

Tipi di UDF's

SQL Server 2000 supporta 3 tipologie di funzioni definite dall'utente:

• Scalari
• Valutate a livello di tabella con istruzione singola
• Valutate a livello di tabella con istruzioni multiple

Funzioni Scalari

Ogni volta che vengono invocate queste UDF restituiscono un singolo valore scalare. I valori possibili che questa funzione può restituire sono tutti i tipi di dati supportati da SQL Server eccezion fatta per BLOB,  timestamp, cursor e table. La sintassi per la creazione di una funzione scalare è:

Ecco invece la nostra prima funzione:

Come possiamo vedere la clausola RETURNS ci dice esattamente il tipo dati che la funzione restituisce. Il corpo della funzione nel nostro caso rappresentato da una singola istruzione che calcola il volume di un oggetto a partire dai parametri di ingresso, ricordiamoci però che avremmo potuto tranquillamente scrivere più istruzioni per effettuare i nostri calcoli:

Anche in questo caso il risultato non cambia! Per richiamare la funzione all'interno del nostro codice Transact-SQL sarà sufficiente eseguire il codice sottostante.

Funziona tranquillamente sia con una SELECT che con una PRINT; inoltre la funzione deve essere richiamata obbligatoriamente (solo nel caso di funzioni scalari)  nel formato a due parti: [nome_utente][nome_funzione]

Le UDF scalari ci vengono spesso in aiuto quando vogliamo scrivere codice più leggibile e soprattutto riusabile. Incapsulare logiche di business all'interno di UDF può essere un approccio molto comodo perché assicura una adeguata flessibilità e scalabilità nella programmazione lato SQL Server.

Usare una UDF scalare sui vincoli di tipo CHECK

L'ANSI SQL permette di utilizzare delle sottoquery all'interno di vincoli di tipo CHECK, ma sfortunatamente SQL Server 2000 non supporta questa funzionalità. Grazie all'uso delle UDFs però possiamo aggirare l'ostacolo.

Supponiamo di voler validare una regola di questo tipo: data una tabella T0 contente una colonna ColREF, vogliamo che i valori inseriti in questa colonna siano già contenuti nella colonna ColKEY presente in una delle due tabelle correlate T1,T2.

Il codice completo dell'esempio è allegato nello zip dell'articolo, vediamo invece il codice della funzione e l'aggiunta del vincolo CHECK:

In questo modo se inseriamo in T0 un valore che non è contenuto né in T1 né in T2 avremo una risposta simile a questa:

Usare una UDF per affrontare questo problema è una soluzione molto più elegante e performante rispetto all'uso di triggers.

Usare una UDF scalare in colonne calcolate

Le funzioni scalari possono essere utilizzate all'interno di campi calcolati. Supponiamo ad esempio di avere la tabella DIMENSIONI contente le tre dimensioni (lunghezza, larghezza e altezza) di ipotetici oggetti. In questa caso potremmo creare una colonna calcolata dal nome volume, nel quale utilizziamo la funzione CalcoloVolume per avere direttamente nella tabella DIMENSIONI il valore del volume. Ecco il codice:

I vantaggi di questa soluzione sono molteplici ad esempio la possibilità di fare query che richiamano in causa direttamente il volume, il che risulta molto elegante.

rispetto alla tipica

Indicizzare una colonna calcolata con una UDF

Ricordiamoci che se la funzione è deterministica possiamo anche indicizzare la colonna calcolata, l'unico requisito è quello di applicare alla funzione l'opzione WITH SCHEMABINDING.


*/
CREATE FUNCTION dbo.CalcoloVolume
(@Lung decimal (4,1), @Larg decimal (4,1), @Altezz decimal (4,1))
RETURNS decimal (12,3)
WITH SCHEMABINDING /*Posso indicizzare la colonna volume*/

BEGIN
   RETURN (@Lung * @Larg * @Altezz)
END
/* Associo un indice alla colonna calcolata volume */
CREATE  INDEX [IDX_VOLUME] ON [dbo].[DIMENSIONI]([volume])

Così facendo abbiamo creato un legame esplicito tra i due oggetti: la funzione CalcolaVolume DIMENSIONI

Creare una lista di Autori

Tramite le UDFs è molto semplice anche creare una lista di elementi in formato csv, ad esempio Mario Rossi, Gino Verdi, Luigi Bianchi. I passi per produrre questa lista sono:

1. selezionare i dati tramite una SELECT opportuna
2. scorrere il set di righe restituito e concatenare in una variabile stringa i valori opportunamente separati da virgola

Allo scopo ho utilizzato il database pubs di SQL Server, per creare la lista degli autori dato un identificativo per il titolo:

Veramente molto comodo, nessuna concatenazione lato client, meno codice e più ordine nelle pagina ASP o ASP.NET!

Funzioni valutate a livello di tabella con istruzione singola

Le funzioni di questo tipo possono essere considerate viste parametrizzate. Il corpo contiene una unica istruzione SELECT generalmente accompagnata da una clausola WHERE in cui sono presenti criteri di ricerca basati sui parametri di ingresso della funzione. Ecco la sintassi per la creazione di una funzione inline con istruzione singola:

Queste funzioni restituiscono sempre una set di righe (rowset) tramite variabili di tipo tabella come indicato dalla parola chiave RETURNS. Vediamo un esempio:

Come possiamo vedere la funzione OrdiniClienti può essere considerata alla stregua di una vista vera e propria. Per richiamare questo tipo di funzioni, a differenza di quelle scalari, possiamo omettere il nome del proprietario della funzione, tipicamente dbo (cioè l'owner del database).

Funzioni valutate a livello di tabella con istruzione multipla

A differenza delle precedenti non ritornano semplicemente il risultato di una singola istruzione SELECT. La parola chiave RETURNS dichiara una variabile di tipo tabella che viene generalmente riempita dalle istruzioni contenute nel corpo della funzione. Ecco la sintassi per la creazione di una funzione inline con istruzioni multiple:

A differenza delle altre tipologie di funzioni, qui RETURN viene sempre chiamata come ultima istruzione del corpo delle funzione ma non abbinata nessun valore,  in quanto è la tabella definita in RETURNS ad essere sempre restituita come risultato finale dalla nostra funzione.

Un esempio molto interessante: F_SPLITTA_STRINGA

Il più delle volte vi sarete posti il problema di passare da una pagina ASP un array di valori, come parametro di ingresso, ad una stored procedure. Allo scopo di risolvere questo problema, nel codice allegato all'articolo, vi è la funzione F_SPLITTA_STRINGA.

Questa funzione emula la classica Split() di Visual Basic, partendo da una lista di valori separati da virgola o altro genere, produce come risultato una tabella di valori con una strutttura simile:

Ecco alcuni esempi di utilizzo di questa funzione:

La funzione accetta due parametri di ingresso, la lista dei valori da "splittare" e il separatore, di default è impostato la virgola e restituisce una tabella a due colonne, in cui troviamo la posizione e il valore dell'elemento separato nella lista.

Le performance delle UDFs

Se la comodità di incapsulare codice SQL al'interno delle UDF è grande, non sempre possiamo dire delle performance. Difatti prima di iniziare a sostituire in modo massivo viste e procedure memorizzate con le più "fresche" UDFs bisogna pensarci un attimo. I casi vanno valutati volta per volta. Un esempio: la funzione dbo.TotaleOrdini si occupa di calcolare il totale di un ordine partendo da uno specifico ID Ordine.


GO
CREATE FUNCTION dbo.TotaleOrdini(@OrdineID int)
RETURNS money AS
BEGIN
RETURN(
            SELECT CAST(
SUM(UnitPrice * Quantity * (1 - Discount)) AS money)
            FROM [Order Details]
                 
WHERE OrderID = @OrdineID
       )
END
GO
/*CASO 1 - eseguo la query usando la funzione personalizzata per calcolare il
totale*/
SELECT OrderID, dbo.TotaleOrdini(OrderID) AS OrderTotal FROM Orders ORDER BY OrderID
/*CASO 2 - eseguo la query calcolando direttamente nell SELECT il totale con la
formula prestabilita*/
SELECT OrderID, CAST(SUM(UnitPrice * Quantity * (1 -Discount)) AS money) AS OrderTotal
FROM [Order Details] GROUP BY OrderID ORDER BY OrderID

In entrambi i casi il risultato è lo stesso, sul mio PC 830 righe, con una differenza nel caso 2 il l'esecuzione della query è almeno 10/15 volte più veloce! Chiediamoci il perché?

Nel caso 1 il numero di letture I/O è notevolmente maggiore, perché per ogni riga della query (cioè ogni volta che invochiamo la funzione) SQL Server deve effettuare delle letture/accessi addizionali sulle pagine dati della tabella Order Details.



Nel secondo caso invece il numero di letture è notevolmente minore perché SQL Server accede ad ogni pagina dati una volta sola!



Le immagini fanno riferimento al tracciamento lato server delle due query.

Conclusione

Spero che la panoramica fatta in questo articolo possa esservi stata utile per comprendere le potenzialità dell'uso delle UDF all'interno della programmazione Transact-SQL in SQL Server.

La prossima volta affronteremo il problema di come trasformare una UDF scritta da noi in una UDF di sistema, rendendola così accessibile da tutti i database utente!