Door de gebruiker gedefinieerde functies maken (Database Engine)

Artikel
2025-03-05

van toepassing op:SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

In dit artikel wordt beschreven hoe u een door de gebruiker gedefinieerde functie (UDF) maakt in SQL Server met behulp van Transact-SQL.

Beperkingen en beperkingen

Door de gebruiker gedefinieerde functies kunnen niet worden gebruikt om acties uit te voeren die de databasestatus wijzigen.
Door de gebruiker gedefinieerde functies kunnen geen OUTPUT INTO-component bevatten die een tabel als doel heeft.
Door de gebruiker gedefinieerde functies kunnen niet meerdere resultatensets retourneren. Gebruik een opgeslagen procedure als u meerdere resultatensets wilt retourneren.
Foutafhandeling is beperkt in een door de gebruiker gedefinieerde functie. Een UDF biedt geen ondersteuning voor TRY...CATCH, @ERROR of RAISERROR.
Door de gebruiker gedefinieerde functies kunnen geen opgeslagen procedure aanroepen, maar kunnen een uitgebreide opgeslagen procedure aanroepen.
Door de gebruiker gedefinieerde functies kunnen geen gebruik maken van dynamische SQL- of tijdelijke tabellen. Tabelvariabelen zijn toegestaan.
Instructies van het type SET zijn niet toegestaan in een door de gebruiker gedefinieerde functie.
De FOR XML-clausule is niet toegestaan.
Door de gebruiker gedefinieerde functies kunnen worden genest; Dat wil gezegd, één door de gebruiker gedefinieerde functie kan een andere aanroepen. Het nestniveau wordt verhoogd wanneer de aangeroepen functie wordt gestart en afneemt wanneer de aangeroepen functie de uitvoering voltooit. Gebruikersgedefinieerde functies kunnen tot maximaal 32 niveaus worden genest. Als u de maximumniveaus van nesten overschrijdt, mislukt de hele aanroepende functieketen. Elke verwijzing naar beheerde code van een gebruikergedefinieerde Transact-SQL-functie telt als één niveau ten opzichte van de nestlimiet van 32 niveaus. Methoden die vanuit beheerde code worden aangeroepen, tellen niet mee voor deze limiet.
De volgende Service Broker-instructies kunnen niet worden opgenomen in de definitie van een door de gebruiker gedefinieerde Transact-SQL functie:
- BEGIN DIALOG CONVERSATION
- END CONVERSATION
- GET CONVERSATION GROUP
- MOVE CONVERSATION
- RECEIVE
- SEND

Machtigingen

Vereist CREATE FUNCTION machtiging in de database en ALTER machtiging voor het schema waarin de functie wordt gemaakt. Als de functie een door de gebruiker gedefinieerd type opgeeft, is EXECUTE machtiging voor het type vereist.

Voorbeelden van scalaire functies

Scalaire functie (scalaire UDF)

In het volgende voorbeeld wordt een multi-statement gemaakt scalaire functie (scalaire UDF) in de AdventureWorks2022-database. De functie gebruikt één invoerwaarde, een ProductIDen retourneert één gegevenswaarde, de geaggregeerde hoeveelheid van het opgegeven product in de voorraad.

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;

In het volgende voorbeeld wordt de functie ufnGetInventoryStock gebruikt om de huidige voorraadhoeveelheid te retourneren voor producten met een ProductModelID tussen 75 en 80.

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

Zie CREATE FUNCTIONvoor meer informatie en voorbeelden van scalaire functies.

Voorbeelden van functies met tabelwaarden

Inline tabelwaarde functie (TVF)

In het volgende voorbeeld wordt een inline tabelwaardefunctie (TVF) gemaakt in de Database AdventureWorks2022. De functie gebruikt één invoerparameter, een klant-id (winkel) en retourneert de kolommen ProductID, Nameen de cumulatieve omzet van het jaar tot heden als YTD Total voor elk product dat in de winkel wordt verkocht.

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
);

In het volgende voorbeeld wordt de functie aangeroepen en wordt de klant-id 602 opgegeven.

SELECT * FROM Sales.ufn_SalesByStore (602);

Tabelwaardefunctie met meerdere instructies (MSTVF)

In het volgende voorbeeld wordt een tabelwaardefunctie met meerdere instructies (MSTVF) gemaakt in de AdventureWorks2022-database. De functie gebruikt één invoerparameter, een EmployeeID en retourneert een lijst met alle werknemers die direct of indirect rapporteren aan de opgegeven werknemer. De functie wordt vervolgens aangeroepen met de werknemer-id 109.

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

In het volgende voorbeeld wordt de functie aangeroepen en wordt de werknemer-id 1 opgegeven.

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

Zie CREATE FUNCTIONvoor meer informatie en voorbeelden van inline-tabelwaardefuncties (inline-TVF's) en multi-statement tabelwaardefuncties (MSTVF's).

Goede praktijken

Als een door de gebruiker gedefinieerde functie (UDF) niet wordt gemaakt met de SCHEMABINDING component, kunnen wijzigingen die worden aangebracht in onderliggende objecten de definitie van de functie beïnvloeden en onverwachte resultaten opleveren wanneer deze worden aangeroepen. U wordt aangeraden een van de volgende methoden te implementeren om ervoor te zorgen dat de functie niet verouderd raakt vanwege wijzigingen in de onderliggende objecten:

Geef de WITH SCHEMABINDING-clausule op wanneer u de UDF maakt. Dit zorgt ervoor dat de objecten waarnaar wordt verwezen in de functiedefinitie niet kunnen worden gewijzigd, tenzij de functie ook wordt gewijzigd.
Voer de sp_refreshsqlmodule opgeslagen procedure uit nadat u een object hebt gewijzigd dat is opgegeven in de definitie van de UDF.

Als u een UDF maakt die geen toegang heeft tot gegevens, geeft u de SCHEMABINDING optie op om te voorkomen dat de query optimizer onnodige spooloperators genereert voor queryplannen met betrekking tot deze UDF's. Zie Showplan Logical and Physical Operators Referencevoor meer informatie over spools. Zie Schemagebonden functiesvoor meer informatie over het maken van een schemagebonden functie.

Deelnemen aan een MSTVF in een FROM clausule is mogelijk, maar kan leiden tot slechte prestaties. SQL Server kan niet alle geoptimaliseerde technieken gebruiken voor sommige instructies die kunnen worden opgenomen in een MSTVF, wat resulteert in een suboptimaal queryplan. Om de best mogelijke prestaties te verkrijgen, gebruikt u waar mogelijk joins tussen basistabellen in plaats van functies.

MSTVF's hebben een vaste kardinaliteits schatting van 100 vanaf SQL Server 2014 (12.x) en 1 voor eerdere SQL Server-versies.

Vanaf SQL Server 2017 (14.x) kan het optimaliseren van een uitvoeringsplan dat MSTVF's gebruikt, interleaved execution gebruiken, wat resulteert in het gebruik van de werkelijke kardinaliteit in plaats van de bovenstaande heuristiek.

Zie Interleaved-uitvoering voor tabelfuncties met meerdere instructiesvoor meer informatie.

ANSI_WARNINGS wordt niet gehonoreerd wanneer u parameters doorgeeft in een opgeslagen procedure, door de gebruiker gedefinieerde functie of wanneer u variabelen declareert en instelt in een batch-instructie. Als een variabele bijvoorbeeld is gedefinieerd als teken(3)en vervolgens is ingesteld op een waarde die groter is dan drie tekens, worden de gegevens afgekapt tot de gedefinieerde grootte en slaagt de instructie INSERT of UPDATE.

Delen via