Freigeben über

  • Artikel

Benutzerdefinierte Inlinefunktionen

Benutzerdefinierte Inlinefunktionen stellen eine Teilmenge von benutzerdefinierten Funktionen dar, die einen table-Datentyp zurückgeben. Inlinefunktionen ermöglichen die Funktionalität parametrisierter Sichten.

Im folgenden Beispiel werden die Namen von Läden und Städten für eine angegebene Region zurückgegeben.

USE AdventureWorks2008R2;
GO
CREATE VIEW CustomersByRegion
AS
SELECT DISTINCT S.Name AS Store, A.City
FROM Sales.Store AS S
    JOIN Sales.BusinessEntityAddress AS BEA ON BEA.BusinessEntityID = S.BusinessEntityID
    JOIN Person.Address AS A ON A.AddressID = BEA.AddressID
    JOIN Person.StateProvince SP ON 
        SP.StateProvinceID = A.StateProvinceID
WHERE SP.Name = N'Washington';
GO

Diese Sicht würde optimiert, wenn sie allgemeiner wäre und Benutzer die Region angeben könnten, die sie anzeigen möchten. Bei Sichten sind für die Suchbedingungen der WHERE-Klausel jedoch keine Parameter möglich. Sie können deshalb benutzerdefinierte Inlinefunktionen verwenden, um in Suchbedingungen der WHERE-Klausel Parameter zuzulassen. Das folgende Beispiel erstellt eine Inlinefunktion, mit der Benutzer die Region in ihrer Abfrage angeben können:

USE AdventureWorks2008R2;
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
        INNER JOIN Person.BusinessEntityAddress AS bea 
            ON bea.BusinessEntityID = s.BusinessEntityID 
        INNER JOIN Person.Address AS a 
            ON a.AddressID = bea.AddressID
        INNER JOIN Person.StateProvince AS 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')
ORDER BY City;
GO

Regeln für benutzerdefinierte Inlinefunktionen

Für benutzerdefinierte Inlinefunktionen gelten die folgenden Regeln:

  • Die RETURNS-Klausel enthält nur das table-Schlüsselwort. Sie müssen das Format einer Rückgabevariablen nicht definieren, weil es durch das Format des Resultsets der SELECT-Anweisung in der RETURN-Klausel festgelegt wird.

  • Es gibt keinen function_body, der durch BEGIN und END begrenzt ist.

  • Die RETURN-Klausel enthält eine einzige SELECT-Anweisung in Klammern. Das Resultset der SELECT-Anweisung bildet die Tabelle, die von der Funktion zurückgegeben wird. Die SELECT-Anweisung, die in einer Inlinefunktion verwendet wird, unterliegt denselben Einschränkungen, die für SELECT-Anweisungen in Sichten gelten.

  • Die Tabellenwertfunktion akzeptiert nur Konstanten oder @local_variable-Argumente.

Inlinefunktionen und indizierte Sichten

Mit Inlinefunktionen kann auch die Leistung von indizierten Sichten gesteigert werden. Die indizierte Sicht kann keine Parameter für die Suchbedingungen in der WHERE-Klausel verwenden, um das gespeicherte Resultset an bestimmte Benutzer anzupassen. Sie können jedoch eine indizierte Sicht definieren, die die vollständigen Daten für die Sicht speichert, und dann eine Inlinefunktion für die indizierte Sicht definieren, die parametrisierte Suchbedingungen enthält, mit denen die Benutzer die Ergebnisse anpassen können. Bei einer komplexen Sichtdefinition besteht der meiste Aufwand für das Erstellen eines Resultsets aus Schritten wie dem Erstellen von Aggregaten oder dem Join mehrerer Tabellen, wenn der gruppierte Index für die Sicht erstellt wird. Wenn Sie anschließend eine Inlinefunktion erstellen, die auf die indizierte Sicht verweist, kann die Funktion mit den parametrisierten Filtern des Benutzers bestimmte Zeilen aus dem materialisierten Resultset der indizierten Sicht zurückgeben. Beispiel:

  1. Sie definieren die vw_QuarterlySales-Sicht, die alle Verkaufsdaten in einem Resultset zusammenfasst, das für alle Geschäfte eine Zusammenfassung der Verkaufsdaten nach Quartal erstellt.

  2. Sie legen eine gruppierte Sicht für vw_QuarterlySales an, um ein Resultset zu erstellen, das die zusammengefassten Daten enthält.

  3. Sie erstellen eine Inlinefunktion, um die zusammengefassten Daten zu filtern:

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

  4. Benutzer können nun die Daten für ihr jeweiliges Geschäft durch Auswählen in der Inlinefunktion abrufen:

    SELECT *
FROM fn_QuarterlySalesByStore(14432)

Der meiste Aufwand beim Beantworten der in Schritt 4 ausgegebenen Abfragen besteht im Aggregieren der Verkaufsdaten nach Quartal. Die erforderlichen Aktionen werden einmal in Schritt 2 ausgeführt. Jede einzelne SELECT-Anweisung in Schritt 4 verwendet die fn_QuarterlySalesByStore-Funktion, um die für das jeweilige Geschäft spezifischen aggregierten Daten zu filtern.