Teilen über

Erstellen von benutzerdefinierten Funktionen (Datenbank-Engine)

  • Artikel

Gilt für: SQL Server Azure SQL-Datenbank Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

In diesem Artikel wird beschrieben, wie Sie mithilfe von Transact-SQL eine benutzerdefinierte Funktion (User-Defined Function, UDF) in SQL Server erstellen.

Einschränkungen

  • Mit benutzerdefinierten Funktionen können keine Aktionen ausgeführt werden, die den Status einer Datenbank ändern.

  • Benutzerdefinierte Funktionen dürfen keine OUTPUT INTO-Klausel enthalten, deren Ziel eine Tabelle ist.

  • Benutzerdefinierte Funktionen können nicht mehrere Resultsets zurückgeben. Falls mehrere Resultsets zurückgegeben werden müssen, verwenden Sie eine gespeicherte Prozedur.

  • Die Fehlerbehandlung ist in einer benutzerdefinierten Funktion eingeschränkt. Eine benutzerdefinierte Funktion unterstützt TRY...CATCH, @ERROR oder RAISERROR nicht.

  • Benutzerdefinierte Funktionen können keine gespeicherte Prozedur, aber eine erweiterte gespeicherte Prozedur aufrufen.

  • Benutzerdefinierte Funktionen können kein dynamisches SQL und keine temporären Tabellen verwenden. Tabellenvariablen sind zulässig.

  • SET-Anweisungen sind in einer benutzerdefinierten Funktion nicht zulässig.

  • Die Klausel FOR XML ist nicht zulässig.

  • Benutzerdefinierte Funktionen können geschachtelt werden. Dies bedeutet, dass eine benutzerdefinierte Funktion eine andere aufrufen kann. Die Schachtelungsebene wird um eins erhöht, wenn die aufgerufene Funktion mit der Ausführung beginnt, und wird wieder um eins erniedrigt, wenn die aufgerufene Funktion die Ausführung beendet. Benutzerdefinierte Funktionen unterstützen bis zu 32 geschachtelte Ebenen. Ein Überschreiten der maximalen Schachtelungsebenen verursacht das Fehlschlagen der gesamten Funktionsaufrufskette. Alle Verweise auf verwalteten Code von einer benutzerdefinierten Transact-SQL-Funktion aus gelten hinsichtlich des Maximums von 32 Schachtelungsebenen als eine Ebene. Methoden, die aus verwaltetem Code aufgerufen werden, werden nicht mitgezählt.

  • Die folgenden Service Broker-Anweisungen können nicht in die Definition einer benutzerdefinierten Transact-SQL-Funktion eingeschlossen werden:

    • BEGIN DIALOG CONVERSATION
    • END CONVERSATION
    • GET CONVERSATION GROUP
    • MOVE CONVERSATION
    • RECEIVE
    • SEND

Berechtigungen

Erfordert die CREATE FUNCTION-Berechtigung in der Datenbank und die ALTER-Berechtigung für das Schema, in dem die Funktion erstellt wird. Wenn die Funktion einen benutzerdefinierten Typ angibt, wird die EXECUTE-Berechtigung für den Typ benötigt.

Beispiele für Skalarfunktionen

Skalarfunktion (benutzerdefinierte Skalarfunktion)

Das folgende Beispiel erstellt eine Skalarfunktion (benutzerdefinierte Skalarfunktion) mit mehreren Anweisungen in der AdventureWorks2022-Datenbank. Die Funktion nimmt einen Eingabewert ( ProductID) an und gibt einen einzelnen Datenwert zurück, der die aggregierte Menge des Lagerbestands für das angegebene Produkt darstellt.

IF OBJECT_ID (N'dbo.ufnGetInventoryStock', N'FN') IS NOT NULL
    DROP FUNCTION ufnGetInventoryStock;
GO
CREATE FUNCTION dbo.ufnGetInventoryStock(@ProductID int)
RETURNS int
AS
-- Returns the stock level for the product.
BEGIN
    DECLARE @ret int;
    SELECT @ret = SUM(p.Quantity)
    FROM Production.ProductInventory p
    WHERE p.ProductID = @ProductID
        AND p.LocationID = '6';
     IF (@ret IS NULL)
        SET @ret = 0;
    RETURN @ret;
END;

Im folgenden Beispiel wird die ufnGetInventoryStock -Funktion verwendet, um den aktuellen Lagerbestand für Produkte mit einer ProductModelID zwischen 75 und 80 zurückzugeben.

SELECT ProductModelID, Name, dbo.ufnGetInventoryStock(ProductID)AS CurrentSupply
FROM Production.Product
WHERE ProductModelID BETWEEN 75 and 80;

Weitere Informationen zu Skalarfunktionen sowie einige Beispiele finden Sie unter CREATE FUNCTION.

Beispiele für Tabellenwertfunktionen

Inline-Tabellenwertfunktion

Das folgende Beispiel erstellt eine Inline-Tabellenwertfunktion (Table-Valued Function, TVF) in der AdventureWorks2022-Datenbank. Die Funktion nimmt einen Eingabeparameter (eine Kunden-ID (Geschäfts-ID)) an und gibt die Spalten ProductID, Namesowie das Aggregat der bisherigen Verkaufseinnahmen dieses Jahres als YTD Total für jedes Produkt zurück, das an das Geschäft verkauft wurde.

IF OBJECT_ID (N'Sales.ufn_SalesByStore', N'IF') IS NOT NULL
    DROP FUNCTION Sales.ufn_SalesByStore;
GO
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
    JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID
    JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID
    JOIN Sales.Customer AS C ON SH.CustomerID = C.CustomerID
    WHERE C.StoreID = @storeid
    GROUP BY P.ProductID, P.Name
);

Das folgende Beispiel ruft die Funktion auf und gibt die Kunden-ID 602 an.

SELECT * FROM Sales.ufn_SalesByStore (602);

Tabellenwertfunktion mit mehreren Anweisungen

Das folgende Beispiel erstellt eine Tabellenwertfunktion mit mehreren Anweisungen (Multi-Statement Table-Valued Function, MSTVF) in der AdventureWorks2022-Datenbank. Die Funktion nimmt einen einzelnen Eingabeparameter ( EmployeeID ) an und gibt eine Liste aller Mitarbeiter zurück, die dem angegebenen Mitarbeiter direkt oder indirekt unterstellt sind. Die Funktion wird dann unter Angabe der Mitarbeiternummer 109 aufgerufen.

IF OBJECT_ID (N'dbo.ufn_FindReports', N'TF') IS NOT NULL
    DROP FUNCTION dbo.ufn_FindReports;
GO
CREATE FUNCTION dbo.ufn_FindReports (@InEmpID INTEGER)
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 (
        SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, 0 -- Get the initial list of Employees for Manager n
        FROM HumanResources.Employee e
INNER JOIN Person.Person p
ON p.BusinessEntityID = e.BusinessEntityID
        WHERE e.BusinessEntityID = @InEmpID
        UNION ALL
        SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, RecursionLevel + 1 -- Join recursive member to anchor
        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

Das folgende Beispiel ruft die Funktion auf und gibt die Mitarbeiternummer 1 an.

SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel
FROM dbo.ufn_FindReports(1);

Weitere Informationen zu Inline-Tabellenwertfunktionen und Tabellenwertfunktionen mit mehreren Anweisungen sowie einige Beispiele finden Sie unter CREATE FUNCTION.

Bewährte Methoden

Wenn eine benutzerdefinierte Funktion nicht mit der SCHEMABINDING-Klausel erstellt wurde, können sich die an zugrunde liegenden Objekten vorgenommenen Änderungen auf die Definition der Funktion auswirken und bei Aufruf der Funktion zu unerwarteten Ergebnissen führen. Es wird empfohlen, eine der folgenden Methoden zu implementieren, um sicherzustellen, dass die Funktion nicht aufgrund von Änderungen an den zugrunde liegenden Objekten veraltet:

  • Geben Sie beim Erstellen der benutzerdefinierten Funktion die WITH SCHEMABINDING-Klausel an. Damit wird sichergestellt, dass die Objekte, auf die in der Funktionsdefinition verwiesen wird, nicht geändert werden können, es sei denn, die Funktion wird auch geändert.

  • Führen Sie die gespeicherte Prozedur sp_refreshsqlmodule aus, nachdem Sie ein Objekt geändert haben, das in der Definition der benutzerdefinierten Funktion angegeben ist.

Wenn Sie eine UDF erstellen, die nicht auf Daten zugreift, geben Sie die Option SCHEMABINDING an, um zu verhindern, dass der Abfrageoptimierer unnötige Spool-Operatoren für Abfragepläne mit diesen UDFs erzeugt. Weitere Informationen zu Spoolvorgängen finden Sie unter Referenz zu logischen und physischen Showplanoperatoren. Weitere Informationen zum Erstellen einer schemagebundenen Funktion finden Sie unter Schemagebundene Funktionen.

Ein Verknüpfen mit einer Tabellenwertfunktion mit mehreren Anweisungen in einer FROM-Klausel ist möglich, kann jedoch zu Leistungseinbußen führen. SQL Server kann für einige Anweisungen, die in einer Tabellenwertfunktion mit mehreren Anweisungen enthalten sein können, nicht alle optimierten Techniken verwenden, was zu einem suboptimalen Abfrageplan führt. Um die bestmögliche Leistung zu erzielen, sollten nach Möglichkeit anstelle von Funktionen Joins zwischen Basistabellen verwendet werden.

Die festgelegte Kardinalitätsschätzung von Tabellenwertfunktionen mit mehreren Anweisungen beträgt ab SQL Server 2014 (12.x) „100“ und in früheren Versionen von SQL Server „1“.

Ab SQL Server 2017 (14.x) kann beim Optimieren eines Ausführungsplans, der Tabellenwertfunktionen mit mehreren Anweisungen verwendet, eine verschachtelte Ausführung genutzt werden, was dazu führt, dass die tatsächliche Kardinalität anstelle der oben genannten Heuristik verwendet wird.

Weitere Informationen finden Sie unter Verschachtelte Ausführung mit Tabellenwertfunktionen mit mehreren Anweisungen.

ANSI_WARNINGS wird bei der Übergabe von Parametern in einer gespeicherten Prozedur oder in einer benutzerdefinierten Funktion oder beim Deklarieren und Festlegen von Variablen in einer Batchanweisung nicht berücksichtigt. Wird beispielsweise eine Variable als char(3) definiert und dann auf einen Wert festgelegt, der länger als drei Zeichen ist, werden die Daten auf die definierte Größe abgeschnitten, und die Anweisung INSERT oder UPDATE wird erfolgreich ausgeführt.

Zugehöriger Inhalt