Freigeben über

CREATE-FUNKTION (Transact-SQL)

Gilt für:SQL ServerAzure SQL-DatenbankVerwaltete Azure SQL-Instanz

Erstellt eine benutzerdefinierte Funktion (User-Defined Function, UDF), bei der es sich um eine Transact-SQL oder CLR-Routine (Common Language Runtime) handelt. Eine benutzerdefinierte Funktion akzeptiert Parameter, führt eine Aktion aus, z. B. eine komplexe Berechnung, und gibt das Ergebnis dieser Aktion als Wert zurück. Der Rückgabewert kann entweder ein skalarer (einzelner) Wert oder eine Tabelle sein. Verwenden Sie diese Anweisung, um eine wiederverwendbare Routine zu erstellen, die auf folgende Weise verwendet werden kann:

  • In Transact-SQL Aussagen wie SELECT
  • In Anwendungen, die die Funktion
  • Bei der Definition einer anderen benutzerdefinierten Funktion
  • So parametrisieren Sie eine Sicht oder verbessern die Funktionalität einer indizierten Sicht
  • So definieren Sie eine Spalte in einer Tabelle
  • So definieren Sie eine CHECK Beschränkung für eine Spalte
  • So ersetzen Sie eine gespeicherte Prozedur
  • Verwenden einer Inlinefunktion als Filterprädikat für eine Sicherheitsrichtlinie

In diesem Artikel wird die Integration von .NET Framework CLR in SQL Server erläutert. Die CLR-Integration gilt nicht für Azure SQL-Datenbank.

Informationen zu Azure Synapse Analytics oder Microsoft Fabric finden Sie unter CREATE FUNCTION (Azure Synapse Analytics und Microsoft Fabric).

Transact-SQL-Syntaxkonventionen

Syntax

Syntax für Transact-SQL skalare Funktionen.

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 eingebundenen Tabellenwertfunktionen.

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 Tabellenwertfunktionen mit mehreren Anweisungen.

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 Funktionsklauseln.

<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 CLR-Skalarfunktionen.

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-Tabellenwertfunktionen.

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-Funktionsklauseln.

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

In-Memory-OLTP-Syntax für nativ kompilierte, benutzerdefinierte skalare Funktionen.

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 ]
}

Argumente

ODER ÄNDERN

Gilt für: SQL Server 2016 (13.x) SP 1 und höher sowie Azure SQL-Datenbank.

Ändert die Funktion nur bedingt, wenn sie bereits vorhanden ist.

Optionale Syntax OR ALTER ist für CLR verfügbar, beginnend mit SQL Server 2016 (13.x) SP 1 CU 1.

schema_name

Der Name des Schemas, zu dem die benutzerdefinierte Funktion gehört.

function_name

Der Name der benutzerdefinierten Funktion. Funktionsnamen müssen den Regeln für Bezeichner entsprechen und innerhalb der Datenbank und in ihrem Schema eindeutig sein.

Nach dem Funktionsnamen sind Klammern erforderlich, auch wenn kein Parameter angegeben ist.

@parameter_name

Ein Parameter in der benutzerdefinierten Funktion. Ein oder mehrere Parameter können deklariert werden.

Eine Funktion kann maximal 2.100 Parameter haben. Der Wert jedes deklarierten Parameters muss vom Benutzer angegeben werden, wenn die Funktion ausgeführt wird, es sei denn, es ist ein Standardwert für den Parameter definiert.

Geben Sie einen Parameternamen an, indem Sie ein At-Zeichen (@) als erstes Zeichen verwenden. Der Parametername muss den Regeln für Bezeichner entsprechen. Parameter sind lokal für die Funktion. Dieselben Parameternamen können in anderen Funktionen verwendet werden. Parameter können nur den Platz von Konstanten einnehmen; Sie können nicht anstelle von Tabellennamen, Spaltennamen oder den Namen anderer Datenbankobjekte verwendet werden.

ANSI_WARNINGS Wird nicht berücksichtigt, wenn Sie Parameter in einer gespeicherten Prozedur oder einer benutzerdefinierten Funktion übergeben oder wenn Sie Variablen in einer Batch-Anweisung deklarieren und festlegen. Wenn beispielsweise eine Variable als char(3) definiert und dann auf einen Wert gesetzt wird, der größer als drei Zeichen ist, werden die Daten auf die definierte Größe gekürzt und die INSERT oder-Anweisung UPDATE ist erfolgreich.

[ type_schema_name. ] parameter_data_type

Der Parameterdatentyp und optional das Schema, zu dem er gehört. Für Transact-SQL Funktionen sind alle Datentypen, einschließlich benutzerdefinierter CLR-Typen und benutzerdefinierter Tabellentypen, mit Ausnahme des timestamp-Datentyps zulässig. Für CLR-Funktionen sind alle Datentypen, einschließlich benutzerdefinierter CLR-Typen, zulässig, mit Ausnahme der Datentypen text, ntext, image, benutzerdefinierte Tabellentypen und timestamp . Die nicht skalaren Typen Cursor und Table können weder in Transact-SQL noch in CLR-Funktionen als Parameterdatentyp angegeben werden.

Wenn type_schema_name nicht angegeben ist, sucht Datenbank-Engine in der folgenden Reihenfolge nach dem scalar_parameter_data_type :

  • Das Schema, das die Namen der SQL Server-Systemdatentypen enthält.
  • Das Standardschema des aktuellen Benutzers in der aktuellen Datenbank.
  • Das dbo-Schema in der aktuellen Datenbank.

[ = Standardeinstellung ]

Ein Standardwert für den Parameter. Wenn ein Standardwert definiert ist, kann die Funktion ausgeführt werden, ohne einen Wert für diesen Parameter anzugeben.

Standardparameterwerte können für CLR-Funktionen angegeben werden, mit Ausnahme der Datentypen varchar(max) und varbinary(max).

Wenn ein Parameter der Funktion einen Standardwert hat, muss das Schlüsselwort DEFAULT angegeben werden, wenn die Funktion aufgerufen wird, um den Standardwert abzurufen. Dieses Verhalten unterscheidet sich von der Verwendung von Parametern mit Standardwerten in gespeicherten Prozeduren, bei denen das Weglassen des Parameters auch den Standardwert impliziert. Das DEFAULT Schlüsselwort ist jedoch nicht erforderlich, wenn eine Skalarfunktion mithilfe der EXECUTE Anweisung aufgerufen wird.

SCHREIBGESCHÜTZT

Gibt an, dass der Parameter innerhalb der Definition der Funktion nicht aktualisiert oder geändert werden kann. READONLY ist für benutzerdefinierte Tabellentypparameter (TVPs) erforderlich und kann nicht für einen anderen Parametertyp verwendet werden.

return_data_type

Der Rückgabewert einer benutzerdefinierten skalaren Funktion. Für Transact-SQL Funktionen sind alle Datentypen, einschließlich benutzerdefinierter CLR-Typen, mit Ausnahme des timestamp-Datentyps zulässig. Für CLR-Funktionen sind alle Datentypen, einschließlich benutzerdefinierter CLR-Typen, mit Ausnahme der Datentypen text, ntext, image und timestamp , zulässig. Die nicht skalaren Typen Cursor und Tabelle können weder in Transact-SQL noch in CLR-Funktionen als Rückgabedatentyp angegeben werden.

function_body

Gibt an, dass eine Reihe von Transact-SQL Anweisungen, die zusammen keinen Nebeneffekt wie das Ändern einer Tabelle erzeugen, den Wert der Funktion definieren. function_body wird nur in skalaren Funktionen und MSTVFs (Multi-Statement Table-Valued Functions) verwendet.

In skalaren Funktionen ist function_body eine Reihe von Transact-SQL Anweisungen, die zusammen zu einem skalaren Wert ausgewertet werden.

In MSTVFs ist function_body eine Reihe von Transact-SQL Anweisungen, die eine TABLE Rückgabevariable füllen.

scalar_expression

Gibt den skalaren Wert an, den die Skalarfunktion zurückgibt.

Tabelle

Gibt an, dass der Rückgabewert der Tabellenwertfunktion (TVF) eine Tabelle ist. Nur Konstanten und @local_variables können an TVFs übergeben werden.

In Inline-TVFs wird der TABLE Rückgabewert durch eine einzige SELECT Anweisung definiert. Inlinefunktionen sind keine Rückgabevariablen zugeordnet.

In MSTVFs ist @return_variable eine TABLE Variable, die zum Speichern und Akkumulieren der Zeilen verwendet wird, die als Wert der Funktion zurückgegeben werden sollen. @ return_variable kann nur für Transact-SQL Funktionen und nicht für CLR-Funktionen angegeben werden.

select_stmt

Die einzelne SELECT Anweisung, die den Rückgabewert einer Inline-Tabellenwertfunktion (TVF) definiert.

BESTELLUNG (<order_clause>)

Gibt die Reihenfolge an, in der Ergebnisse von der Tabellenwertfunktion zurückgegeben werden. Weitere Informationen finden Sie im Abschnitt Verwenden der Sortierreihenfolge in CLR-Tabellenwertfunktionen weiter unten in diesem Artikel.

EXTERNER NAME <method_specifier>assembly_name.class_name. method_name

Gilt für: SQL Server 2008 (10.0.x) SP 1 und höher.

Gibt die Assembly und Methode an, auf die sich der erstellte Funktionsname beziehen soll.

  • assembly_name - muss mit einem Wert in der name Spalte von SELECT * FROM sys.assemblies;übereinstimmen.

    Der Name, der für die CREATE ASSEMBLY Anweisung verwendet wurde.

  • class_name - muss mit einem Wert in der assembly_name Spalte von SELECT * FROM sys.assembly_modules;übereinstimmen.

    Häufig enthält der Wert einen eingebetteten Punkt oder Punkt. In solchen Fällen erfordert die Transact-SQL Syntax, dass der Wert durch ein Paar eckiger Klammern ([]) oder durch ein Paar doppelter Anführungszeichen ("") begrenzt wird.

  • method_name - muss mit einem Wert in der method_name Spalte von SELECT * FROM sys.assembly_modules;übereinstimmen.

    Die Methode muss statisch sein.

In einem typischen Beispiel für MyFood.dll, in dem MyFood sich alle Typen im Namensraum befinden, könnte der EXTERNAL NAME Wert lauten MyFood.[MyFood.MyClass].MyStaticMethod.

Standardmäßig kann SQL Server keinen CLR-Code ausführen. Sie können Datenbankobjekte erstellen, ändern und löschen, die auf Common Language Runtime-Module verweisen. Sie können diese Verweise jedoch erst dann in SQL Server ausführen, wenn Sie die Option clr enabled aktiviert haben. Um diese Option zu aktivieren, verwenden Sie sp_configure. Diese Option ist in einer eigenständigen Datenbank nicht verfügbar.

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

Definiert den Tabellendatentyp für eine Transact-SQL Funktion. Die Tabellendeklaration enthält Spaltendefinitionen und Spalten- oder Tabelleneinschränkungen. Die Tabelle wird immer in der primären Dateigruppe abgelegt.

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

Gilt für: SQL Server 2008 (10.0.x) SP 1 und höher sowie Azure SQL-Datenbank (Preview in einigen Regionen).

Definiert die Tabellendatentypen für eine CLR-Funktion. Die Tabellendeklaration enthält nur Spaltennamen und Datentypen. Die Tabelle wird immer in der primären Dateigruppe abgelegt.

NULL | NICHT NULL

Wird nur für nativ kompilierte, benutzerdefinierte skalare Funktionen unterstützt. Weitere Informationen finden Sie unter Skalare User-Defined Funktionen für In-Memory OLTP.

Native-Kompilierung

Gibt an, ob eine benutzerdefinierte Funktion nativ kompiliert wurde. Dieses Argument ist für nativ kompilierte, skalare benutzerdefinierte Funktionen erforderlich.

BEGINNEN SIE ATOMAR MIT

Erforderlich und wird nur für nativ kompilierte benutzerdefinierte skalare Funktionen unterstützt. Weitere Informationen finden Sie unter Atomare Blöcke in systemeigenen Prozeduren.

SCHEMABINDING

Das SCHEMABINDING Argument ist für nativ kompilierte, skalare benutzerdefinierte Funktionen erforderlich.

AUSFÜHREN ALS

EXECUTE AS ist für nativ kompilierte, skalare benutzerdefinierte Funktionen erforderlich.

< > function_option ::= und <clr_function_option> ::=

Gibt an, dass die Funktion über eine oder mehrere der folgenden Optionen verfügt.

VERSCHLÜSSELUNG

Gilt für: SQL Server 2008 (10.0.x) SP 1 und höher.

Gibt an, dass Datenbank-Engine den ursprünglichen Text der CREATE FUNCTION Anweisung in ein verschleiertes Format konvertiert. Die Ausgabe der Verschleierung ist in Katalogansichten nicht direkt sichtbar. Benutzer, die keinen Zugriff auf Systemtabellen oder Datenbankdateien haben, können den verschleierten Text nicht abrufen. Der Text ist jedoch für privilegierte Benutzer verfügbar, die entweder über die Diagnoseverbindung für Datenbankadministratoren oder direkt auf Datenbankdateien auf Systemtabellen zugreifen können. Außerdem können Benutzer, die einen Debugger an den Serverprozess anfügen können, die ursprüngliche Prozedur zur Laufzeit aus dem Arbeitsspeicher abrufen. Weitere Informationen zum Zugreifen auf Systemmetadaten finden Sie unter Konfiguration der Metadatensichtbarkeit.

Durch die Verwendung dieser Option wird verhindert, dass die Funktion im Rahmen der SQL Server-Replikation veröffentlicht wird. Diese Option kann für CLR-Funktionen nicht angegeben werden.

SCHEMABINDING

Gibt an, dass die Funktion an die Datenbankobjekte gebunden ist, auf die sie verweist. Wenn SCHEMABINDING angegeben ist, können die Basisobjekte nicht so geändert werden, dass sie sich auf die Funktionsdefinition auswirken würden. Die Funktionsdefinition selbst muss zuerst geändert oder gelöscht werden, um Abhängigkeiten von dem zu ändernden Objekt zu entfernen.

Die Bindung der Funktion an die Objekte, auf die sie verweist, wird nur entfernt, wenn eine der folgenden Aktionen eintritt:

  • Die Funktion wird gelöscht.
  • Die Funktion wird geändert, indem die ALTER Anweisung verwendet wird, bei der die SCHEMABINDING Option nicht angegeben ist.

Eine Funktion kann nur dann schemagebunden werden, wenn die folgenden Bedingungen erfüllt sind:

  • Bei der Funktion handelt es sich um eine Transact-SQL Funktion.
  • Die benutzerdefinierten Funktionen und Ansichten, auf die von der Funktion verwiesen wird, sind ebenfalls schemagebunden.
  • Die Objekte, auf die von der Funktion verwiesen wird, werden mit einem zweiteiligen Namen referenziert.
  • Die Funktion und die Objekte, auf die sie verweist, gehören zur gleichen Datenbank.
  • Der Benutzer, der die CREATE FUNCTION Anweisung ausgeführt hat, verfügt über REFERENCES Berechtigungen für die Datenbankobjekte, auf die die Funktion verweist.

GIBT NULL BEI NULL-EINGABE ZURÜCK | BEI NULL-EINGABE AUFGERUFEN

Gibt das OnNULLCall Attribut einer Skalarfunktion an. Wenn nicht angegeben, CALLED ON NULL INPUT wird dies standardmäßig impliziert. Mit anderen Worten, der Funktionskörper wird auch dann ausgeführt, wenn NULL er als Argument übergeben wird.

Wenn RETURNS NULL ON NULL INPUT in einer CLR-Funktion angegeben ist, gibt dies an, dass SQL Server zurückgeben NULL kann, wenn eines der empfangenen Argumente ist NULL, ohne den Text der Funktion tatsächlich aufzurufen. Wenn die Methode einer CLR-Funktion, die in <method_specifier> angegeben ist, bereits über ein benutzerdefiniertes Attribut verfügt, das angibt RETURNS NULL ON NULL INPUT, die CREATE FUNCTION Anweisung jedoch angibt CALLED ON NULL INPUT, hat die CREATE FUNCTION Anweisung Vorrang. Das OnNULLCall Attribut kann nicht für CLR-Tabellenwertfunktionen angegeben werden.

AUSFÜHREN ALS

Gibt den Sicherheitskontext an, unter dem die benutzerdefinierte Funktion ausgeführt wird. Daher können Sie steuern, welches Benutzerkonto SQL Server zum Überprüfen von Berechtigungen für Datenbankobjekte verwendet, auf die von der Funktion verwiesen wird.

EXECUTE AS Kann nicht für eingebundene Tabellenwertfunktionen angegeben werden.

Weitere Informationen finden Sie unter EXECUTE AS-Klausel (Transact-SQL).

INLINE = { EIN | AUS }

Gilt für: SQL Server 2019 (15.x) und höhere Versionen sowie Azure SQL-Datenbank.

Gibt an, ob dieses skalare UDF inline sein soll oder nicht. Diese Klausel gilt nur für benutzerdefinierte skalare Funktionen. Die INLINE Klausel ist nicht obligatorisch. Wenn die INLINE Klausel nicht angegeben wird, wird sie automatisch auf oder OFF basierend darauf ON festgelegt, ob die benutzerdefinierte Klausel inlinefähig ist. Wenn INLINE = ON angegeben ist, die benutzerdefinierte Datei jedoch als nicht inlinefähig befunden wird, wird ein Fehler ausgegeben. Weitere Informationen finden Sie unter Skalares UDF-Inlining.

< > column_definition ::=

Definiert den Datentyp der Tabelle. Die Tabellendeklaration enthält Spaltendefinitionen und Einschränkungen. Für CLR-Funktionen können nur column_name und data_type angegeben werden.

column_name

Der Name einer Spalte in der Tabelle. Spaltennamen müssen den Regeln für Bezeichner entsprechen und in der Tabelle eindeutig sein. column_name kann aus 1 bis 128 Zeichen bestehen.

data_type

Gibt den Spaltendatentyp an. Für Transact-SQL Funktionen sind alle Datentypen, einschließlich benutzerdefinierter CLR-Typen, mit Ausnahme des Zeitstempels zulässig. Für CLR-Funktionen sind alle Datentypen, einschließlich benutzerdefinierter CLR-Typen, mit Ausnahme von text, ntext, image, char, varchar, varchar(max) und timestamp zulässig. Der Cursor des nicht skalaren Typs kann weder in Transact-SQL noch in der CLR-Funktion als Spaltendatentyp angegeben werden.

STANDARDMÄSSIGE constant_expression

Gibt den Wert an, der für die Spalte bereitgestellt wird, wenn während einer Einfügung kein Wert explizit angegeben wird. constant_expression ist eine Konstante NULLoder ein Systemfunktionswert. DEFAULT Definitionen können auf jede Spalte angewendet werden, mit Ausnahme derjenigen, die über die IDENTITY Eigenschaft verfügen. DEFAULT kann nicht für CLR-Tabellenwertfunktionen angegeben werden.

SORTIEREN collation_name

Gibt die Sortierung für die Spalte an. Wenn keine Sortierung angegeben ist, wird der Spalte die Standardsortierung der Datenbank zugewiesen. Als Sortierungsname kann entweder der Name einer Windows-Sortierreihenfolge oder ein SQL-Sortierungsname verwendet werden. Eine Liste und weitere Informationen zu Sortierungen finden Sie unter Windows-Sortierungsname (Transact-SQL) und SQL Server-Sortierungsname (Transact-SQL).

Die COLLATE Klausel kann verwendet werden, um die Sortierungen nur von Spalten der Datentypen char, varchar, nchar und nvarchar zu ändern. COLLATE kann nicht für CLR-Tabellenwertfunktionen angegeben werden.

ROWGUIDCOL

Gibt an, dass es sich bei der neuen Spalte um eine Spalte mit global eindeutigem Zeilenbezeichner handelt. Es kann nur eine uniqueidentifier-Spalte pro Tabelle als ROWGUIDCOL Spalte festgelegt werden. Die ROWGUIDCOL Eigenschaft kann nur einer uniqueidentifier-Spalte zugewiesen werden.

Die ROWGUIDCOL Eigenschaft erzwingt nicht die Eindeutigkeit der in der Spalte gespeicherten Werte. Es werden auch nicht automatisch Werte für neue Zeilen generiert, die in die Tabelle eingefügt werden. Um eindeutige Werte für jede Spalte zu generieren, verwenden Sie die NEWID Funktion für INSERT Anweisungen. Es kann ein Standardwert angegeben werden. Kann NEWID jedoch nicht als Standard angegeben werden.

IDENTITÄT

Gibt an, dass es sich bei der neuen Spalte um eine Identitätsspalte handelt. Wenn der Tabelle eine neue Zeile hinzugefügt wird, stellt SQL Server einen eindeutigen, inkrementellen Wert für die Spalte bereit. Identitätsspalten werden in der Regel zusammen mit PRIMARY KEY Einschränkungen verwendet, um als eindeutige Zeilenkennung für die Tabelle zu dienen. Die IDENTITY Eigenschaft kann den Spalten tinyint, smallint, int, bigint, decimal(p,0) oder numeric(p,0) zugewiesen werden. Pro Tabelle kann nur eine Identitätsspalte erstellt werden. Gebundene Standardwerte und DEFAULT Einschränkungen können nicht mit einer Identitätsspalte verwendet werden. Sie müssen sowohl den Startwert als auch das Inkrement angeben oder keines von beiden. Wenn keines der beiden angegeben ist, ist der Standardwert (1,1).

IDENTITY kann nicht für CLR-Tabellenwertfunktionen angegeben werden.

Samen

Der ganzzahlige Wert, der der ersten Zeile in der Tabelle zugewiesen werden soll.

Zunahme

Der ganzzahlige Wert, der dem Startwert für aufeinanderfolgende Zeilen in der Tabelle hinzugefügt werden soll.

< > column_constraint ::= und <table_constraint> ::=

Definiert die Einschränkung für eine angegebene Spalte oder Tabelle. Für CLR-Funktionen ist NULLder einzige zulässige Einschränkungstyp . Benannte Einschränkungen sind nicht zulässig.

NULL | NICHT NULL

Bestimmt, ob NULL-Werte in der Spalte zulässig sind. NULL ist nicht streng genommen eine Einschränkung, sondern kann genau wie NOT NULLangegeben werden. NOT NULL kann nicht für CLR-Tabellenwertfunktionen angegeben werden.

PRIMÄRSCHLÜSSEL

Eine Einschränkung, die die Entitätsintegrität für eine angegebene Spalte über einen eindeutigen Index erzwingt. In benutzerdefinierten Tabellenwertfunktionen kann die PRIMARY KEY Einschränkung nur für eine Spalte pro Tabelle erstellt werden. PRIMARY KEY kann nicht für CLR-Tabellenwertfunktionen angegeben werden.

EINZIGARTIG

Eine Einschränkung, die Entitätsintegrität für eine angegebene Spalte (oder Spalten) durch einen eindeutigen Index bereitstellt. Eine Tabelle kann mehrere UNIQUE Einschränkungen aufweisen. UNIQUE kann nicht für CLR-Tabellenwertfunktionen angegeben werden.

GRUPPIERT | NICHT GRUPPIERT

Geben Sie an, dass ein gruppierter oder nicht gruppierter Index für die PRIMARY KEYUNIQUE oder-Einschränkung erstellt wird. PRIMARY KEYconstraints verwenden CLUSTERED, und UNIQUE constraints verwenden .NONCLUSTERED

CLUSTERED Kann nur für eine Abhängigkeit angegeben werden. Wenn CLUSTERED für eine UNIQUE Randbedingung angegeben ist und auch eine PRIMARY KEY Randbedingung angegeben ist, verwendet die PRIMARY KEY .NONCLUSTERED

CLUSTERED und NONCLUSTERED kann nicht für CLR-Tabellenwertfunktionen angegeben werden.

PRÜFEN

Eine Einschränkung, die Domänenintegrität erzwingt, indem die möglichen Eingabewerte für eine oder mehrere Spalten beschränkt wird. CHECK Einschränkungen können für CLR-Tabellenwertfunktionen nicht angegeben werden.

logical_expression

Ein logischer Ausdruck, der oder zurückgibt TRUEFALSE.

< > computed_column_definition ::=

Gibt eine berechnete Spalte an. Weitere Informationen zu berechneten Spalten finden Sie unter CREATE TABLE (Transact-SQL).

column_name

Der Name der berechneten Spalte.

computed_column_expression

Ein Ausdruck, der den Wert einer berechneten Spalte definiert.

< > index_option ::=

Gibt die Indexoptionen für den PRIMARY KEYUNIQUE oder-Index an. Weitere Informationen zu Indexoptionen finden Sie unter CREATE INDEX (Transact-SQL).

PAD_INDEX = { EIN | AUS }

Gibt den Indexabstand an. Der Standardwert lautet OFF.

FILLFACTOR = Füllfaktor

Gibt einen Prozentsatz an, der angibt, wie voll die Blattebene jeder Indexseite von Datenbank-Engine während der Indexerstellung oder -änderung erstellt werden soll. Füllfaktor muss ein ganzzahliger Wert zwischen 1 und 100 sein. Der Standardwert ist 0.

IGNORE_DUP_KEY = { EIN | AUS }

Gibt die Fehlermeldung an, wenn ein Einfügevorgang versucht, doppelte Schlüsselwerte in einen eindeutigen Index einzufügen. Die IGNORE_DUP_KEY Option gilt nur für Einfügevorgänge, nachdem der Index erstellt oder neu erstellt wurde. Der Standardwert lautet OFF.

STATISTICS_NORECOMPUTE = { EIN | AUS }

Gibt an, ob Verteilungsstatistiken neu berechnet werden. Der Standardwert lautet OFF.

ALLOW_ROW_LOCKS = { EIN | AUS }

Gibt an, ob Zeilensperren zulässig sind. Der Standardwert lautet ON.

ALLOW_PAGE_LOCKS = { EIN | AUS }

Gibt an, ob Seitensperren zulässig sind. Der Standardwert lautet ON.

Bewährte Methoden

Wenn eine benutzerdefinierte Funktion nicht mit der SCHEMABINDING Klausel erstellt wird, können Änderungen, die an zugrunde liegenden Objekten vorgenommen werden, die Definition der Funktion beeinflussen und beim Aufrufen 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 ist:

  • Geben Sie die WITH SCHEMABINDING Klausel an, wenn Sie die Funktion erstellen. Diese Option stellt sicher, dass die Objekte, auf die in der Funktionsdefinition verwiesen wird, nicht geändert werden können, es sei denn, die Funktion wird ebenfalls geändert.

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

Weitere Informationen und Leistungsüberlegungen zu Inline-Tabellenwertfunktionen (Inline-TVFs) und Tabellenwertfunktionen mit mehreren Anweisungen (MSTVFs) finden Sie unter Erstellen benutzerdefinierter Funktionen (Datenbank-Engine).

Datentypen

Wenn Parameter in einer CLR-Funktion angegeben werden, sollten es sich um SQL Server-Typen handeln, die zuvor für scalar_parameter_data_type definiert wurden. Weitere Informationen zum Vergleichen von SQL Server-Systemdatentypen mit CLR-Integrationsdatentypen oder .NET Framework Common Language Runtime-Datentypen finden Sie unter Zuordnen von CLR-Parameterdaten.

Damit SQL Server auf die richtige Methode verweist, wenn sie in einer Klasse überladen ist, muss die in <method_specifier> angegebene Methode die folgenden Merkmale aufweisen:

  • Sie erhalten die gleiche Anzahl von Parametern wie in [ , ...n ]angegeben.
  • Empfangen Sie alle Parameter als Wert, nicht als Referenz.
  • Verwenden Sie Parametertypen, die mit den in der SQL Server-Funktion angegebenen Typen kompatibel sind.

Wenn der Rückgabedatentyp der CLR-Funktion einen Tabellentyp (RETURNS TABLE) angibt, sollte der Rückgabedatentyp der Methode in <method_specifier> vom Typ or IEnumerablesein, und es wird davon ausgegangen, dass die Schnittstelle vom Ersteller IEnumerator der Funktion implementiert wurde. Im Gegensatz zu Transact-SQL Funktionen können CLR-Funktionen keine PRIMARY KEY, UNIQUEoder CHECK Einschränkungen in <table_type_definition>enthalten. Die Datentypen der Spalten, die in <table_type_definition> angegeben sind, müssen mit den Typen der entsprechenden Spalten des Resultsets übereinstimmen, das von der Methode in <method_specifier> zur Ausführungszeit zurückgegeben wird. Diese Typüberprüfung wird zum Zeitpunkt der Erstellung der Funktion nicht durchgeführt.

Weitere Informationen zum Programmieren von CLR-Funktionen finden Sie unter CLR User-Defined Functions.

Bemerkungen

Skalarfunktionen können aufgerufen werden, wenn skalare Ausdrücke verwendet werden, z. B. berechnete Spalten und CHECK Einschränkungsdefinitionen. Skalare Funktionen können auch mit der Anweisung EXECUTE (Transact-SQL) ausgeführt werden. Skalare Funktionen müssen aufgerufen werden, indem mindestens der zweiteilige Name der Funktion (<schema>.<function>) verwendet wird. Weitere Informationen zu mehrteiligen Namen finden Sie unter Transact-SQL Syntaxkonventionen (Transact-SQL). Tabellenwertfunktionen können aufgerufen werden, wenn Tabellenausdrücke in der FROM Klausel von SELECT, INSERT, UPDATEoder DELETE -Anweisungen zulässig sind. Weitere Informationen finden Sie unter Ausführen benutzerdefinierter Funktionen.

Interoperabilität

Die folgenden Anweisungen sind in einer Funktion gültig:

  • Aufgabenanweisungen.
  • Control-of-Flow-Anweisungen mit Ausnahme TRY...CATCH von Anweisungen.
  • DECLARE Anweisungen, die lokale Datenvariablen und lokale Cursor definieren.
  • SELECT Anweisungen, die Auswahllisten mit Ausdrücken enthalten, die lokalen Variablen Werte zuweisen.
  • Cursorvorgänge, die auf lokale Cursor verweisen, die in der Funktion deklariert, geöffnet, geschlossen und freigegeben werden. Nur FETCH Anweisungen, die lokalen Variablen mithilfe der INTO Klausel Werte zuweisen, sind zulässig. FETCH Anweisungen, die Daten an den Client zurückgeben, sind nicht zulässig.
  • INSERT, und DELETE Anweisungen, UPDATEdie lokale Tabellenvariablen ändern.
  • EXECUTE Anweisungen, die erweiterte gespeicherte Prozeduren aufrufen.

Weitere Informationen finden Sie unter Erstellen benutzerdefinierter Funktionen (Datenbankmodul).

Interoperabilität der berechneten Spalte

Funktionen haben die folgenden Eigenschaften. Die Werte dieser Eigenschaften bestimmen, ob Funktionen in berechneten Spalten verwendet werden können, die beibehalten oder indiziert werden können.

Eigentum BESCHREIBUNG Hinweise
IsDeterministic Die Funktion ist deterministisch oder nichtdeterministisch. Der lokale Datenzugriff ist in deterministischen Funktionen erlaubt. Beispielsweise würden Funktionen, die immer das gleiche Ergebnis zurückgeben, wenn sie mit einem bestimmten Satz von Eingabewerten und mit demselben Zustand der Datenbank aufgerufen werden, als deterministisch bezeichnet.
IsPrecise Funktion ist präzise oder ungenau. Ungenaue Funktionen enthalten Operationen, wie z. B. Gleitkommaoperationen.
IsSystemVerified Die Genauigkeits- und Determinismuseigenschaften der Funktion können von SQL Server überprüft werden.
SystemDataAccess Die Funktion greift auf Systemdaten (Systemkataloge oder Tabellen virtueller Systeme) in der lokalen Instanz von SQL Server zu.
UserDataAccess Die Funktion greift auf Benutzerdaten in der lokalen Instanz von SQL Server zu. Enthält benutzerdefinierte Tabellen und temporäre Tabellen, jedoch keine Tabellenvariablen.

Die Genauigkeits- und Determinismuseigenschaften Transact-SQL Funktionen werden automatisch von SQL Server bestimmt. Die Datenzugriffs- und Determinismuseigenschaften von CLR-Funktionen können vom Benutzer angegeben werden. Weitere Informationen finden Sie unter CLR-Integration: Benutzerdefinierte Attribute für CLR-Routinen.

Um die aktuellen Werte für diese Eigenschaften anzuzeigen, verwenden Sie OBJECTPROPERTYEX (Transact-SQL).

Von Bedeutung

Funktionen müssen mit SCHEMABINDING erstellt werden, um deterministisch zu sein.

Eine berechnete Spalte, die eine benutzerdefinierte Funktion aufruft, kann in einem Index verwendet werden, wenn die benutzerdefinierte Funktion die folgenden Eigenschaftswerte aufweist:

  • IsDeterministic ist true
  • IsSystemVerified ist true (es sei denn, die berechnete Spalte wird beibehalten)
  • UserDataAccess ist false
  • SystemDataAccess ist false

Weitere Informationen finden Sie unter Indexes on Computed Columns.

Aufrufen erweiterter gespeicherter Prozeduren aus Funktionen

Wenn die erweiterte gespeicherte Prozedur sie innerhalb einer Funktion aufruft, kann sie keine Resultsets an den Client zurückgeben. Alle ODS-APIs, die Resultsets an den Client zurückgeben, geben FAIL. Die erweiterte gespeicherte Prozedur kann eine Verbindung mit einer Instanz von SQL Server herstellen. Es sollte jedoch nicht versucht werden, dieselbe Transaktion wie die Funktion zu verknüpfen, die die erweiterte gespeicherte Prozedur aufgerufen hat.

Ähnlich wie Aufrufe aus einem Batch oder einer gespeicherten Prozedur wird die erweiterte gespeicherte Prozedur im Kontext des Windows-Sicherheitskontos ausgeführt, unter dem SQL Server ausgeführt wird. Der Besitzer der gespeicherten Prozedur sollte dieses Szenario berücksichtigen, wenn er Benutzern Berechtigungen dafür erteilt EXECUTE .

Einschränkungen

Benutzerdefinierte Funktionen können nicht zum Ausführen von Aktionen verwendet werden, die den Datenbankstatus ändern.

Benutzerdefinierte Funktionen dürfen keine OUTPUT INTO Klausel enthalten, die eine Tabelle als Ziel hat.

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

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

Benutzerdefinierte Funktionen können geschachtelt werden; d. h. eine benutzerdefinierte Funktion kann eine andere aufrufen. Die Schachtelungsebene wird erhöht, wenn die aufgerufene Funktion die Ausführung startet, und wird verringert, wenn die aufgerufene Funktion die Ausführung beendet. Benutzerdefinierte Funktionen können bis zu 32 Ebenen geschachtelt werden. Das Überschreiten der maximalen Schachtelungsebenen führt dazu, dass die gesamte aufrufende Funktionskette fehlschlägt. Jeder Verweis auf verwalteten Code aus einer Transact-SQL benutzerdefinierte Funktion zählt als eine Ebene für die Schachtelungsgrenze auf 32 Ebenen. Methoden, die in verwaltetem Code aufgerufen werden, werden nicht auf diesen Grenzwert angerechnet.

Verwenden der Sortierreihenfolge in CLR-Tabellenwertfunktionen

Wenn Sie die ORDER Klausel in CLR-Tabellenwertfunktionen verwenden, befolgen Sie die folgenden Richtlinien:

  • Sie müssen sicherstellen, dass die Ergebnisse immer in der angegebenen Reihenfolge sortiert werden. Wenn die Ergebnisse nicht in der angegebenen Reihenfolge angezeigt werden, generiert SQL Server beim Ausführen der Abfrage eine Fehlermeldung.

  • Wenn eine ORDER Klausel angegeben wird, muss die Ausgabe der Tabellenwertfunktion entsprechend der Sortierung der Spalte (explizit oder implizit) sortiert werden. Wenn die Spaltensortierung z. B. chinesisch ist, müssen die zurückgegebenen Ergebnisse gemäß den chinesischen Sortierregeln sortiert werden. (Die Sortierung wird entweder in der DDL für die Tabellenwertfunktion angegeben oder aus der Datenbanksortierung abgerufen.)

  • SQL Server überprüft bei der Rückgabe von Ergebnissen immer, ob die ORDER Klausel angegeben ist, unabhängig davon, ob der Abfrageprozessor sie zum Ausführen weiterer Optimierungen verwendet. Verwenden Sie die ORDER Klausel nur, wenn Sie wissen, dass sie für den Abfrageprozessor nützlich ist.

  • Der SQL Server-Abfrageprozessor nutzt die ORDER Klausel in den folgenden Fällen automatisch:

    • Fügen Sie Abfragen ein, bei denen die ORDER Klausel mit einem Index kompatibel ist.
    • ORDER BY Klauseln, die mit der ORDER Klausel vereinbar sind.
    • Aggregate, sofern GROUP BY dies mit ORDER der Klausel vereinbar ist.
    • DISTINCT Aggregate, bei denen die unterschiedlichen Spalten mit der ORDER Klausel kompatibel sind.

Die ORDER Klausel garantiert keine geordneten Ergebnisse, wenn eine SELECT Abfrage ausgeführt wird, es sei denn ORDER BY , sie wird auch in der Abfrage angegeben. Unter sys.function_order_columns (Transact-SQL) finden Sie Informationen zum Abfragen von Spalten, die in der Sortierreihenfolge für Tabellenwertfunktionen enthalten sind.

Metadaten

In der folgenden Tabelle sind die Systemkatalogsichten aufgeführt, die Sie verwenden können, um Metadaten zu benutzerdefinierten Funktionen zurückzugeben.

Systemansicht BESCHREIBUNG
sys.sql_Module Siehe Beispiel E im Abschnitt Beispiele.
sys.assembly_modules Zeigt Informationen zu benutzerdefinierten CLR-Funktionen an.
sys.parameters Zeigt Informationen zu den Parametern an, die in benutzerdefinierten Funktionen definiert sind.
sys.sql_Abhängigkeiten von Ausdrücken Zeigt die zugrunde liegenden Objekte an, auf die von einer Funktion verwiesen wird.

Erlaubnisse

Erfordert die Berechtigung in der Datenbank und ALTER die Berechtigung für das SchemaCREATE FUNCTION, in dem die Funktion erstellt wird. Wenn die Funktion einen benutzerdefinierten Typ angibt, ist die Berechtigung für den Typ erforderlich EXECUTE .

Beispiele

Weitere Beispiele und Leistungsüberlegungen zu UDFs finden Sie unter Erstellen benutzerdefinierter Funktionen (Datenbank-Engine).

Ein. Verwenden Sie eine benutzerdefinierte Skalarwertfunktion, die die ISO-Woche berechnet

Im folgenden Beispiel wird die benutzerdefinierte Funktion ISOweekerstellt. Diese Funktion verwendet ein Datumsargument und berechnet die ISO-Wochennummer. Damit diese Funktion korrekt berechnet werden kann, muss sie aufgerufen werden, SET DATEFIRST 1 bevor die Funktion aufgerufen wird.

Im Beispiel wird auch gezeigt, wie die EXECUTE AS-Klausel (Transact-SQL) verwendet wird, um den Sicherheitskontext anzugeben, in dem eine gespeicherte Prozedur ausgeführt werden kann. Im Beispiel gibt die Option an, dass die Prozedur CALLER im Kontext des Benutzers ausgeführt wird, der sie aufruft. Die anderen Optionen, die Sie angeben können, sind SELF, OWNERund user_name.

Hier ist der Funktionsaufruf. DATEFIRST ist auf 1 eingestellt.

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

Hier ist das Ergebnis.

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

B. Erstellen einer Inline-Tabellenwertfunktion

Im folgenden Beispiel wird eine Inline-Tabellenwertfunktion in der AdventureWorks2022-Datenbank zurückgegeben. Es werden drei Spalten ProductID, Nameund die Summe der Gesamtsummen seit Jahresbeginn pro Filiale für YTD Total jedes an die Filiale verkaufte Produkt zurückgegeben.

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

Führen Sie diese Abfrage aus, um die Funktion aufzurufen.

SELECT * FROM Sales.ufn_SalesByStore (602);

C. Erstellen einer Tabellenwertfunktion mit mehreren Anweisungen

Im folgenden Beispiel wird die Tabellenwertfunktion fn_FindReports(InEmpID) in der AdventureWorks2022 Datenbank erstellt. Wenn die Funktion mit einer gültigen Mitarbeiter-ID versorgt wird, gibt sie eine Tabelle zurück, die allen Mitarbeitern entspricht, die direkt oder indirekt an den Mitarbeiter berichten. Die Funktion verwendet einen rekursiven Common Table Expression (CTE), um die hierarchische Liste der Mitarbeiter zu erstellen. Weitere Informationen zu rekursiven CTEs finden Sie unter 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. Erstellen einer CLR-Funktion

Im Beispiel wird die CLR-Funktion len_s. Bevor die Funktion erstellt wird, wird die Assembly SurrogateStringFunction.dll in der lokalen Datenbank registriert.

Gilt für: SQL Server 2008 (10.0.x) SP 1 und höher.

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

Ein Beispiel für das Erstellen einer CLR-Tabellenwertfunktion finden Sie unter CLR Table-Valued Functions.

E. Zeigen Sie die Definition von benutzerdefinierten Funktionen an

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

Die Definition von Funktionen, die mit der ENCRYPTION Option erstellt wurden, kann nicht mit eingesehen sys.sql_moduleswerden. Es werden jedoch andere Informationen zu den verschlüsselten Funktionen angezeigt.

Verwandte Inhalte