CREATE FUNCTION

Van toepassing op:SQL analytics-eindpunt in Microsoft Fabric en Warehouse in Microsoft Fabric

CREATE FUNCTION maakt inline tabelwaardefuncties en scalaire functies.

Opmerking

Externe UDF's zijn een previewfunctie in Fabric Data Warehouse.

Dit artikel geldt voor Microsoft Fabric Data Warehouse en het SQL-analyse-eindpunt van Fabric items. Voor andere platforms, zie CREATE FUNCTION (Transact-SQL).

Een door de gebruiker gedefinieerde functie is een Transact-SQL routine die parameters accepteert, een actie uitvoert zoals een complexe berekening, en het resultaat van die actie als waarde teruggeeft. Scalaire functies retourneren een scalaire waarde, zoals een getal of tekenreeks. Door de gebruiker gedefinieerde functies met tabelwaarden (TVF's) retourneren een tabel.

Gebruik CREATE FUNCTION om een herbruikbare T-SQL-routine te maken die je op deze manieren kunt gebruiken:

  • In Transact-SQL uitspraken zoals SELECT.
  • In Warehouse Transact-SQL datamanipulatie-statements (DML) zoals UPDATE, INSERT, en DELETE.
  • In applicaties die de functie aanroepen.
  • In de definitie van een andere door de gebruiker gedefinieerde functie.
  • Om een opgeslagen procedure te vervangen.

Specificeer CREATE OR ALTER FUNCTION om een nieuwe functie te maken als er geen bestaat onder die naam, of verander een bestaande functie in één enkele instructie.

Transact-SQL syntaxisconventies

Syntaxis

Syntaxis van scalaire functie

CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] parameter_data_type
    [ = default ] }
    [ ,...n ]
  ]
)
RETURNS return_data_type
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    BEGIN
        function_body
        RETURN scalar_expression
    END
[ ; ]

<function_option>::=
{
    [ INLINE = AUTO ]
  | [ SCHEMABINDING ]
  | [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
}

Syntaxis van inline-tabelwaardefunctie

CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] parameter_data_type
    [ = default ] }
    [ ,...n ]
  ]
)
RETURNS TABLE
    [ WITH SCHEMABINDING ]
    [ AS ]
    RETURN [ ( ] select_stmt [ ) ]
[ ; ]

Syntaxis van externe functies

Een externe UDF is een functie die verwijst naar een externe Fabric User Data Function.

CREATE FUNCTION [ schema_name. ] function_name
[ RETURNS return_data_type ]
AS EXTERNAL FUNCTION external_function_set_name.external_function_name
[ ; ]

Opmerking

Externe UDF's zijn een previewfunctie in Fabric Data Warehouse.

De CREATE FUNCTION instructie leidt automatisch het retourtype, parameters en parametertypes af uit de definitie van de referentie van de externe Fabric User Data Function. Als de handtekening van de externe Fabric User Data Function verandert, moet je de functie opnieuw aanmaken om de definitie te synchroniseren met de bijgewerkte handtekening.

Argumenten

schema_name

De naam van het schema waartoe de door de gebruiker gedefinieerde functie behoort.

function_name

De naam van de door de gebruiker gedefinieerde functie. Functienamen moeten voldoen aan de regels voor identificaties en uniek zijn binnen de database en het schema daarvan.

Voor native T-SQL scalaire en inline tabelwaardige functiedefinities, voeg haakjes toe na de functienaam, zelfs wanneer er geen parameters zijn. Externe functiedefinities gebruiken de aparte AS EXTERNAL FUNCTION syntaxis.

@ parameter_name

Een parameter in de door de gebruiker gedefinieerde functie. Je kunt één of meer parameters declareren.

Een functie kan tot 2.100 parameters hebben. Wanneer een gebruiker of applicatie een functie aanroept, moet je de waarde voor elke gedeclareerde parameter opgeven, tenzij je een standaardwaarde voor die parameter definieert.

Geef een parameternaam op met behulp van een at-teken (@) als het eerste teken. De parameternaam moet voldoen aan de regels voor identifiers. Parameters zijn lokaal aan de functie. Je kunt dezelfde parameternamen in andere functies gebruiken. Parameters kunnen alleen constanten vervangen. Ze kunnen niet worden gebruikt in plaats van tabelnamen, kolomnamen of de namen van andere databaseobjecten.

ANSI_WARNINGS wordt niet gerespecteerd 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 je bijvoorbeeld een variabele definieert als char(3) en deze vervolgens op een waarde groter dan drie tekens zet, wordt de data afgekapt tot de gedefinieerde grootte en slaagt de SQL-instructie.

parameter_data_type

Het gegevenstype parameter. Voor Transact-SQL functies zijn alle ondersteunde scalaire gegevenstypen toegestaan.

[ = standaard ]

Een standaardwaarde voor de parameter. Als je een standaardwaarde definieert, kun je de functie uitvoeren zonder een waarde voor die parameter op te geven.

Wanneer een parameter van de functie een standaardwaarde heeft, moet je het trefwoord DEFAULT opgeven bij het aanroepen van de functie om de standaardwaarde op te halen. Dit gedrag verschilt van het gebruik van parameters met standaardwaarden in opgeslagen procedures, waarbij het weglaten van de parameter ook de standaardwaarde impliceert.

return_data_type

De retourwaarde van een scalaire, door de gebruiker gedefinieerde functie.

Voor functies in Fabric Data Warehouse kun je alle datatypes gebruiken behalve de timestamp van de rowversie/. Niet-scalaire types zoals de tabel zijn niet toegestaan.

Voor externe UDF's (Preview) is de RETURNS clausule optioneel. Indien weggelaten, wordt het retourtype automatisch afgeleid uit het retourtype van de verwezen Fabric User Data Function. Je kunt de RETURNS clausule specificeren om het afgeleide retourtype te overschrijven wanneer dat nodig is.

function_body

Een reeks Transact-SQL instructies.

In scalaire functies is function_body een reeks Transact-SQL instructies die samen een scalaire waarde evalueren, waaronder:

  • Expressie met één instructie
  • Multi-statement expressies (IF/ELSE en BEGIN/END blokken)
  • Lokale variabelen
  • Aanroepen naar ingebouwde SQL-functies die beschikbaar zijn
  • Aanroepen naar andere UDF's
  • SELECT instructies en verwijzingen naar tabellen, weergaven en inline tabelwaardefuncties
  • Control-of-flow-instructies, waaronder IF/ELSE, WHILE, BREAK, CONTINUE, en RETURN

Voor externe UDF's (Preview) kun je de function_body niet specificeren omdat de functie-implementatie extern wordt gedefinieerd in de bijbehorende Fabric User Data Function. De database slaat alleen de functiemetadata op. De uitvoerbare logica bevindt zich in de definitie van de externe functie.

scalar_expression

Hiermee geeft u de scalaire waarde op die de scalaire functie retourneert.

select_stmt

De enkele SELECT instructie waarmee de retourwaarde van een inline-tabelwaardefunctie wordt gedefinieerd. Voor een inline tabelwaarde functie is er geen functiebody. De tabel is de resultaatset van één enkele SELECT instructie.

TABLE

Hiermee geeft u aan dat de retourwaarde van de tabelwaardige functie (TVF) een tabel is. Je kunt alleen constanten en @local_variables doorgeven aan TVF's.

In inline TVF's (preview) definieer je de TABLE retourwaarde via één enkele SELECT instructie. Inline-functies hebben geen gekoppelde retourvariabelen.

<function_option>

In Fabric Data Warehouse worden de ENCRYPTION en EXECUTE AS trefwoorden niet ondersteund.

De ondersteunde functieopties zijn onder andere:

INLINE = AUTO

Specificeert of een door de gebruiker gedefinieerde scalairfunctie kan worden aangemaakt of aangepast ongeacht de inline-eisen. De INLINE clausule is optioneel. Voor een inlineable scalar UDF verandert specificeren INLINE = AUTO niets aan de inlineability of uitvoeringsgedrag.

Voor een ondersteund standalone aanroeppatroon, zie Maak een scalaire UDF voor standalone uitvoering.

SCHEMABINDING

Hiermee geeft u aan dat de functie is gebonden aan de databaseobjecten waarnaar wordt verwezen. Wanneer je specificeert SCHEMABINDING, kun je de onderliggende objecten (zoals bijvoorbeeld een weergave of een tabel) niet aanpassen op een manier die de functiedefinitie beïnvloedt. Je moet eerst de functiedefinitie aanpassen of verwijderen om afhankelijkheden van het object dat je wilt aanpassen te verwijderen.

De binding van de functie aan de objecten waarnaar wordt verwezen, wordt alleen verwijderd wanneer een van de volgende acties plaatsvindt:

  • Je laat de functie vallen.

  • Je doet ALTER de functie-statement en verwijdert de SCHEMABINDING optie.

Je kunt een functie alleen schema-binden als de volgende voorwaarden waar zijn:

  • Alle door de gebruiker gedefinieerde functies waar de functie naar verwijst, zijn ook schema-gebonden.

  • De functie verwijst naar objecten door gebruik te maken van een tweedelige naam.

  • Binnen de body van UDF's kun je alleen naar ingebouwde functies en andere UDF's in dezelfde database verwijzen.

  • De gebruiker die de CREATE FUNCTION instructie uitvoert heeft REFERENCES-rechten op de databaseobjecten waar de functie naar verwijst.

Als u SCHEMABINDING wilt verwijderen, gebruikt u ALTER.

GEEFT ALS RESULTAAT NULL BIJ NULL-INVOER | OPGEROEPEN OP NULL-INVOER

Hiermee geeft u het OnNULLCall kenmerk van een scalaire-waardefunctie. Als je dit attribuut niet specificeert, CALLED ON NULL INPUT wordt standaard impliciet, en voert het functielichaam uit, zelfs als NULL het als argument wordt doorgegeven.

ALS EXTERN FUNCTION

Verwijst naar een Fabric User Data Function (UDF). Wanneer de functie wordt aangeroepen, wordt de uitvoering gedelegeerd aan de verwezen Fabric User Data Function, waar de logica wordt uitgevoerd en het resultaat wordt teruggegeven aan de T-SQL-caller.

Deze clausule accepteert de volgende parameters:

  • Naam van functieset: De naam van de functieset die de Fabric User Data Function bevat. Elke Fabric User Data Function moet tot een functieset behoren.
  • Functienaam: De naam van de Fabric User Data-functie waarnaar wordt verwezen.

De volgende syntaxis laat zien hoe deze parameters worden gespecificeerd:

CREATE FUNCTION [schema_name.]function_name
AS EXTERNAL FUNCTION <<functionset_name>>.<<python_function_name>>

Omdat de functie op afstand wordt uitgevoerd, veroorzaakt het aanroepen van een externe functie doorgaans een hogere latentie dan het uitvoeren van een native T-SQL, door de gebruiker gedefinieerde functie. Gebruik externe functies wanneer de vereiste bedrijfslogica in Fabric is geïmplementeerd en niet direct in T-SQL kan worden uitgedrukt.

De CREATE FUNCTION instructie leidt automatisch het retourtype, parameters en parametertypes af uit de definitie van de referentie van de externe Fabric User Data Function. Als de handtekening van de externe Fabric User Data Function verandert, moet je de functie opnieuw aanmaken om de definitie te synchroniseren met de bijgewerkte handtekening.

Beste praktijken

  • Schrijf je door de gebruiker gedefinieerde functies zo dat ze inlineable zijn. Voor meer informatie over het inline-concept, zie Inlining van scalair UDF. Voor voorbeelden van hoe je een scalair UDF inlineable maakt, zie Create scalar UDF in Microsoft Fabric Data Warehouse.

  • Als je geen door de gebruiker gedefinieerde functie met schemabinding maakt, kunnen wijzigingen aan onderliggende objecten de definitie van de functie beïnvloeden en onverwachte resultaten veroorzaken wanneer je de functie aanroept. Wanneer je aangeeft WITH SCHEMABINDING wanneer je de functie aanmaakt, zorg je ervoor dat latere wijzigingen aan onderliggende objecten het gedrag van de functie niet kunnen veranderen of breken.

  • Implementeer waar mogelijk je logica als een T-SQL user-defined function (UDF). Gebruik een externe UDF alleen voor functionaliteit die niet in de T-SQL-taal kan worden geïmplementeerd. Deze aanpak helpt de overhead voor externe uitvoering te minimaliseren en levert over het algemeen betere queryprestaties.

Interoperabiliteit

Door de gebruiker gedefinieerde functies inlinetabelwaarden

Een inline tabel-waarde functie accepteert slechts één enkele SELECT stelling.

Scalaire door de gebruiker gedefinieerde functies

  • Wanneer een scalair UDF niet-inlineerbaar is, CREATE zou de OR-instructie ALTER de DDL-instructie afwijzen met een aangepaste foutmelding.

  • Niet-inlineable functie kan worden gecreëerd door de functieoptie te specificeren INLINE=AUTO . Niet-inlineable functie kan niet worden gebruikt in een SELECT ... FROM query op een gebruikerstabel. INLINE=AUTO Maakt functie niet inlinebaar.

  • De volgende instructies zijn geldig in een scalaire functie:

    • Opdracht verklaringen.
    • Control-of-flow-statements behalve TRY...CATCH en GOTO statements.
    • DECLARE statements die lokale datavariabelen definiëren.
    • Oproepen naar ingebouwde functies.
    • Verwijzingen naar tabellen/views/iTVFs/andere scalar UDF's.
  • DML-statements zijn niet toegestaan in scalaire, door de gebruiker gedefinieerde functies.

Metagegevens

Deze sectie bevat de systeemcatalogusweergaven die u kunt gebruiken om metagegevens over door de gebruiker gedefinieerde functies te retourneren.

  • sys.sql_modules: Toont de definitie van Transact-SQL door gebruikers gedefinieerde functies, evenals informatie over inlinebaarheid. Voorbeeld:

      SELECT 
          SCHEMA_NAME(o.schema_id) AS SchemaName,
          o.name AS FunctionName,
          m.definition AS FunctionDefinition,
          m.is_inlineable AS Inlineable,
          m.inline_eligibility_mask AS InlineEligibilityMask
      FROM sys.objects o
      JOIN sys.sql_modules m ON o.object_id = m.object_id
      WHERE o.type = 'FN';
    
  • sys.parameters: Toont informatie over de parameters die zijn gedefinieerd in door gebruikers gedefinieerde functies. Het volgende voorbeeld gebruikt de sys.parameters catalogusweergave om functiehandtekeningen weer te geven, inclusief retourtypes en parametergegevenstypen:

     WITH metadata AS
     (
         SELECT  o.schema_id, o.object_id, o.name, p.parameter_id, p.name AS parameter_name, t.name AS type_name
         FROM sys.objects o JOIN sys.parameters p ON p.object_id = o.object_id JOIN sys.types t ON t.user_type_id = p.user_type_id
         WHERE o.type IN ('FN', 'XF')
     ),
     signature AS (
     SELECT SCHEMA_NAME(schema_id) as schema_name, name, ANY_VALUE(CASE WHEN parameter_id = 0 THEN type_name END) return_type, CONCAT(
         SCHEMA_NAME(schema_id), '.', name, '(',STRING_AGG(CASE WHEN parameter_id > 0 THEN CONCAT(parameter_name, ' ', type_name) END, ', ' ) WITHIN GROUP (ORDER BY parameter_id),
         ') -> ',  MAX(CASE WHEN parameter_id = 0 THEN type_name END)
     ) AS signature
     FROM metadata GROUP BY schema_id, object_id, name
     )
     SELECT *
     FROM signature;
    
  • sys.sql_expression_dependencies: Toont de onderliggende objecten waarnaar een functie verwijst.

Machtigingen

Leden van de rollen Fabric-werkruimteBeheerder, Lid en Inzender kunnen functies maken.

Inlining van scalair UDF

Microsoft Fabric Data Warehouse inlines automatisch geschikte scalaire UDF's tijdens het compileren van query.

Expression Block inlining compileert de instructies van de functie, inclusief takken en lussen, tot een scalaire expressie die wordt uitgevoerd als onderdeel van de aanroepende query. Scalaire UDF-inlining transformeert het functielichaam in expressies of subqueries als onderdeel van de aanroepende query.

Een functie kan aan de eisen voor beide technieken voldoen, maar de engine selecteert er één op basis van haar definitie. Verschillende functies binnen dezelfde query kunnen verschillende technieken gebruiken.

Naarmate Microsoft Fabric Data Warehouse zijn inline-mogelijkheden uitbreidt, krijgen bestaande functies deze automatisch.

Ondersteunde functieconstructies

De volgende tabel vergelijkt ondersteunde functie-objectenconstructies.

Function-lichaamconstructie Expression Block inlining Scalaire UDF-inlining
Scalaire expressies, lokale variabelen, en IF/ELSE Supported Supported
WHILE lussen, waaronder BREAK en CONTINUE Supported Niet ondersteund
Meerdere RETURN instructies Supported Niet ondersteund
NEWID() Supported Niet ondersteund
Tijdsafhankelijke functies (bijvoorbeeld en GETDATE()SYSDATETIME()) Supported Niet ondersteund
Tabel-, weergave- of inline TVF-toegang Niet ondersteund Supported
Oproepen naar scalaire UDF's* Niet ondersteund Supported

* Door de gebruiker gedefinieerde functies kunnen worden genestet: één UDF kan een andere aanroepen. Het nestniveau neemt toe wanneer een aangeroepen functie start en neemt af wanneer deze eindigt. Microsoft Fabric Data Warehouse ondersteunt tot vier geneste niveaus wanneer een UDF-body verwijst naar een tabel, view of inline TVF, of tot 32 niveaus anderszins. Het overschrijden van de nestlimiet zorgt ervoor dat de aanroepende functieketen faalt.

Ingebouwde functies

Ondersteuning voor ingebouwde functies, inclusief systeemfuncties, verschilt tussen de twee inlinetechnieken.

Bijvoorbeeld het aanmaken van een UDF die zonder INLINE = AUTO fout 19841 retourneert gebruikt@@ROWCOUNT. De foutmelding bevat de volgende tekst:

Function <function_name> cannot be inlined.
Expression block: Unsupported built-in @@ROWCOUNT found.
Scalar UDF inlining: Unsupported built-in @@ROWCOUNT found.

Voor een specifieke functiedefinitie, zie Controleer of een scalaire UDF inline kan worden.

Ondersteunde aanroepquery's

De volgende tabel vergelijkt ondersteuning voor aanroepen voor inlinebare scalaire UDF's.

Query-functie Expression Block inlining Scalaire UDF-inlining
UDF roept de SELECT lijst op, WHERE, of JOIN ... ON Supported Supported
Directe UDF-oproepen in GROUP BY of HAVING Supported Niet ondersteund
Directe UDF-oproepen ORDER BY Supported Niet ondersteund
CTE's in de aanroepende query Supported Niet ondersteund
ROLLUP, of CUBE, of GROUPING SETS Niet ondersteund Niet ondersteund

Als een UDF-aanroep afhankelijk is van scalaire UDF-inline, moet de gehele query voldoen aan de calling-query-eisen van die techniek.

Met scalaire UDF-inlining projecteer je het UDF-resultaat in de SELECT lijst en orden je het kolomalias of ordinaal in plaats van de UDF direct in te roepen ORDER BY. Voor voorbeelden van query-herschrijf, zie Pas een aanroepende query aan voor Scalar UDF-inline. Voor andere uitvoeringslimieten, zie Beperkingen.

Controleren of een scalaire UDF inline kan zijn

De sys.sql_modules catalogusweergave beschrijft de geschiktheid op basis van de functiedefinitie. is_inlineable is 1 voor een inlinebare definitie en 0 anderszins. inline_eligibility_mask Identificeert de in aanmerking komende technieken:

inline_eligibility_mask Definitie van geschiktheid
0 Geen van beide technieken
1 Scalaire UDF-inlining
2 Expression Block inlining
3 Beide technieken

Deze waarden beschrijven de definitie van de functie, niet het uitvoeringsplan van een specifieke query.

Gebruik de volgende voorbeeldquery om te controleren of een scalaire UDF inlinebaar is:

SELECT
    SCHEMA_NAME(b.schema_id) AS function_schema_name,
    b.name AS function_name,
    b.type_desc AS function_type,
    a.is_inlineable,
    a.inline_eligibility_mask
FROM sys.sql_modules AS a
     INNER JOIN sys.objects AS b
         ON a.object_id = b.object_id
WHERE b.type IN ('FN');

Beperkingen

  • Het aanroepen van een scalaire UDF in een niet-ondersteunde querycontext kan de fout teruggeven Scalar UDF execution is currently unavailable in this context.
  • Een query die afhankelijk is van Scalar UDF-inline kan mislukken als deze meer dan 10 UDF-aanroepen bevat.

Voorbeelden

Eén. Een functie met tabelwaarde inline maken

Het volgende voorbeeld creëert een inline tabel-waarde functie die belangrijke informatie over modules teruggeeft, gefilterd op de objectType parameter. Het bevat een standaardwaarde om alle modules terug te geven wanneer je de functie met de DEFAULT parameter aanroept. Dit voorbeeld gebruikt enkele van de systeemcatalogusweergaven die in Metadata worden genoemd.

CREATE FUNCTION dbo.ModulesByType (@objectType CHAR(2) = '%%')
RETURNS TABLE
AS
RETURN (
        SELECT sm.object_id AS 'Object Id',
            o.create_date AS 'Date Created',
            OBJECT_NAME(sm.object_id) AS 'Name',
            o.type AS 'Type',
            o.type_desc AS 'Type Description',
            sm.DEFINITION AS 'Module Description',
            sm.is_inlineable AS 'Inlineable'
        FROM sys.sql_modules AS sm
        INNER JOIN sys.objects AS o ON sm.object_id = o.object_id
        WHERE o.type LIKE '%' + @objectType + '%'
        );
GO

Roep de functie aan om alle inline tabel-waarde functies terug te geven (IF):

SELECT * FROM dbo.ModulesByType('IF'); -- SQL_INLINE_TABLE_VALUED_FUNCTION

Of zoek alle scalaire functies (FN):

SELECT * FROM dbo.ModulesByType('FN'); -- SQL_SCALAR_FUNCTION

B. Resultaten van een inline tabelwaardefunctie combineren

Dit eenvoudige voorbeeld gebruikt de eerder gemaakte inline TVF om te laten zien hoe je de resultaten kunt combineren met andere tabellen door gebruik te maken van CROSS APPLY. Hier selecteer je alle kolommen uit beide sys.objects en de resultaten van ModulesByType voor alle rijen die overeenkomen in de type kolom. Voor meer informatie over het gebruik van APPLY, zie FROM-clausule plus JOIN, APPLY, PIVOT (Transact-SQL).

SELECT * 
FROM sys.objects AS o
CROSS APPLY dbo.ModulesByType(o.type);
GO

C. Een scalaire UDF-functie maken

In het volgende voorbeeld wordt een inlineerbare scalaire UDF gemaakt waarmee een invoertekst wordt gemaskeerd.

CREATE OR ALTER FUNCTION [dbo].[cleanInput] (@InputString VARCHAR(100))
    RETURNS VARCHAR(50)
    AS
    BEGIN
        DECLARE @Result VARCHAR(50);
        DECLARE @CleanedInput VARCHAR(50);

        -- Trim whitespace
        SET @CleanedInput = LTRIM(RTRIM(@InputString));

        -- Handle empty or null input
        IF @CleanedInput = '' OR @CleanedInput IS NULL
        BEGIN
            SET @Result = '';
        END
        ELSE IF LEN(@CleanedInput) <= 2
        BEGIN
            -- If string length is 1 or 2, just return the cleaned string
            SET @Result = @CleanedInput;
        END
        ELSE
        BEGIN
            -- Construct the masked string
            SET @Result = 
                LEFT(@CleanedInput, 1) +
                REPLICATE('*', LEN(@CleanedInput) - 2) +
                RIGHT(@CleanedInput, 1);
        END

        RETURN @Result
    END

U kunt de functie als volgt aanroepen:

DECLARE @input varchar(100) = '123456789';

SELECT dbo.cleanInput (@input) AS function_output;

Meer voorbeelden van hoe u scalaire UDF's in Fabric Data Warehouse kunt gebruiken:

In een SELECT instructie:

SELECT TOP 10 
t.id, t.name, 
dbo.cleanInput (t.name) AS function_output
FROM dbo.MyTable AS t;

In een WHERE component:

 SELECT t.id, t.name, dbo.cleanInput(t.name) AS function_output
FROM dbo.MyTable AS t
WHERE dbo.cleanInput(t.name) = '1*******9';

In een JOIN component:

SELECT t1.id, t1.name, 
     dbo.cleanInput (t1.name) AS function_output, 
     dbo.cleanInput (t2.name) AS function_output_2
FROM dbo.MyTable1 AS t1
    INNER JOIN dbo.MyTable2 AS t2 
        ON dbo.cleanInput(t1.name)=dbo.cleanInput(t2.name);

In een ORDER BY component:

SELECT  t.id, t.name, dbo.cleanInput (t.name) AS function_output
FROM dbo.MyTable AS t
ORDER BY function_output;

In Warehouse kunt u ook scalaire UDF's aanroepen vanuit datamanipulatiestatements zoals INSERT, UPDATE, of DELETE. Deze voorbeelden van data-aanpassing zijn niet van toepassing op het SQL Analytics-eindpunt.

SELECT t.id, t.name, dbo.cleanInput (t.name) AS function_output 
INTO dbo.MyTable_new
FROM dbo.MyTable AS t;

UPDATE t
SET t.mycolumn_new = dbo.cleanInput (t.name)
FROM dbo.MyTable AS t;

DELETE t
FROM dbo.MyTable AS t
WHERE dbo.cleanInput(t.name) = '1*******9';

D. Maak een externe functie aan

Het volgende voorbeeld creëert een externe functie die in het dbo schema wordt genoemd geohash en verwijst naar de geohash Fabric User Data Function in de spatial_functions functieset. Omdat de parameterdefinities en het retourtype worden afgeleid uit de verwezen Fabric User Data Function, hoeven ze niet expliciet in de CREATE FUNCTION instructie te worden gespecificeerd.

CREATE FUNCTION dbo.geohash
AS EXTERNAL FUNCTION spatial_functions.geohash;

Eenmaal aangemaakt, kan de externe functie worden gebruikt in T-SQL-queries net als elke andere door de gebruiker gedefinieerde of ingebouwde functie. Het kan worden verwezen waar functieaanroepen worden ondersteund, waardoor Fabric User Data Functions naadloos geïntegreerd kan worden in T-SQL-code.

E. Maak een scalaire UDF voor standalone uitvoering

De volgende tabelcreatie en gegevenslaadinstallatie vereist Warehouse. Gebruik op een SQL analytics-eindpunt een bestaande bron-ondersteunde tabel met de vereiste kolommen en gegevens.

CREATE TABLE dbo.Customer
(
    CustomerID INT NOT NULL,
    CustomerName VARCHAR(100) NOT NULL,
    CustomerType VARCHAR(20) NOT NULL,
    MemberSince DATE NOT NULL,
    YearToDateRevenue DECIMAL(18, 2) NOT NULL
);
GO

INSERT INTO dbo.Customer
(
    CustomerID,
    CustomerName,
    CustomerType,
    MemberSince,
    YearToDateRevenue
)
VALUES
    (1, 'Customer 1', 'Regular', '2020-01-15', 12000.00),
    (2, 'Customer 2', 'Premium', '2022-06-01', 24000.00),
    (3, 'Customer 3', 'VIP',     '2024-03-10', 36000.00);
GO

De onderstaande functie combineert tabeltoegang met GETUTCDATE(). Tabeltoegang voorkomt inlining van Expression Block en GETUTCDATE() voorkomt inlining van Scalar UDF.

Zonder WITH INLINE = AUTO, wordt de definitie bij creatie of verandering verworpen. De foutmelding meldt dat de functie niet kan worden ingelijnd en geeft de afwijzingsredenen voor beide technieken. Specificeer WITH INLINE = AUTO om het aanmaken toe te staan:

CREATE OR ALTER FUNCTION dbo.GetCustomerTenureDays (@CustomerID INT)
RETURNS INT
WITH INLINE = AUTO
AS
BEGIN
    DECLARE @MemberSince DATE;

    SELECT @MemberSince = MemberSince
    FROM dbo.Customer
    WHERE CustomerID = @CustomerID;

    RETURN DATEDIFF(DAY, @MemberSince, GETUTCDATE());
END;
GO

De functie wordt aangemaakt maar blijft niet-inlinebaar: is_inlineable en inline_eligibility_mask zijn beide 0. Een zelfstandige call slaagt:

DECLARE @udf_output int;
SET @udf_output = dbo.GetCustomerTenureDays(1);
SELECT @udf_output as 'udf_output';

Het aanroepen van de functie in een query over een gebruikerstabel wordt niet ondersteund:

SELECT
    CustomerID,
    dbo.GetCustomerTenureDays(CustomerID) AS TenureDays
FROM dbo.Customer;

Deze query geeft terug Scalar UDF execution is currently unavailable in this context.

Aanbeveling

Om deze logica te gebruiken in een query over gebruikerstabellen, evalueer je de huidige tijdwaarde in de caller en geef je deze door als parameter. Voor de herschreven functie en het aanroepen van de query, zie Een tijdsafhankelijke waarde doorgeven aan een data-access UDF.

Geldt op:Azure Synapse Analytics

Maakt een user-defined function (UDF) aan in Azure Synapse Analytics. Een door de gebruiker gedefinieerde functie is een Transact-SQL routine die parameters accepteert, een actie uitvoert, zoals een complexe berekening, en het resultaat van die actie als een waarde retourneert. Door de gebruiker gedefinieerde functies met tabelwaarden (TVF's) retourneren een tabelgegevenstype.

Aanbeveling

Voor syntaxis in Fabric Data Warehouse, zie de versie van CREATE FUNCTION for Fabric Data Warehouse.

  • In Azure Synapse Analytics CREATE FUNCTION kan een tabel worden geretourneerd met behulp van de syntaxis voor inline-tabelwaardefuncties (preview) of kan er één waarde worden geretourneerd met behulp van de syntaxis voor scalaire functies.

  • In serverloze SQL-pools in Azure Synapse Analytics kunt u inline-tabelwaardefuncties maken, CREATE FUNCTION maar niet scalaire functies.

    Gebruik deze verklaring om een herbruikbare routine te creëren die je op deze manieren kunt gebruiken:

  • In Transact-SQL verklaringen zoals SELECT

  • In toepassingen die de functie aanroepen

  • In de definitie van een andere door de gebruiker gedefinieerde functie

  • Een CHECK-beperking voor een kolom definiëren

  • Een opgeslagen procedure vervangen

  • Een inlinefunctie gebruiken als filterpredicaat voor een beveiligingsbeleid

Transact-SQL syntaxisconventies

Syntaxis

Syntaxis van scalaire functie

-- Transact-SQL Scalar Function Syntax (in dedicated pools in Azure Synapse Analytics)
-- Not available in the serverless SQL pools in Azure Synapse Analytics

CREATE FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] parameter_data_type
    [ = default ] }
    [ ,...n ]
  ]
)
RETURNS return_data_type
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    BEGIN
        function_body
        RETURN scalar_expression
    END
[ ; ]

<function_option>::=
{
    [ SCHEMABINDING ]
  | [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
}

Syntaxis van inline-tabelwaardefunctie

-- Transact-SQL Inline Table-Valued Function Syntax
-- Preview in dedicated SQL pools in Azure Synapse Analytics
-- Available in the serverless SQL pools in Azure Synapse Analytics
CREATE FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] parameter_data_type
    [ = default ] }
    [ ,...n ]
  ]
)
RETURNS TABLE
    [ WITH SCHEMABINDING ]
    [ AS ]
    RETURN [ ( ] select_stmt [ ) ]
[ ; ]

Argumenten

schema_name

De naam van het schema waartoe de door de gebruiker gedefinieerde functie behoort.

function_name

De naam van de door de gebruiker gedefinieerde functie. Functienamen moeten voldoen aan de regels voor identificaties en uniek zijn binnen de database en het schema daarvan.

Opmerking

Je moet haakjes achter de functienaam zetten, zelfs als je geen parameter specificeert.

@ parameter_name

Een parameter in de door de gebruiker gedefinieerde functie. Je kunt één of meer parameters declareren.

Een functie kan tot 2.100 parameters hebben. Wanneer een gebruiker of applicatie een functie aanroept, moet je de waarde voor elke gedeclareerde parameter opgeven, tenzij je een standaardwaarde voor die parameter definieert.

Geef een parameternaam op met behulp van een at-teken (@) als het eerste teken. De parameternaam moet voldoen aan de regels voor identifiers. Parameters zijn lokaal aan de functie; Je kunt dezelfde parameternamen in andere functies gebruiken. Parameters kunnen alleen constanten vervangen; ze kunnen niet worden gebruikt in plaats van tabelnamen, kolomnamen of de namen van andere databaseobjecten.

Opmerking

ANSI_WARNINGS wordt niet gerespecteerd 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 je bijvoorbeeld een variabele definieert als char(3) en deze vervolgens op een waarde groter dan drie tekens zet, wordt de data afgekapt tot de gedefinieerde grootte en slaagt de INSERT or-instructie UPDATE .

parameter_data_type

Het gegevenstype parameter. Voor Transact-SQL functies zijn alle scalaire gegevenstypen die worden ondersteund in Azure Synapse Analytics toegestaan. Het tijdstempel (rijversie) datatype wordt niet ondersteund.

[ = standaard ]

Een standaardwaarde voor de parameter. Als je een standaardwaarde definieert, kun je de functie uitvoeren zonder een waarde voor die parameter op te geven.

Wanneer een parameter van de functie een standaardwaarde heeft, moet je het trefwoord DEFAULT opgeven bij het aanroepen van de functie om de standaardwaarde op te halen. Dit gedrag verschilt van het gebruik van parameters met standaardwaarden in opgeslagen procedures, waarbij het weglaten van de parameter ook de standaardwaarde impliceert.

return_data_type

De retourwaarde van een scalaire, door de gebruiker gedefinieerde functie. Voor Transact-SQL functies zijn alle scalaire gegevenstypen die worden ondersteund in Azure Synapse Analytics toegestaan. Het typerowversie-timestamp/ data wordt niet ondersteund. De cursor- en tabel niet-scalaire types zijn niet toegestaan.

function_body

Reeks Transact-SQL instructies. De function_body kan geen SELECT statement bevatten en geen databasegegevens verwijzen. De function_body kan geen tabellen of weergaven refereren. Het functielichaam kan andere deterministische functies aanroepen, maar kan niet-deterministische functies niet aanroepen.

In scalaire functies is function_body een reeks Transact-SQL instructies die samen tot een scalaire waarde leiden.

scalar_expression

Hiermee geeft u de scalaire waarde op die de scalaire functie retourneert.

select_stmt

De enkele SELECT instructie waarmee de retourwaarde van een inline-tabelwaardefunctie wordt gedefinieerd. Voor een inline tabel-waarde functie is er geen functielichaam; De tabel is de resultaatverzameling van één enkele SELECT uitspraak.

TABLE

Hiermee geeft u aan dat de retourwaarde van de tabelwaardige functie (TVF) een tabel is. Je kunt alleen constanten en @local_variables doorgeven aan TVF's.

In inline TVF's (preview) definieer je de TABLE retourwaarde via één enkele SELECT instructie. Inline-functies hebben geen gekoppelde retourvariabelen.

<function_option>

Geeft aan dat de functie een of meer van de volgende opties heeft.

SCHEMABINDING

Hiermee geeft u aan dat de functie is gebonden aan de databaseobjecten waarnaar wordt verwezen. Wanneer je specificeert SCHEMABINDING, kun je de onderliggende objecten (zoals bijvoorbeeld een weergave of een tabel) niet aanpassen op een manier die de functiedefinitie beïnvloedt. Je moet eerst de functiedefinitie aanpassen of verwijderen om afhankelijkheden van het object dat je wilt aanpassen te verwijderen.

De binding van de functie aan de objecten waarnaar wordt verwezen, wordt alleen verwijderd wanneer een van de volgende acties plaatsvindt:

  • Je laat de functie vallen.

  • Je doet ALTER de functie-statement en verwijdert de SCHEMABINDING optie.

Je kunt een functie alleen schema-binden als de volgende voorwaarden waar zijn:

  • Alle door de gebruiker gedefinieerde functies waar de functie naar verwijst, zijn ook schema-gebonden.

  • De functiereferenties gebruiken namen die één of twee delen bevatten.

  • Binnen de body van UDF's kun je alleen naar ingebouwde functies en andere UDF's in dezelfde database verwijzen.

  • De gebruiker die de CREATE FUNCTION instructie uitvoert heeft REFERENCES-rechten op de databaseobjecten waar de functie naar verwijst.

Als u SCHEMABINDING wilt verwijderen, gebruikt u ALTER.

RETOURNEERT NULL OP NULL-INVOER | AANGEROEPEN OP NULL-INVOER

Hiermee geeft u het OnNULLCall kenmerk van een scalaire-waardefunctie. Als je dit attribuut niet specificeert, CALLED ON NULL INPUT wordt standaard impliciet, en voert het functielichaam uit, zelfs als NULL het als argument wordt doorgegeven.

Beste praktijken

Als je geen door de gebruiker gedefinieerde functie maakt met de SCHEMABINDING-clausule, kunnen wijzigingen in onderliggende objecten de definitie van de functie beïnvloeden en onverwachte resultaten veroorzaken wanneer je deze aanroept. Specificeer de WITH SCHEMABINDING clausule wanneer je de functie maakt. Deze clausule zorgt ervoor dat je de objecten die in de functiedefinitie worden genoemd niet kunt wijzigen, tenzij je ook de functie aanpast.

Interoperabiliteit

De volgende instructies zijn geldig in een scalaire functie:

  • Opdracht verklaringen.

  • Control-of-Flow statements, behalve TRY... CATCH-verklaringen.

  • DECLARE-statements die lokale datavariabelen definiëren.

In een inline tabel-gewaardeerde functie (preview) kun je slechts één select-instructie gebruiken.

Beperkingen

Je kunt geen door de gebruiker gedefinieerde functies gebruiken om acties uit te voeren die de status van de database wijzigen.

Je kunt door de gebruiker gedefinieerde functies nesten. Een door de gebruiker gedefinieerde functie kan een andere aanroepen. Het genestingsniveau neemt toe wanneer de aangeroepen functie begint met uitvoeren, en neemt af wanneer de aangeroepen functie is uitgevoerd. Als je de maximale niveaus van genesing overschrijdt, faalt de hele aanroepende functieketen.

Je kunt geen objecten, inclusief functies, aanmaken in de master database van je serverless SQL-pool in Azure Synapse Analytics.

Metagegevens

Deze sectie bevat de systeemcatalogusweergaven die u kunt gebruiken om metagegevens over door de gebruiker gedefinieerde functies te retourneren.

  • sys.sql_modules: Toont de definitie van Transact-SQL door gebruikers gedefinieerde functies. Voorbeeld:

    SELECT definition, type   
    FROM sys.sql_modules AS m  
    JOIN sys.objects AS o   
        ON m.object_id = o.object_id   
        AND type = ('FN');
    
  • sys.parameters: Toont informatie over de parameters die zijn gedefinieerd in door gebruikers gedefinieerde functies.

  • sys.sql_expression_dependencies: Toont de onderliggende objecten waarnaar een functie verwijst.

Machtigingen

Vereist CREATE FUNCTION toestemming in de database en ALTER-toestemming voor het schema waarin de functie wordt gemaakt.

Voorbeelden

Eén. Een door de gebruiker gedefinieerde scalaire functie gebruiken om een gegevenstype te wijzigen

Deze eenvoudige functie neemt een int-datatype als invoer en geeft een decimaal(10,2) datatype terug als uitvoer.

CREATE FUNCTION dbo.ConvertInput (@MyValueIn int)  
RETURNS decimal(10,2)  
AS  
BEGIN
    DECLARE @MyValueOut int;  
    SET @MyValueOut= CAST( @MyValueIn AS decimal(10,2));  
    RETURN(@MyValueOut);  
END;  
GO  

SELECT dbo.ConvertInput(15) AS 'ConvertedValue';  

Opmerking

Scalaire functies zijn niet beschikbaar in serverloze SQL-pools.

B. Een functie met tabelwaarde inline maken

Het volgende voorbeeld creëert een inline tabel-waarde functie die belangrijke informatie over modules teruggeeft, gefilterd op de objectType parameter. Het bevat een standaardwaarde om alle modules terug te geven wanneer je de functie met de DEFAULT parameter aanroept. Dit voorbeeld gebruikt enkele van de systeemcatalogusweergaven die in Metadata worden genoemd.

CREATE FUNCTION dbo.ModulesByType(@objectType CHAR(2) = '%%')
RETURNS TABLE
AS
RETURN
(
    SELECT 
        sm.object_id AS 'Object Id',
        o.create_date AS 'Date Created',
        OBJECT_NAME(sm.object_id) AS 'Name',
        o.type AS 'Type',
        o.type_desc AS 'Type Description', 
        sm.definition AS 'Module Description'
    FROM sys.sql_modules AS sm  
    JOIN sys.objects AS o ON sm.object_id = o.object_id
    WHERE o.type like '%' + @objectType + '%'
);
GO

Je kunt de functie aanroepen om alle view (V) objecten terug te geven met:

select * from dbo.ModulesByType('V');

Opmerking

Inline tabelwaardefuncties zijn beschikbaar in serverless SQL-pools, maar in preview in de speciale SQL-pools.

C. Resultaten van een inline tabelwaardefunctie combineren

Dit eenvoudige voorbeeld gebruikt de eerder gemaakte inline TVF om te laten zien hoe je de resultaten kunt combineren met andere tabellen door gebruik te maken van CROSS APPLY. In dit voorbeeld selecteer je alle kolommen uit beide sys.objects en de resultaten van ModulesByType voor alle rijen die overeenkomen in de type kolom. Voor meer informatie over het gebruik van APPLY, zie FROM-clausule plus JOIN, APPLY, PIVOT (Transact-SQL).

SELECT * 
FROM sys.objects o
CROSS APPLY dbo.ModulesByType(o.type);
GO

Opmerking

Inline tabelwaardefuncties zijn beschikbaar in serverless SQL-pools, maar in preview in de speciale SQL-pools.

Volgende stap