Funzioni inline definite dall'utente

Articolo
12/15/2008

Le funzioni inline definite dall'utente sono un subset di funzioni definite dall'utente che restituiscono un tipo di dati table. Le funzioni inline possono essere utilizzate per ottenere la funzionalità delle viste parametrizzate.

Nell'esempio seguente vengono restituiti nomi di archivio e città per una regione specificata:

USE AdventureWorks;
GO
CREATE VIEW CustomersByRegion
AS
SELECT DISTINCT S.Name AS Store, A.City
FROM Sales.Store AS S
    JOIN Sales.CustomerAddress AS CA ON CA.CustomerID = S.CustomerID
    JOIN Person.Address AS A ON A.AddressID = CA.AddressID
    JOIN Person.StateProvince SP ON 
        SP.StateProvinceID = A.StateProvinceID
WHERE SP.Name = N'Washington';
GO

Questa vista sarebbe migliore se fosse più generalizzata e consentisse agli utenti di specificare la regione che sono interessati a visualizzare. Le viste, tuttavia, non supportano i parametri nelle condizioni di ricerca specificate nella clausola WHERE. Le funzioni inline definite dall'utente sono utilizzabili per supportare i parametri nelle condizioni di ricerca specificate nella clausola WHERE. Nell'esempio seguente viene creata una funzione inline che consente agli utenti di specificare la regione nella propria query:

USE AdventureWorks;
GO
IF OBJECT_ID(N'Sales.ufn_CustomerNamesInRegion', N'IF') IS NOT NULL
    DROP FUNCTION Sales.ufn_CustomerNamesInRegion;
GO
CREATE FUNCTION Sales.ufn_CustomerNamesInRegion
                 ( @Region nvarchar(50) )
RETURNS table
AS
RETURN (
        SELECT DISTINCT S.Name AS Store, A.City
        FROM Sales.Store AS S
        JOIN Sales.CustomerAddress AS CA ON CA.CustomerID = S.CustomerID
        JOIN Person.Address AS A ON A.AddressID = CA.AddressID
        JOIN Person.StateProvince SP ON 
        SP.StateProvinceID = A.StateProvinceID
        WHERE SP.Name = @Region
       );
GO
-- Example of calling the function for a specific region
SELECT *
FROM Sales.ufn_CustomerNamesInRegion(N'Washington');
GO

Regole funzione inline definite dall'utente

Le funzioni inline definite dall'utente si attengono alle regole seguenti:

La clausola RETURN include solo la parola chiave table. Non è necessario definire il formato di una variabile restituita in quanto è impostato dal formato del set di risultati dell'istruzione SELECT nella clausola RETURN.
Non è presente alcun function_body delimitato da BEGIN e END.
La clausola RETURN include una singola istruzione SELECT tra parentesi. Il set di risultati dell'istruzione SELECT costituisce la tabella virtuale restituita dalla funzione. L'istruzione SELECT utilizzata in una funzione inline è soggetta alle stesse restrizioni delle istruzioni SELECT utilizzate nelle viste.
La funzione valutata a livello di tabella accetta solo costanti oppure argomenti @local_variable.

Funzioni inline e viste indicizzate

Le funzioni inline possono inoltre essere utilizzate per aumentare le funzioni delle viste indicizzate. La vista indicizzata non può utilizzare i parametri nella proprie condizioni di ricerca della clausola WHERE per personalizzare il set di risultati archiviato in base a utenti specifici. È possibile, tuttavia, definire una vista indicizzata che archivia il set di dati completo corrispondente alla vista, e quindi definire una funzione inline sulla vista indicizzata che include le condizioni di ricerca parametrizzate che consentono agli utenti di personalizzare i propri risultati. Se la definizione della vista è complessa, la maggior parte del lavoro eseguito per generare un set di risultati riguarda operazioni come la creazione di aggregati o l'unione in join di più tabelle quando viene creato l'indice cluster sulla vista. Se successivamente si crea una funzione inline che fa riferimento alla vista indicizzata, la funzione può applicare i filtri parametrizzati dell'utente per restituire righe specifiche dal set di risultati materializzato della vista indicizzata. Ad esempio:

Definire una vista vw_QuarterlySales che aggrega tutti i dati di vendita in un set di risultati che indica i dati di vendita riepilogati per trimestre di tutti i punti vendita.
Creare un indice cluster su vw_QuarterlySales per materializzare un set di risultati che contenga i dati riepilogati.

Creare una funzione inline per filtrare i dati riepilogati:

CREATE FUNCTION dbo.ufn_QuarterlySalesByStore
     ( @StoreID int )
RETURNS table
AS
RETURN (
        SELECT *
        FROM SalesDB.dbo.vw_QuarterlySales
        WHERE StoreID = @StoreID
       )

Gli utenti possono quindi ottenere i dati per il punto vendita di interesse selezionandoli dalla funzione inline:
```
SELECT *
FROM fn_QuarterlySalesByStore(14432)
```

La maggior parte del lavoro necessario a rispondere alle query generate al passaggio 4 riguarda l'aggregazione dei dati di vendita per trimestre. Tale lavoro viene eseguito una volta al passaggio 2. Ogni singola istruzione SELECT nel passaggio 4 utilizza la funzione fn_QuarterlySalesByStore per estrarre i dati aggregati specifici del punto vendita di interesse.

Vedere anche

Concetti

Linee guida per la progettazione di funzioni definite dall'utente
Funzioni definite dall'utente valutate a livello di tabella
Funzioni deterministiche e non deterministiche
Riformulazione di stored procedure come funzioni
Creazione di viste indicizzate

Guida in linea e informazioni

Assistenza su SQL Server 2005

Share via