SKAPA FUNKTION (Transact-SQL)

2025-06-21

gäller för:SQL Server Azure SQL Database Azure SQL Managed Instance

Skapar en användardefinierad funktion (UDF), som är en Transact-SQL eller CLR-rutin (Common Language Runtime). En användardefinierad funktion accepterar parametrar, utför en åtgärd, till exempel en komplex beräkning, och returnerar resultatet av åtgärden som ett värde. Det returnerade värdet kan antingen vara ett skalärt (enkelt) värde eller en tabell. Använd den här instruktionen för att skapa en återanvändbar rutin som kan användas på följande sätt:

I Transact-SQL uttalanden som SELECT
I program som anropar funktionen
I definitionen av en annan användardefinierad funktion
Så här parametriserar du en vy eller förbättrar funktionerna i en indexerad vy
Så här definierar du en kolumn i en tabell
Så här definierar du en CHECK begränsning för en kolumn
Så här ersätter du en lagrad procedur
Använda en infogad funktion som filterpredikat för en säkerhetsprincip

Integreringen av .NET Framework CLR i SQL Server beskrivs i den här artikeln. CLR-integrering gäller inte för Azure SQL Database.

Information om Azure Synapse Analytics eller Microsoft Fabric finns i CREATE FUNCTION (Azure Synapse Analytics och Microsoft Fabric).

Transact-SQL syntaxkonventioner

Syntax

Syntax för Transact-SQL skalära funktioner.

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

Syntax för Transact-SQL infogade tabellvärdesfunktioner.

CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ NULL ]
    [ = default ] [ READONLY ] }
    [ , ...n ]
  ]
)
RETURNS TABLE
    [ WITH <function_option> [ , ...n ] ]
    [ AS ]
    RETURN [ ( ] select_stmt [ ) ]
[ ; ]

Syntax för Transact-SQL tabellvärdesfunktioner med flera satser.

CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ NULL ]
    [ = default ] [ READONLY ] }
    [ , ...n ]
  ]
)
RETURNS @return_variable TABLE <table_type_definition>
    [ WITH <function_option> [ , ...n ] ]
    [ AS ]
    BEGIN
        function_body
        RETURN
    END
[ ; ]

Syntax för Transact-SQL funktionssatser.

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

<table_type_definition> ::=
( { <column_definition> <column_constraint>
  | <computed_column_definition> }
    [ <table_constraint> ] [ , ...n ]
)
<column_definition> ::=
{
    { column_name data_type }
    [ [ DEFAULT constant_expression ]
      [ COLLATE collation_name ] | [ ROWGUIDCOL ]
    ]
    | [ IDENTITY [ (seed , increment ) ] ]
    [ <column_constraint> [ ...n ] ]
}

<column_constraint> ::=
{
    [ NULL | NOT NULL ]
    { PRIMARY KEY | UNIQUE }
      [ CLUSTERED | NONCLUSTERED ]
      [ WITH FILLFACTOR = fillfactor
        | WITH ( <index_option> [ , ...n ] )
      [ ON { filegroup | "default" } ] ]
  | [ CHECK ( logical_expression ) ] [ , ...n ]
}

<computed_column_definition> ::=
column_name AS computed_column_expression

<table_constraint> ::=
{
    { PRIMARY KEY | UNIQUE }
      [ CLUSTERED | NONCLUSTERED ]
      ( column_name [ ASC | DESC ] [ , ...n ]
        [ WITH FILLFACTOR = fillfactor
        | WITH ( <index_option> [ , ...n ] )
  | [ CHECK ( logical_expression ) ] [ , ...n ]
}

<index_option> ::=
{
    PAD_INDEX = { ON | OFF }
  | FILLFACTOR = fillfactor
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
}

Syntax för skalära CLR-funktioner.

CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ NULL ]
    [ = default ] }
    [ , ...n ]
)
RETURNS { return_data_type }
    [ WITH <clr_function_option> [ , ...n ] ]
    [ AS ] EXTERNAL NAME <method_specifier>
[ ; ]

Syntax för CLR-tabellvärdesfunktioner.

CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ NULL ]
    [ = default ] }
    [ , ...n ]
)
RETURNS TABLE <clr_table_type_definition>
    [ WITH <clr_function_option> [ , ...n ] ]
    [ ORDER ( <order_clause> ) ]
    [ AS ] EXTERNAL NAME <method_specifier>
[ ; ]

Syntax för CLR-funktionssatser.

<order_clause> ::=
{
   <column_name_in_clr_table_type_definition>
   [ ASC | DESC ]
} [ , ...n ]

<method_specifier> ::=
    assembly_name.class_name.method_name

<clr_function_option> ::=
{
    [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
  | [ EXECUTE_AS_Clause ]
}

<clr_table_type_definition> ::=
( { column_name data_type } [ , ...n ] )

Minnesintern OLTP-syntax för internt kompilerade, skalära användardefinierade funktioner.

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

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

Argumentpunkter

ELLER ÄNDRA

Gäller för: SQL Server 2016 (13.x) SP 1 och senare versioner samt Azure SQL Database.

Ändrar funktionen villkorligt endast om den redan finns.

Valfri OR ALTER syntax är tillgänglig för CLR, från och med SQL Server 2016 (13.x) SP 1 CU 1.

schema_name

Namnet på det schema som den användardefinierade funktionen tillhör.

function_name

Namnet på den användardefinierade funktionen. Funktionsnamn måste följa reglerna för identifierare och måste vara unika i databasen och i förhållande till dess schema.

Parenteser krävs efter funktionsnamnet, även om en parameter inte har angetts.

@parameter_name

En parameter i den användardefinierade funktionen. En eller flera parametrar kan deklareras.

En funktion kan ha högst 2 100 parametrar. Värdet för varje deklarerad parameter måste anges av användaren när funktionen körs, såvida inte ett standardvärde för parametern har definierats.

Ange ett parameternamn med hjälp av ett snabel-a-tecken (@) som första tecken. Parameternamnet måste överensstämma med reglerna för identifierare. Parametrarna är lokala för funktionen. Samma parameternamn kan användas i andra funktioner. Parametrar kan endast ersätta konstanter; De kan inte användas i stället för tabellnamn, kolumnnamn eller namnen på andra databasobjekt.

ANSI_WARNINGS respekteras inte när du skickar parametrar i en lagrad procedur, en användardefinierad funktion eller när du deklarerar och anger variabler i en batch-instruktion. Om en variabel definieras som tecken(3)och sedan tilldelas ett värde som är större än tre tecken, förkortas data till den definierade storleken och INSERT- eller UPDATE-instruktionen utförs framgångsrikt.

[ type_schema_name. ] parameter_data_type

Parameterns datatyp och eventuellt det schema som den tillhör. För Transact-SQL funktioner tillåts alla datatyper, inklusive användardefinierade CLR-typer och användardefinierade tabelltyper, förutom tidsstämpeldatatypen . För CLR-funktioner tillåts alla datatyper, inklusive användardefinierade CLR-typer, förutom text, ntext, image, användardefinierade tabelltyper och tidsstämpeldatatyper . De icke-skalära typerna, markören och tabellen, kan inte anges som en parameterdatatyp i Transact-SQL- eller CLR-funktionerna.

Om type_schema_name inte anges söker databasmotorn efter scalar_parameter_data_type i följande ordning:

Schemat som innehåller namnen på SQL Server systemdatatyper.
Standardschemat för den aktuella användaren i den aktuella databasen.
dbo-schemat i den aktuella databasen.

[ = standard ]

Ett standardvärde för parametern. Om ett standardvärde definieras kan funktionen köras utan att ange ett värde för den parametern.

Standardparametervärden kan anges för CLR-funktioner, förutom datatyperna varchar(max) och varbinary(max).

När en parameter för funktionen har ett standardvärde måste nyckelordet DEFAULT anges när funktionen anropas för att hämta standardvärdet. Det här beteendet skiljer sig från att använda parametrar med standardvärden i lagrade procedurer där utelämnande av parametern också innebär standardvärdet. Nyckelordet DEFAULT krävs dock inte när du anropar en skalär funktion med hjälp av EXECUTE -instruktionen.

SKRIVSKYDDAD

Anger att parametern inte kan uppdateras eller ändras i definitionen av funktionen. READONLY krävs för användardefinierade tabelltypsparametrar (TVP:er) och kan inte användas för någon annan parametertyp.

return_data_type

Returvärdet för en skalär användardefinierad funktion. För Transact-SQL funktioner tillåts alla datatyper, inklusive användardefinierade CLR-typer, förutom tidsstämpeldatatypen . För CLR-funktioner tillåts alla datatyper, inklusive användardefinierade CLR-typer, förutom datatyperna text, ntext, image och timestamp . De icke-skalära typerna, markören och tabellen, kan inte anges som en returdatatyp i antingen Transact-SQL- eller CLR-funktioner.

function_body

Anger att en serie Transact-SQL instruktioner, som tillsammans inte ger någon sidoeffekt, till exempel att ändra en tabell, definierar funktionens värde. function_body används endast i skalära funktioner och MSTVF:er (Multi-Statement Table-Value Functions).

I skalära funktioner är function_body en serie Transact-SQL satser som tillsammans utvärderas till ett skalärt värde.

I MSTVF:er är function_body en serie Transact-SQL instruktioner som fyller i en TABLE returvariabel.

scalar_expression

Anger det skalära värde som skalärfunktionen returnerar.

BORD

Anger att returvärdet för den tabellvärdesfunktion (TVF) är en tabell. Endast konstanter och @local_variables kan skickas till TVF:er.

I infogade TVF:er definieras returvärdet TABLE via en enda SELECT instruktion. Infogade funktioner har inte associerade returvariabler.

I MSTVF:er är @return_variable en TABLE variabel som används för att lagra och ackumulera de rader som ska returneras som funktionens värde. @ return_variable kan endast anges för Transact-SQL funktioner och inte för CLR-funktioner.

select_stmt

Den programsats SELECT som definierar returvärdet för en infogad tabellvärdesfunktion (TVF).

BESTÄLL (<order_clause>)

Anger i vilken ordning resultaten returneras från tabellvärdesfunktionen. Mer information finns i avsnittet Använda sorteringsordning i CLR-tabellvärdesfunktioner senare i den här artikeln.

EXTERNT NAMN <method_specifierassembly_name>.class_name. method_name

Gäller för: SQL Server 2008 (10.0.x) SP 1 och senare versioner.

Anger den sammansättning och metod som det skapade funktionsnamnet ska referera till.

assembly_name – måste matcha ett värde i kolumnen SELECT * FROM sys.assemblies;i name .

Namnet som användes i instruktionen CREATE ASSEMBLY .
class_name – måste matcha ett värde i kolumnen SELECT * FROM sys.assembly_modules;för assembly_name .

Ofta innehåller värdet en inbäddad punkt eller punkt. I sådana fall kräver Transact-SQL-syntaxen att värdet är avgränsat med ett par hakparenteser ([]) eller med ett par dubbla citattecken ("").
method_name – måste matcha ett värde i kolumnen SELECT * FROM sys.assembly_modules;för method_name .

Metoden måste vara statisk.

I ett typiskt exempel för MyFood.dll, där alla typer finns i MyFood namnområdet, EXTERNAL NAME kan värdet vara MyFood.[MyFood.MyClass].MyStaticMethod.

Som standard kan SQL Server inte köra CLR-kod. Du kan skapa, ändra och ta bort databasobjekt som refererar till vanliga språkkörningsmoduler. Du kan dock inte köra dessa referenser i SQL Server förrän du aktiverar alternativet clr enabled. Om du vill aktivera det här alternativet använder du sp_configure. Det här alternativet är inte tillgängligt i en innesluten databas.

< > table_type_definition ( { <column_definition><column_constraint | <> computed_column_definition } [ <table_constraint> ] [ , ... n ] )

Definierar tabelldatatypen för en Transact-SQL funktion. Tabelldeklarationen innehåller kolumndefinitioner och kolumn- eller tabellbegränsningar. Tabellen placeras alltid i den primära filgruppen.

< > clr_table_type_definition ( { column_namedata_type } [ , ... n ] )

Gäller för: SQL Server 2008 (10.0.x) SP 1 och senare versioner samt Azure SQL Database (förhandsversion i vissa regioner).

Definierar tabelldatatyperna för en CLR-funktion. Tabelldeklarationen innehåller endast kolumnnamn och datatyper. Tabellen placeras alltid i den primära filgruppen.

NULL | INTE NULL

Stöds endast för internt kompilerade, skalära användardefinierade funktioner. Mer information finns i Skalär User-Defined Functions för In-Memory OLTP.

NATIVE_COMPILATION

Anger om en användardefinierad funktion är internt kompilerad. Det här argumentet krävs för internt kompilerade, skalära användardefinierade funktioner.

BÖRJA ATOMIC MED

Krävs och stöds endast för internt kompilerade skalära användardefinierade funktioner. Mer information finns i Atomic Blocks i Interna procedurer.

SCHEMABINDNING

Argumentet SCHEMABINDING krävs för internt kompilerade, skalära användardefinierade funktioner.

KÖR SOM

EXECUTE AS krävs för internt kompilerade, skalära användardefinierade funktioner.

< > function_option ::= och <clr_function_option> ::=

Anger att funktionen har ett eller flera av följande alternativ.

KRYPTERING

Gäller för: SQL Server 2008 (10.0.x) SP 1 och senare versioner.

Anger att databasmotorn konverterar den ursprungliga texten CREATE FUNCTION i uttalandet till ett dolt format. Utdata från fördunklingen är inte direkt synliga i några katalogvyer. Användare som inte har åtkomst till systemtabeller eller databasfiler kan inte hämta den dolda texten. Texten är dock tillgänglig för privilegierade användare som antingen kan komma åt systemtabeller via diagnostikanslutningen för databasadministratörer eller få direkt åtkomst till databasfiler. Användare som kan koppla en felsökare till serverprocessen kan också hämta den ursprungliga proceduren från minnet vid körning. Mer information om hur du kommer åt systemmetadata finns i Konfiguration av metadatasynlighet.

Om du använder det här alternativet förhindras funktionen från att publiceras som en del av SQL Server replikering. Det här alternativet kan inte anges för CLR-funktioner.

SCHEMABINDNING

Anger att funktionen är bunden till de databasobjekt som den refererar till. När SCHEMABINDING anges kan basobjekten inte ändras på ett sätt som skulle påverka funktionsdefinitionen. Själva funktionsdefinitionen måste först ändras eller tas bort för att ta bort beroenden på det objekt som ska ändras.

Bindningen av funktionen till de objekt som den refererar till tas bara bort när någon av följande åtgärder inträffar:

Funktionen tas bort.
Funktionen ändras med hjälp av -instruktionen ALTER med alternativet SCHEMABINDING inte angivet.

En funktion kan bara bindas till ett schema om följande villkor är uppfyllda:

Funktionen är en Transact-SQL funktion.
De användardefinierade funktionerna och vyerna som refereras av funktionen är också schemabundna.
Objekten som refereras av funktionen refereras till med hjälp av ett namn i två delar.
Funktionen och de objekt som den refererar till tillhör samma databas.
Användaren som körde instruktionen CREATE FUNCTION har REFERENCES behörighet till de databasobjekt som funktionen refererar till.

RETURNERAR NULL VID NULL-INDATA | ANROPAS VID NULL-INDATA

Anger OnNULLCall attributet för en skalär funktion. Om inget anges är det CALLED ON NULL INPUT underförstått som standard. Med andra ord körs funktionstexten även om NULL den skickas som ett argument.

Om RETURNS NULL ON NULL INPUT anges i en CLR-funktion anger det att SQL Server kan returnera NULL när något av argumenten som den tar emot är NULL, utan att faktiskt anropa funktionens brödtext. Om metoden för en CLR-funktion som anges i <method_specifier> redan har ett anpassat attribut som anger RETURNS NULL ON NULL INPUT, men programsatsen CREATE FUNCTION anger CALLED ON NULL INPUT, CREATE FUNCTION har satsen företräde. Attributet OnNULLCall kan inte anges för CLR-tabellvärdesfunktioner.

KÖR SOM

Anger den säkerhetskontext under vilken den användardefinierade funktionen körs. Därför kan du styra vilket användarkonto SQL Server använder för att verifiera behörigheter för alla databasobjekt som refereras av funktionen.

EXECUTE AS Det går inte att ange för infogade tabellvärdesfunktioner.

Mer information finns i EXECUTE AS-satsen (Transact-SQL).

INLINE = { PÅ | AV }

Gäller för: SQL Server 2019 (15.x) och senare versioner och Azure SQL Database.

Anger om den här skalära UDF:en ska vara infogad eller inte. Den här satsen gäller endast för skalära användardefinierade funktioner. Satsen INLINE är inte obligatorisk. Om satsen INLINE inte anges anges den automatiskt till ON eller OFF baserat på om UDF är infogad. Om INLINE = ON anges men UDF visar sig vara icke-infogad genereras ett fel. Mer information finns i Scalar UDF-inlining.

< > column_definition ::=

Definierar tabellens datatyp. Tabelldeklarationen innehåller kolumndefinitioner och begränsningar. För CLR-funktioner kan endast column_name och data_type anges.

column_name

Namnet på en kolumn i tabellen. Kolumnnamn måste följa reglerna för identifierare och måste vara unika i tabellen. column_name kan bestå av 1 till 128 tecken.

data_type

Anger kolumnens datatyp. För Transact-SQL funktioner tillåts alla datatyper, inklusive användardefinierade CLR-typer, förutom tidsstämpel. För CLR-funktioner tillåts alla datatyper, inklusive användardefinierade CLR-typer, förutom text, ntext, image, char, varchar, varchar(max) och timestamp. Markören av icke-skalig typ kan inte anges som en kolumndatatyp i funktionerna Transact-SQL eller CLR.

FÖRVALD constant_expression

Anger det värde som anges för kolumnen när ett värde inte uttryckligen anges under en infogning. constant_expression är en konstant NULLeller ett systemfunktionsvärde. DEFAULT Definitioner kan tillämpas på alla kolumner utom de som har egenskapen IDENTITY . DEFAULT Det går inte att ange för CLR-tabellvärdesfunktioner.

SORTERA COLLATION_NAME

Anger sortering för kolumnen. Om inget anges tilldelas kolumnen standardsorteringen för databasen. Sorteringsnamnet kan vara antingen ett Windows-sorteringsnamn eller ett SQL-sorteringsnamn. En lista över och mer information om sorteringar finns i Windows Collation Name (Transact-SQL) och SQL Server Collation Name (Transact-SQL) .

Satsen COLLATE kan endast användas för att ändra sorteringen av kolumner i datatyperna char, varchar, ncharochnvarchar . COLLATE Det går inte att ange för CLR-tabellvärdesfunktioner.

ROWGUIDCOL

Anger att den nya kolumnen är en rad med globalt unik identifierare. Endast en uniqueidentifier-kolumn per tabell kan anges som kolumn.ROWGUIDCOL Egenskapen ROWGUIDCOL kan bara tilldelas till en uniqueidentifier kolumn.

Egenskapen ROWGUIDCOL framtvingar inte unikhet för de värden som lagras i kolumnen. Den genererar inte heller automatiskt värden för nya rader som infogas i tabellen. Om du vill generera unika värden för varje kolumn använder du NEWID funktionen på INSERT instruktioner. Ett standardvärde kan anges. Det NEWID går dock inte att ange som standard.

IDENTITET

Anger att den nya kolumnen är en identitetskolumn. När en ny rad läggs till i tabellen tillhandahåller SQL Server ett unikt, inkrementellt värde för kolumnen. Identitetskolumner används vanligtvis tillsammans med PRIMARY KEY begränsningar för att fungera som den unika radidentifieraren för tabellen. Egenskapen IDENTITY kan tilldelas till tinyint, smallint, int, bigint, decimal(p,0) eller numeriska (p,0) kolumner. Endast en identitetskolumn kan skapas per tabell. Bundna standardvärden och DEFAULT begränsningar kan inte användas med en identitetskolumn. Du måste ange både frö och ökning eller ingetdera. Om inget av dem anges är standardvärdet (1,1).

IDENTITY Det går inte att ange för CLR-tabellvärdesfunktioner.

frö

Det heltalsvärde som ska tilldelas den första raden i tabellen.

öka

Heltalsvärdet som ska läggas till i startvärdet för efterföljande rader i tabellen.

< > column_constraint ::= och <table_constraint> ::=

Definierar begränsningen för en angiven kolumn eller tabell. För CLR-funktioner är NULLden enda tillåtna begränsningstypen . Namngivna begränsningar är inte tillåtna.

NULL | INTE NULL

Avgör om null-värden tillåts i kolumnen. NULL är inte strikt en begränsning utan kan anges precis som NOT NULL. NOT NULL Det går inte att ange för CLR-tabellvärdesfunktioner.

PRIMÄRNYCKEL

En begränsning som framtvingar entitetsintegritet för en angiven kolumn via ett unikt index. I användardefinierade tabellvärdesfunktioner PRIMARY KEY kan begränsningen endast skapas för en kolumn per tabell. PRIMARY KEY Det går inte att ange för CLR-tabellvärdesfunktioner.

UNIK

En begränsning som ger entitetsintegritet för en angiven kolumn eller kolumner via ett unikt index. En tabell kan ha flera UNIQUE begränsningar. UNIQUE Det går inte att ange för CLR-tabellvärdesfunktioner.

KLUSTRAD | NONCLUSTERED

Ange att ett grupperat eller ett icke-grupperat index har skapats för villkoret PRIMARY KEY or UNIQUE . PRIMARY KEY constraints use CLUSTEREDoch UNIQUE constraints use NONCLUSTERED.

CLUSTERED kan endast anges för en begränsning. Om CLUSTERED har angetts för en UNIQUE begränsning och en PRIMARY KEY begränsning också har angetts, PRIMARY KEY används NONCLUSTERED.

CLUSTERED och NONCLUSTERED kan inte anges för CLR-tabellvärdesfunktioner.

KONTROLLERA

En begränsning som framtvingar domänintegritet genom att begränsa de möjliga värden som kan anges i en kolumn eller kolumner. CHECK Det går inte att ange begränsningar för CLR-tabellvärdesfunktioner.

logical_expression

Ett logiskt uttryck som returnerar TRUE or .FALSE

< > computed_column_definition ::=

Anger en beräknad kolumn. Mer information om beräknade kolumner finns i CREATE TABLE (Transact-SQL) .

column_name

Namnet på den beräknade kolumnen.

computed_column_expression

Ett uttryck som definierar värdet för en beräknad kolumn.

< > index_option ::=

Anger indexalternativen för PRIMARY KEY indexet eller UNIQUE . Mer information om indexalternativ finns i CREATE INDEX (Transact-SQL) .

PAD_INDEX = { ON | AV }

Anger indexutfyllnad. Standardvärdet är OFF.

FILLFACTOR = fillfactor

Anger en procentsats som anger hur fullständig databasmotorn ska göra lövnivån för varje indexsida när index skapas eller ändras. fillfactor måste vara ett heltalsvärde från 1 till 100. Standardvärdet är 0.

IGNORE_DUP_KEY = { ON | AV }

Anger felsvaret när en infogningsåtgärd försöker infoga dubblettnyckelvärden i ett unikt index. Alternativet IGNORE_DUP_KEY gäller endast för infogningsåtgärder när indexet har skapats eller återskapats. Standardvärdet är OFF.

STATISTICS_NORECOMPUTE = { ON | AV }

Anger om distributionsstatistik beräknas om. Standardvärdet är OFF.

ALLOW_ROW_LOCKS = { ON | AV }

Anger om radlås tillåts. Standardvärdet är ON.

ALLOW_PAGE_LOCKS = { ON | AV }

Anger huruvida sidlås är tillåtna. Standardvärdet är ON.

Metodtips

Om en användardefinierad funktion inte skapas med satsen kan ändringar som görs i SCHEMABINDING underliggande objekt påverka definitionen av funktionen och ge oväntade resultat när den anropas. Vi rekommenderar att du implementerar någon av följande metoder för att säkerställa att funktionen inte blir inaktuell på grund av ändringar i dess underliggande objekt:

Ange -satsen WITH SCHEMABINDING när du skapar funktionen. Det här alternativet säkerställer att de objekt som refereras i funktionsdefinitionen inte kan ändras, såvida inte funktionen också ändras.
Kör den sp_refreshsqlmodule lagrade proceduren när du har ändrat ett objekt som anges i definitionen av funktionen.

Mer information och prestandaöverväganden om infogade tabellvärdesfunktioner (infogade TVF:er) och tabellvärdesfunktioner med flera instruktioner (MSTVF:er) finns i Skapa användardefinierade funktioner (databasmotor).

Datatyper

Om parametrar anges i en CLR-funktion ska de vara SQL Server typer som definierats tidigare för scalar_parameter_data_type. Mer information som jämför SQL Server systemdatatyper med CLR-integreringsdatatyper, eller .NET Framework vanliga språkkörningsdatatyper, finns i Mappa CLR-parameterdata.

För att SQL Server ska kunna referera till rätt metod när den är överbelastad i en klass måste metoden som anges i <method_specifier> ha följande egenskaper:

Ta emot samma antal parametrar som anges i [ , ...n ].
Ta emot alla parametrar efter värde, inte efter referens.
Använd parametertyper som är kompatibla med typer som anges i SQL Server funktionen.

Om returdatatypen för CLR-funktionen anger en tabelltyp (RETURNS TABLE) ska returdatatypen för metoden i <method_specifier> vara av typen IEnumerator eller , IEnumerableoch det förutsätter att gränssnittet implementeras av skaparen av funktionen. Till skillnad från Transact-SQL-funktioner kan CLR-funktioner inte innehålla PRIMARY KEY, UNIQUEeller CHECK begränsningar i <table_type_definition>. Datatyperna för kolumner som anges i <table_type_definition> måste matcha typerna av motsvarande kolumner i resultatuppsättningen som returneras av metoden i <method_specifier> vid körningen. Den här typkontrollen utförs inte när funktionen skapas.

Mer information om hur du programmerar CLR-funktioner finns i CLR User-Defined Functions.

Anmärkningar

Skalära funktioner kan anropas där skalära uttryck används, vilket inkluderar beräknade kolumner och CHECK villkorsdefinitioner. Skalära funktioner kan också köras med hjälp av -instruktionen EXECUTE (Transact-SQL). Skalärfunktioner måste anropas med hjälp av minst tvådelade namn på funktionen (<schema>.<function>). Mer information om namn på flera delar finns iTransact-SQL Syntaxkonventioner (Transact-SQL) . Tabellvärdesfunktioner kan anropas där tabelluttryck tillåts i satsen i FROMSELECT, INSERT, UPDATEeller DELETE -satser. Mer information finns i Köra användardefinierade funktioner.

Samverkan

Följande instruktioner är giltiga i en funktion:

Instruktioner för tilldelning.
Control-of-Flow-satser förutom TRY...CATCH instruktioner.
DECLARE Satser som definierar lokala datavariabler och lokala markörer.
SELECT Satser som innehåller SELECT-listor med uttryck som tilldelar värden till lokala variabler.
Marköråtgärder som refererar till lokala markörer som deklareras, öppnas, stängs och frigörs i funktionen. Endast FETCH instruktioner som tilldelar värden till lokala variabler med hjälp av -satsen INTO tillåts. FETCH Instruktioner som returnerar data till klienten är inte tillåtna.
INSERT, UPDATEoch DELETE -satser som ändrar lokala tabellvariabler.
EXECUTE Instruktioner som anropar utökade lagrade procedurer.

Mer information finns i Skapa användardefinierade funktioner (databasmotor).

Samverkan för beräknade kolumner

Funktioner har följande egenskaper. Värdena för dessa egenskaper avgör om funktioner kan användas i beräknade kolumner som kan sparas eller indexeras.

Fastighet	Beskrivning	Noteringar
`IsDeterministic`	Funktionen är deterministisk eller icke-deterministisk.	Lokal dataåtkomst tillåts i deterministiska funktioner. Till exempel skulle funktioner som alltid returnerar samma resultat när de anropas med hjälp av en specifik uppsättning indatavärden och med samma tillstånd för databasen märkas som deterministiska.
`IsPrecise`	Funktionen är exakt eller oprecis.	Oprecisa funktioner innehåller åtgärder som flyttalsoperationer.
`IsSystemVerified`	Funktionens precisions- och determinismegenskaper kan verifieras av SQL Server.
`SystemDataAccess`	Funktionen har åtkomst till systemdata (systemkataloger eller virtuella systemtabeller) i den lokala instansen av SQL Server.
`UserDataAccess`	Funktionen kommer åt användardata i den lokala instansen av SQL Server.	Innehåller användardefinierade tabeller och temporära tabeller, men inte tabellvariabler.

Precisions- och determinismegenskaperna för Transact-SQL funktioner bestäms automatiskt av SQL Server. Egenskaperna för dataåtkomst och determinism för CLR-funktioner kan anges av användaren. Mer information finns i CLR-integrering: anpassade attribut för CLR-rutiner.

Om du vill visa de aktuella värdena för dessa egenskaper använder du OBJECTPROPERTYEX (Transact-SQL).

Viktigt!

Funktioner måste skapas för SCHEMABINDING att vara deterministiska.

En beräknad kolumn som anropar en användardefinierad funktion kan användas i ett index när den användardefinierade funktionen har följande egenskapsvärden:

IsDeterministic är true
IsSystemVerified är true (om inte den beräknade kolumnen är beständig)
UserDataAccess är false
SystemDataAccess är false

Mer information finns i Index över beräknade kolumner.

Anropa utökade lagrade procedurer från funktioner

Den utökade lagrade proceduren kan inte returnera resultatuppsättningar till klienten när den anropas inifrån en funktion. Alla ODS-API:er som returnerar resultatuppsättningar till klienten returnerar FAIL. Den utökade lagrade proceduren kan ansluta tillbaka till en instans av SQL Server. Den bör dock inte försöka ansluta till samma transaktion som funktionen som anropade den utökade lagrade proceduren.

På samma sätt som anrop från en batch eller lagrad procedur körs den utökade lagrade proceduren i kontexten för det Windows-säkerhetskonto som SQL Server körs under. Ägaren till den lagrade proceduren bör överväga det här scenariot när han eller hon ger EXECUTE behörighet till användarna.

Begränsningar

Användardefinierade funktioner kan inte användas för att utföra åtgärder som ändrar databastillståndet.

Användardefinierade funktioner får inte innehålla en OUTPUT INTO-sats som har en tabell som mål.

Följande Service Broker-instruktioner kan inte ingå i definitionen av en Transact-SQL användardefinierad funktion:

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

Användardefinierade funktioner kan kapslas. Det vill säga en användardefinierad funktion kan anropa en annan. Kapslingsnivån ökas när den anropade funktionen börjar att köras och minskas när den anropade funktionen slutar att köra klart. Användardefinierade funktioner kan kapslas upp till 32 nivåer. Om du överskrider de maximala kapslingsnivåerna orsakar det att hela kedjan av anropsfunktioner misslyckas. Alla referenser till hanterad kod från en Transact-SQL-användardefinierad funktion räknas som ett nivå mot gränsen på 32 kapslingsnivåer. Metoder som anropas inifrån hanterad kod räknas inte mot den här gränsen.

Använd sorteringsordning i CLR-tabellvärdesfunktioner

När du använder -satsen ORDER i CLR-tabellvärdesfunktioner följer du dessa riktlinjer:

Du måste se till att resultaten alltid sorteras i den angivna ordningen. Om resultatet inte är i den angivna ordningen genererar SQL Server ett felmeddelande när frågan körs.
Om en ORDER sats anges måste utdata från den tabellvärdesbaserade funktionen sorteras enligt sorteringen av kolumnen (explicit eller implicit). Om kolumnsorteringen till exempel är kinesiska måste de returnerade resultaten sorteras enligt kinesiska sorteringsregler. (Sortering anges antingen i DDL för tabellvärdesfunktionen eller hämtas från databassorteringen.)
SQL Server verifierar alltid satsen om den ORDER anges, samtidigt som den returnerar resultat, oavsett om frågeprocessorn använder den för att utföra ytterligare optimeringar eller inte. Använd bara satsen ORDER om du vet att den är användbar för frågeprocessorn.
SQL Server-frågeprocessorn drar nytta av satsen ORDER automatiskt i följande fall:
- Infoga frågor där ORDER satsen är kompatibel med ett index.
- ORDER BY klausuler som är förenliga med klausulen ORDER .
- Aggregat, där GROUP BY är kompatibelt med ORDER satsen.
- DISTINCT aggregeringar där de distinkta kolumnerna är kompatibla med -satsen ORDER .

Satsen ORDER garanterar inte ordnade resultat när en SELECT fråga körs, såvida inte ORDER BY den också anges i frågan. Se sys.function_order_columns (Transact-SQL) för information om hur du frågar efter kolumner som ingår i sorteringsordningen för tabellvärdesfunktioner.

Metainformation

I följande tabell visas de systemkatalogvyer som du kan använda för att returnera metadata om användardefinierade funktioner.

Systemvy	Beskrivning
sys.sql_moduler	Se exempel E i avsnittet Exempel.
sys.assembly_modules	Visar information om användardefinierade CLR-funktioner.
sys.parametrar	Visar information om de parametrar som definierats i användardefinierade funktioner.
sys.sql_uttryck_beroenden	Visar de underliggande objekt som en funktion refererar till.

Behörigheter

Kräver CREATE FUNCTION behörighet i databasen och ALTER behörighet för schemat där funktionen skapas. Om funktionen anger en användardefinierad typ kräver EXECUTE behörighet för typen.

Exempel

Fler exempel och prestandaöverväganden om UDF:er finns i Skapa användardefinierade funktioner (databasmotor).

A. Använd en skalärvärderad användardefinierad funktion som beräknar ISO-veckan

I följande exempel skapas den användardefinierade funktionen ISOweek. Den här funktionen tar ett datumargument och beräknar ISO-veckonumret. För att den här funktionen ska beräknas korrekt SET DATEFIRST 1 måste den anropas innan funktionen anropas.

Exemplet visar också hur du använder EXECUTE AS-satsen (Transact-SQL) för att ange i vilken säkerhetskontext en lagrad procedur kan köras. I det här exemplet anger alternativet CALLER att proceduren körs i kontexten för den användare som anropar den. De andra alternativen som du kan ange är SELF, OWNERoch user_name.

Här är funktionsanropet. DATEFIRST är inställt på 1.

CREATE FUNCTION dbo.ISOweek (@DATE DATETIME)
RETURNS INT
WITH EXECUTE AS CALLER
AS
BEGIN
    DECLARE @ISOweek INT;

    SET @ISOweek = DATEPART(wk, @DATE) + 1 -
        DATEPART(wk, CAST(DATEPART(yy, @DATE) AS CHAR(4)) + '0104');

    --Special cases: Jan 1-3 may belong to the previous year
    IF (@ISOweek = 0)
        SET @ISOweek = dbo.ISOweek(CAST(DATEPART(yy, @DATE) - 1 AS CHAR(4))
           + '12' + CAST(24 + DATEPART(DAY, @DATE) AS CHAR(2))) + 1;

    --Special case: Dec 29-31 may belong to the next year
    IF ((DATEPART(mm, @DATE) = 12)
        AND ((DATEPART(dd, @DATE) - DATEPART(dw, @DATE)) >= 28))
    SET @ISOweek = 1;

    RETURN (@ISOweek);
END;
GO

SET DATEFIRST 1;

SELECT dbo.ISOweek(CONVERT(DATETIME, '12/26/2004', 101)) AS 'ISO Week';

Här är resultatet.

ISO Week
----------------
52

B. Skapa en infogad tabellvärdesfunktion

I följande exempel returneras en infogad tabellvärdesfunktion i AdventureWorks2022-databasen. Den returnerar tre kolumner ProductID, Nameoch summan av totalsummorna hittills i år per butik för varje produkt som YTD Total sålts till butiken.

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
    INNER JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID
    INNER JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID
    INNER JOIN Sales.Customer AS C ON SH.CustomerID = C.CustomerID
    WHERE C.StoreID = @storeid
    GROUP BY P.ProductID, P.Name
);
GO

Om du vill anropa funktionen kör du den här frågan.

SELECT * FROM Sales.ufn_SalesByStore (602);

C. Skapa en tabellvärdesfunktion med flera instruktioner

I följande exempel skapas den tabellvärdesbaserade funktionen fn_FindReports(InEmpID) i databasen AdventureWorks2022 . När funktionen anges med ett giltigt medarbetar-ID returnerar den en tabell som motsvarar alla anställda som rapporterar till medarbetaren antingen direkt eller indirekt. Funktionen använder ett rekursivt vanligt tabelluttryck (CTE) för att skapa den hierarkiska listan över anställda. Mer information om rekursiva CTE:er finns i WITH common_table_expression (Transact-SQL).

CREATE FUNCTION dbo.ufn_FindReports (@InEmpID INT)
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 (
        -- Get the initial list of Employees for Manager n
        SELECT e.BusinessEntityID,
            OrganizationNode = ISNULL(e.OrganizationNode, CAST('/' AS HIERARCHYID)),
            p.FirstName,
            p.LastName,
            e.JobTitle,
            0
        FROM HumanResources.Employee e
        INNER JOIN Person.Person p
            ON p.BusinessEntityID = e.BusinessEntityID
        WHERE e.BusinessEntityID = @InEmpID
        
        UNION ALL
        
        -- Join recursive member to anchor
        SELECT e.BusinessEntityID,
            e.OrganizationNode,
            p.FirstName,
            p.LastName,
            e.JobTitle,
            RecursionLevel + 1
        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

-- Example invocation
SELECT EmployeeID,
    FirstName,
    LastName,
    JobTitle,
    RecursionLevel
FROM dbo.ufn_FindReports(1);
GO

D. Skapa en CLR-funktion

I exemplet skapas CLR-funktionen len_s. Innan funktionen skapas registreras sammansättningen SurrogateStringFunction.dll i den lokala databasen.

Gäller för: SQL Server 2008 (10.0.x) SP 1 och senare versioner.

DECLARE @SamplesPath NVARCHAR(1024);

-- You may have to modify the value of this variable if you have
-- installed the sample in a location other than the default location.
SELECT @SamplesPath = REPLACE(physical_name,
    'Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\master.mdf',
    'Microsoft SQL Server\130\Samples\Engine\Programmability\CLR\'
)
FROM master.sys.database_files
WHERE name = 'master';

CREATE ASSEMBLY [SurrogateStringFunction]
FROM @SamplesPath + 'StringManipulate\CS\StringManipulate\bin\debug\SurrogateStringFunction.dll'
    WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO

CREATE FUNCTION [dbo].[len_s] (@str NVARCHAR(4000))
RETURNS BIGINT
AS
EXTERNAL NAME [SurrogateStringFunction].[Microsoft.Samples.SqlServer.SurrogateStringFunction].[LenS];
GO

Ett exempel på hur du skapar en CLR-tabellvärdesfunktion finns i CLR Table-Valued Functions.

E. Visa definitionen av användardefinierade funktioner

SELECT DEFINITION,
    type
FROM sys.sql_modules AS m
INNER JOIN sys.objects AS o
    ON m.object_id = o.object_id
    AND type IN ('FN', 'IF', 'TF');
GO

Definitionen av funktioner som ENCRYPTION skapats med hjälp av alternativet kan inte visas med hjälp sys.sql_modulesav , men annan information om de krypterade funktionerna visas.

Dela via

SKAPA FUNKTION (Transact-SQL)

Syntax

Argumentpunkter

ELLER ÄNDRA

schema_name

function_name

@parameter_name

[ type_schema_name. ] parameter_data_type

[ = standard ]

SKRIVSKYDDAD

return_data_type

function_body

scalar_expression

BORD

select_stmt

BESTÄLL (<order_clause>)

EXTERNT NAMN <method_specifierassembly_name>.class_name. method_name

< > table_type_definition ( { <column_definition><column_constraint | <> computed_column_definition } [ <table_constraint> ] [ , ... n ] )

< > clr_table_type_definition ( { column_namedata_type } [ , ... n ] )

NULL | INTE NULL

NATIVE_COMPILATION

BÖRJA ATOMIC MED

SCHEMABINDNING

KÖR SOM

< > function_option ::= och <clr_function_option> ::=

KRYPTERING

SCHEMABINDNING

RETURNERAR NULL VID NULL-INDATA | ANROPAS VID NULL-INDATA

KÖR SOM

INLINE = { PÅ | AV }

< > column_definition ::=

column_name

data_type

FÖRVALD constant_expression

SORTERA COLLATION_NAME

ROWGUIDCOL

IDENTITET

frö

öka

< > column_constraint ::= och <table_constraint> ::=

NULL | INTE NULL

PRIMÄRNYCKEL

UNIK

KLUSTRAD | NONCLUSTERED

KONTROLLERA

logical_expression

< > computed_column_definition ::=

column_name

computed_column_expression

< > index_option ::=

PAD_INDEX = { ON | AV }

FILLFACTOR = fillfactor

IGNORE_DUP_KEY = { ON | AV }

STATISTICS_NORECOMPUTE = { ON | AV }

ALLOW_ROW_LOCKS = { ON | AV }

ALLOW_PAGE_LOCKS = { ON | AV }

Metodtips

Datatyper

Anmärkningar

Samverkan

Samverkan för beräknade kolumner

Anropa utökade lagrade procedurer från funktioner

Begränsningar

Använd sorteringsordning i CLR-tabellvärdesfunktioner

Metainformation

Behörigheter

Exempel

A. Använd en skalärvärderad användardefinierad funktion som beräknar ISO-veckan

B. Skapa en infogad tabellvärdesfunktion

C. Skapa en tabellvärdesfunktion med flera instruktioner

D. Skapa en CLR-funktion

E. Visa definitionen av användardefinierade funktioner

Relaterat innehåll

Feedback

Ytterligare resurser