CREATE FUNCTION (Transact-SQL)

  • Artikel

Aktualisiert: 14. April 2006

Erstellt eine benutzerdefinierte Funktion. Es handelt sich dabei um eine gespeicherte Transact-SQL- oder CLR-Routine (Common Language Runtime), die einen Wert zurückgibt. Mit benutzerdefinierten Funktionen können keine Aktionen ausgeführt werden, die den Status einer Datenbank ändern. Benutzerdefinierte Funktionen können, wie Systemfunktionen, aus einer Abfrage heraus aufgerufen werden. Skalarfunktionen können, wie gespeicherte Prozeduren, mit einer EXECUTE-Anweisung ausgeführt werden.

Benutzerdefinierte Funktionen werden mit ALTER FUNCTION geändert und mit DROP FUNCTION gelöscht.

Themenlink (Symbol)Transact-SQL-Syntaxkonventionen

Syntax

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

Inline Table-valued Functions
CREATE FUNCTION [ schema_name. ] function_name 
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type 
    [ = default ] } 
    [ ,...n ]
  ]
)
RETURNS TABLE
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    RETURN [ ( ] select_stmt [ ) ]
[ ; ]

Multistatement Table-valued Functions
CREATE FUNCTION [ schema_name. ] function_name 
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type 
    [ = default ] } 
    [ ,...n ]
  ]
)
RETURNS @return_variable TABLE < table_type_definition >
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    BEGIN 
                function_body 
        RETURN
    END
[ ; ]

CLR Functions
CREATE FUNCTION [ schema_name. ] function_name 
( { @parameter_name [AS] [ type_schema_name. ] parameter_data_type 
        [ = default ] } 
    [ ,...n ]
)
RETURNS { return_data_type | TABLE <clr_table_type_definition> }
    [ WITH <clr_function_option> [ ,...n ] ]
    [ AS ] EXTERNAL NAME <method_specifier>
[ ; ]

Method Specifier
<method_specifier>::=
    assembly_name.class_name.method_name

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

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

Table Type Definitions
<table_type_definition>:: = 
( { <column_definition> <column_constraint> 
  | <computed_column_definition> } 
        [ <table_constraint> ] [ ,...n ]
) 

<clr_table_type_definition>::= 
( { column_name data_type } [ ,...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 } 
}

Argumente

  • 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 für jedes Schema eindeutig sein.

    ms186755.note(de-de,SQL.90).gifHinweis:
    Auf den Funktionsnamen müssen Klammern folgen, selbst 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 1.024 Parameter haben. Der Benutzer muss beim Ausführen einer Funktion den Wert jedes deklarierten Parameters angeben (sofern kein Standardwert für den entsprechenden Parameter definiert ist).

    Geben Sie einen Parameternamen an, der mit dem Zeichen (@) beginnt. Der Parametername muss den Regeln für Bezeichner entsprechen. Parameter gelten lokal in der jeweiligen Funktion, d. h. dass Sie dieselben Parameternamen in anderen Funktionen verwenden können. Parameter können nur den Platz von Konstanten einnehmen. Sie können nicht anstelle von Tabellennamen, Spaltennamen oder Namen anderer Datenbankobjekte verwendet werden.

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

  • [ type_schema_name**.** ] parameter_data_type
    Der Parameterdatentyp und optional das Schema, zu dem der Datentyp gehört. Für Transact-SQL-Funktionen sind abgesehen vom timestamp-Datentyp alle Datentypen zulässig, einschließlich CLR-benutzerdefinierter Typen. Für CLR-Funktionen sind abgesehen von den text-, ntext-, image- und timestamp-Datentypen alle Datentypen zulässig, einschließlich CLR-benutzerdefinierter Typen. 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 SQL Server 2005-Datenbankmodul nach scalar_parameter_data_type in der folgenden Reihenfolge:

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

  • [ **=**default ]
    Ein Standardwert für den Parameter. Wenn ein Wert für default definiert ist, kann die Funktion ausgeführt werden, ohne dass ein Wert für diesen Parameter angegeben werden muss.

    ms186755.note(de-de,SQL.90).gifHinweis:
    Standardparameterwerte können nicht für CLR-Funktionen angegeben werden. Ausnahme: die varchar(max)- und varbinary(max)-Datentypen.

    Wenn ein Parameter der Funktion über einen Standardwert verfügt, muss beim Aufrufen der Funktion das DEFAULT-Schlüsselwort angegeben werden, um den Standardwert abzurufen. In diesem Punkt gibt es einen Unterschied zum Verwenden von Parametern in einer gespeicherten Prozedur. Fehlt im Aufruf einer gespeicherten Prozedur ein Parameter, der einen Standardwert hat, wird automatisch dieser Standardwert verwendet.

  • return_data_type
    Der Rückgabewert einer benutzerdefinierten Skalarfunktion. Für Transact-SQL-Funktionen sind abgesehen vom timestamp-Datentyp alle Datentypen, einschließlich CLR-benutzerdefinierter Typen, zulässig. Für CLR-Funktionen sind abgesehen von den text-, ntext-, image- und timestamp-Datentypen alle Datentypen, einschließlich benutzerdefinierter CLR-Typen, zulässig. Die nicht skalaren Typen cursor und table 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 keine Nebeneffekte erzeugen (z. B. Ändern einer Tabelle), den Wert der Funktion definiert. function_body wird nur in Skalarfunktionen sowie in Tabellenwertfunktionen mit mehreren Anweisungen verwendet.

    In Skalarfunktionen entspricht function_body einer Reihe von Transact-SQL-Anweisungen, die zu einem skalaren Wert ausgewertet werden.

    In Tabellenwertfunktionen mit mehreren Anweisungen entspricht function_body einer Reihe von Transact-SQL-Anweisungen, die eine TABLE-Rückgabevariable auffüllen.

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

  • TABLE
    Gibt an, dass der Rückgabewert der Tabellenwertfunktion eine Tabelle ist. Nur Konstanten und **@**local_variables können an Tabellenwertfunktionen übergeben werden.

    In Inline-Tabellenwertfunktionen wird der TABLE-Rückgabewert durch eine einzige SELECT-Anweisung definiert. Inlinefunktionen haben keine zugeordneten Rückgabevariablen.

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

  • select_stmt
    Die eine SELECT-Anweisung, die den Rückgabewert einer Inline-Tabellenwertfunktion definiert.

  • EXTERNAL NAME <method_specifier>, assembly_name.class_name.method_name
    Gibt an, welche Methode eine Assembly zum Binden der Funktion verwendet. assembly_name muss mit einer in SQL Server vorhandenen Assembly in der aktuellen Datenbank übereinstimmen. Die Sichtbarkeit der Assembly muss eingeschaltet sein. class_name muss ein gültiger SQL Server-Bezeichner und als Klasse in der Assembly vorhanden sein. Wenn die Klasse über einen mit einem Namespace qualifizierten Namen verfügt, der separate Namespaceteile durch einen Punkt (.) trennt, muss der Klassenname durch eckige Klammern ([]) oder Anführungszeichen ("") begrenzt werden. method_name muss ein gültiger SQL Server-Bezeichner sein und als eine statische Methode in der angegebenen Klasse vorhanden sein.

    ms186755.note(de-de,SQL.90).gifHinweis:
    Standardmäßig kann SQL Server keinen CLR-Code ausführen. Sie können Datenbankobjekte mit Verweisen auf Common Language Runtime-Module erstellen, ändern und löschen. Sie können diese Verweise in SQL Server jedoch erst ausführen, nachdem Sie die Option clr enabled aktiviert haben. Verwenden Sie zum Aktivieren dieser Option sp_configure.
  • <table_type_definition>, ( { <column_definition> <column_constraint> ,   | <computed_column_definition> } ,   [ <table_constraint> ] [ ,...n ], ) ,
    Definiert den Tabellendatentyp für eine Transact-SQL-Funktion. Die Tabellendeklaration schließt Spaltendefinitionen und Spalten- oder Tabelleneinschränkungen ein. Die Tabelle wird immer in der primären Dateigruppe abgelegt.
  • < clr_table_type_definition > , ( { column_namedata_type } [ ,...n ] ),
    Definiert die Tabellendatentypen für eine CLR-Funktion. Die Tabellendeklaration schließt nur Spaltennamen und Datentypen ein. Die Tabelle wird immer in der primären Dateigruppe abgelegt.

<function_option>::= and <clr_function_option>::=

Gibt an, dass die Funktion mindestens über eine der folgenden Optionen verfügen wird.

  • ENCRYPTION
    Gibt an, dass Datenbankmodul den ursprünglichen Text der CREATE FUNCTION-Anweisung in ein verborgenes Format konvertiert. Die Ausgabe der Verbergung ist nicht direkt in den Katalogsichten in SQL Server 2005 sichtbar. Benutzer, die keinen Zugriff auf Systemtabellen oder Datenbankdateien haben, können den verborgenen Text nicht abrufen. Der Text ist jedoch für Benutzer mit umfangreichen Privilegien verfügbar, die entweder auf die Systemtabellen über den DAC-Port oder direkt auf die Datenbankdateien zugreifen. Des Weiteren können Benutzer, die einen Debugger an den Serverprozess anfügen können, die ursprüngliche Prozedur zur Laufzeit vom Arbeitsspeicher abrufen. Weitere Informationen zum Zugreifen auf Systemmetadaten finden Sie unter Konfigurieren der Sichtbarkeit von Metadaten.

    Mit dieser Option verhindern Sie, dass die Funktion als Teil der SQL Server-Replikation veröffentlicht wird. Diese Option kann nicht für CLR-Funktionen angegeben werden.

  • SCHEMABINDING
    Gibt an, dass die Funktion an die Datenbankobjekte gebunden ist, auf die sie verweist. Diese Bedingung verhindert, dass die Funktion geändert wird, wenn andere schemagebundene Objekte auf sie verweisen.

    Die Bindung der Funktion an die Objekte, auf die sie verweist, wird nur bei einer der folgenden Aktionen entfernt:

    • Die Funktion wird gelöscht.
    • Die Funktion wird mithilfe der ALTER-Anweisung geändert, wobei die Option SCHEMABINDING nicht angegeben ist.

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

    • Die Funktion ist eine Transact-SQL-Funktion.
    • Die benutzerdefinierten Funktionen und Sichten, auf die die Funktion verweist, sind ebenfalls schemagebunden.
    • In der Funktion sind die Verweise auf Objekte mithilfe von zweiteiligen Namen angegeben.
    • Die Funktion und die Objekte, auf die sie verweist, gehören zu derselben Datenbank.
    • Der Benutzer, der die CREATE FUNCTION-Anweisung ausgeführt hat, besitzt REFERENCES-Berechtigungen für die Datenbankobjekte, auf die die Funktion verweist.

    SCHEMABINDING kann nicht für CLR-Funktionen oder für Funktionen angegeben werden, die auf Aliasdatentypen verweisen.

  • RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT
    Gibt das OnNULLCall-Attribut einer Skalarwertfunktion an. Wenn das Attribut nicht angegeben ist, wird standardmäßig CALLED ON NULL INPUT verwendet. Dies bedeutet, dass der Hauptteil der Funktion ausgeführt wird, selbst wenn NULL als ein Argument übergeben wird.

    Wenn RETURNS NULL ON NULL INPUT in einer CLR-Funktion angegeben wird, bedeutet dies, dass SQL Server NULL zurückgeben kann, wenn eines der ihm zugestellten Argumente NULL ist, ohne den eigentlichen Hauptteil der Funktion aufzurufen. Wenn die Methode einer in <method_specifier> angegebenen CLR-Funktion bereits über ein benutzerdefiniertes Attribut verfügt, das RETURNS NULL ON NULL INPUT anzeigt, die CREATE FUNCTION-Anweisung jedoch CALLED ON NULL INPUT anzeigt, hat die CREATE FUNCTION-Anweisung Vorrang. Das OnNULLCall-Attribut kann nicht für CLR-Tabellenwertfunktionen angegeben werden.

  • EXECUTE AS-Klausel
    Gibt den Sicherheitskontext an, in dem die benutzerdefinierte Funktion ausgeführt wird. Deshalb können Sie steuern, welches Benutzerkonto SQL Server verwendet, um Berechtigungen für eines der Datenbankobjekte, auf die in der Funktion verwiesen wird, zu überprüfen.

    ms186755.note(de-de,SQL.90).gifHinweis:
    EXECUTE AS kann nicht für benutzerdefinierte Inlinefunktionen angegeben werden.

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

< column_definition >::=

Definiert den Tabellendatentyp. Die Tabellendeklaration schließt Spaltendefinitionen und Einschränkungen ein. 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 für Bezeichner geltenden Regeln entsprechen und innerhalb der Tabelle eindeutig sein. column_name kann aus 1 bis 128 Zeichen bestehen.
  • data_type
    Gibt den Datentyp der Spalte an. Für Transact-SQL-Funktionen sind abgesehen von timestamp alle Datentypen zulässig, einschließlich benutzerdefinierter CLR-Typen. Für CLR-Funktionen sind abgesehen von text, ntext, image, char, varchar, varchar(max) und timestamp alle Datentypen zulässig, einschließlich benutzerdefinierter CLR-Typen. Der nicht skalare Typ cursor kann weder in Transact-SQL- noch in CLR-Funktionen als Spaltendatentyp angegeben werden.
  • DEFAULT constant_expression
    Gibt den Wert an, der für die Spalte zur Verfügung gestellt wird, wenn während einer Einfügung kein Wert explizit angegeben wird. constant_expression ist eine Konstante, NULL oder ein Systemfunktionswert. DEFAULT-Definitionen können auf alle Spalten angewendet werden, mit Ausnahme von Spalten mit der IDENTITY-Eigenschaft. DEFAULT kann nicht für CLR-Tabellenwertfunktionen angegeben werden.

  • COLLATE 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 Name der Windows-Sortierreihenfolge (Transact-SQL) und SQL-Sortierungsname (Transact-SQL).

    Mit der COLLATE-Klausel können Sie nur die Sortierungen von Spalten der Datentypen char, varchar, nchar und nvarchar ändern.

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

  • ROWGUIDCOL
    Gibt an, dass die neue Spalte eine Spalte mit für alle Zeilen global eindeutigen Bezeichnern ist. Nur eine uniqueidentifier-Spalte pro Tabelle kann als ROWGUIDCOL-Spalte gekennzeichnet werden. Die ROWGUIDCOL-Eigenschaft kann nur einer uniqueidentifier-Spalte zugewiesen werden.

    Die ROWGUIDCOL-Eigenschaft erzwingt keine Eindeutigkeit der in der Spalte gespeicherten Werte. Sie erzeugt auch nicht automatisch Werte für neue Zeilen, die in die Tabelle eingefügt werden. Um eindeutige Werte für jede Spalte zu erzeugen, verwenden Sie die NEWID-Funktion in INSERT-Anweisungen. Ein Standardwert kann angegeben werden; NEWID wird jedoch nicht als Standardwert unterstützt.

  • IDENTITY
    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 normalerweise zusammen mit PRIMARY KEY-Einschränkungen verwendet, um als eindeutiger Zeilenbezeichner für die Tabelle zu dienen. Die IDENTITY-Eigenschaft kann tinyint-, smallint-, int-, bigint-, decimal(p,0)- oder numeric(p,0)-Spalten zugewiesen werden. Es kann nur eine Identitätsspalte pro Tabelle erstellt werden. Gebundene Standardwerte und DEFAULT-Einschränkungen können nicht mit einer Identitätsspalte verwendet werden. Sie müssen entweder sowohl den Wert für seed als auch für increment oder keinen von beiden angeben. Wurden Ausgangswert und inkrementeller Wert nicht angegeben, ist der Standardwert (1,1).

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

    • seed
      Der ganzzahlige Wert, der der ersten Zeile in der Tabelle zugewiesen werden soll.
    • increment
      Der ganzzahlige Wert, der dem seed-Wert nachfolgende Zeilen in der Tabelle hinzugefügt werden soll.

< column_constraint >::= and < table_constraint>::=

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

  • NULL | NOT NULL
    Bestimmt, ob Nullwerte in der Spalte zulässig sind. NULL ist genau genommen keine Einschränkung, kann jedoch wie NOT NULL verwendet werden. NOT NULL kann nicht für CLR-Tabellenwertfunktionen angegeben werden.
  • PRIMARY KEY
    Eine Einschränkung, die Entitätsintegrität für eine angegebene Spalte durch 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.
  • UNIQUE
    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 haben. UNIQUE kann nicht für CLR-Tabellenwertfunktionen angegeben werden.

  • CLUSTERED | NONCLUSTERED
    Gibt an, dass ein gruppierter oder nicht gruppierter Index für die PRIMARY KEY- oder UNIQUE-Einschränkung erstellt wird. PRIMARY KEY-Einschränkungen verwenden CLUSTERED, und UNIQUE-Einschränkungen verwenden NONCLUSTERED.

    CLUSTERED kann nur für eine einzelne Einschränkung angegeben werden. Wenn neben CLUSTERED für eine UNIQUE-Einschränkung auch eine PRIMARY KEY-Einschränkung angegeben wird, verwendet die Einschränkung PRIMARY KEY den Wert NONCLUSTERED.

    CLUSTERED und NONCLUSTERED können nicht für CLR-Tabellenwertfunktionen angegeben werden.

  • CHECK
    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 nicht für CLR-Tabellenwertfunktionen angegeben werden.

    • logical_expression
      Ein logischer Ausdruck, der TRUE oder FALSE zurückgibt.

<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 KEY- oder UNIQUE-Index an. Weitere Informationen zu Indexoptionen finden Sie unter CREATE INDEX (Transact-SQL).

  • PAD_INDEX = { ON | OFF }
    Gibt das Auffüllen von Indizes an. Die Standardeinstellung ist OFF.
  • FILLFACTOR = fillfactor
    Gibt einen Prozentwert an, der dem Füllfaktor entspricht. Dieser Faktor legt fest, wie weit die Datenbankmodul die Blattebene jeder Indexseite während der Indexerstellung auffüllen soll. fillfactor muss ein ganzzahliger Wert zwischen 1 und 100 sein. Der Standard lautet 0.
  • IGNORE_DUP_KEY = { ON | OFF }
    Legt fest, welcher Fehler durch doppelte Schlüsselwerte beim Einfügen mehrerer Zeilen in einen eindeutigen gruppierten oder eindeutigen nicht gruppierten Index ausgelöst wird. Die Standardeinstellung ist OFF.
  • STATISTICS_NORECOMPUTE = { ON | OFF }
    Gibt an, ob Verteilungsstatistiken neu berechnet werden. Die Standardeinstellung ist OFF.
  • ALLOW_ROW_LOCKS = { ON | OFF }
    Gibt an, ob Zeilensperren zulässig sind. Die Standardeinstellung ist ON.
  • ALLOW_PAGE_LOCKS = { ON | OFF }
    Gibt an, ob Seitensperren zulässig sind. Die Standardeinstellung ist ON.

Hinweise

Eine benutzerdefinierte Funktion ist entweder eine Skalarwert- oder eine Tabellenwertfunktion. Eine Funktion ist eine Skalarwertfunktion, wenn in der RETURNS-Klausel einer der skalaren Datentypen angegeben ist. Eine Skalarwertfunktion kann mithilfe mehrerer Transact-SQL-Anweisungen definiert werden.

Eine Funktion ist eine Tabellenwertfunktion, wenn in der RETURNS-Klausel das TABLE-Schlüsselwort angegeben ist. Abhängig davon, wie der Hauptteil der Funktion definiert ist, wird eine Tabellenwertfunktion als Inlinefunktion oder als Funktion mit mehreren Anweisungen klassifiziert. Weitere Informationen finden Sie unter Benutzerdefinierte Tabellenwertfunktionen.

Die folgenden Anweisungen sind in einer Funktion zulässig:

  • Zuweisungsanweisungen
  • Anweisungen zur Ablaufsteuerung, mit Ausnahme von TRY...CATCH-Anweisungen
  • DECLARE-Anweisungen zum Definieren lokaler Datenbankvariablen und lokaler Cursor
  • 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 und geschlossen werden und deren Zuordnungen in der Funktion aufgehoben werden. Es sind nur FETCH-Anweisungen zulässig, die lokalen Variablen Werte mit der INTO-Klausel zuweisen. FETCH-Anweisungen, die Daten an den Client zurückgeben, sind nicht zulässig.
  • INSERT-, UPDATE- und DELETE-Anweisungen, die lokale table-Variablen ändern
  • EXECUTE-Anweisungen, die erweiterte gespeicherte Prozeduren aufrufen
  • Weitere Informationen finden Sie unter Erstellen von benutzerdefinierten Funktionen (Datenbankmodul).

Schachteln von benutzerdefinierten Funktionen

Benutzerdefinierte Funktionen können geschachtelt werden. Dies bedeutet, dass eine benutzerdefinierte Funktion eine andere aufrufen kann. Die Schachtelungsebene wird um eins erhöht, wenn die aufgerufene Funktion mit der Ausführung beginnt, und wird wieder um eins erniedrigt, wenn die aufgerufene Funktion die Ausführung beendet. Benutzerdefinierte Funktionen unterstützen bis zu 32 geschachtelte Ebenen. Ein Überschreiten der maximalen Schachtelungsebenen verursacht das Fehlschlagen der gesamten Funktionsaufrufskette.

ms186755.note(de-de,SQL.90).gifHinweis:
   Alle etwaigen Verweise auf verwalteten Code von einer benutzerdefinierten Transact-SQL-Funktion aus gelten, was das Maximum von 32 Schachtelungsebenen betrifft, als eine Ebene. Innerhalb von verwaltetem Code aufgerufene Methoden werden nicht auf dieses Limit angerechnet.

Funktionseigenschaften

In früheren Versionen von SQL Server wurden Funktionen nur als deterministische oder nicht deterministische Funktionen eingestuft. In SQL Server 2005 verfügen Funktionen über die im Folgenden aufgeführten Eigenschaften. Die Werte dieser Eigenschaften bestimmen, ob Funktionen in permanent berechneten oder indizierten berechneten Spalten verwendet werden können.

Eigenschaft Beschreibung Hinweise

IsDeterministic

Die Funktion ist deterministisch oder nicht deterministisch.

Lokaler Datenzugriff ist in deterministischen Funktionen zulässig. Funktionen, die immer dasselbe Ergebnis zurückgeben, wenn sie mit bestimmten Eingabewerten und mit demselben Datenbankstatus aufgerufen werden, werden beispielsweise als deterministisch bezeichnet.

IsPrecise

Die Funktion ist präzise oder unpräzise.

Unpräzise Funktionen enthalten Vorgänge wie Gleitkommatransaktionen.

IsSystemVerified

Die Präzisions- und Determinismuseigenschaften der Funktion können von SQL Server geprüft werden.

 

SystemDataAccess

Die Funktion greift auf Systemdaten (Systemkataloge oder virtuelle Systemtabellen) in der lokalen Instanz von SQL Server zu.

 

UserDataAccess

Die Funktion greift auf Benutzerdaten in der lokalen Instanz von SQL Server zu.

Schließt benutzerdefinierte Tabellen und temporäre Tabellen ein, jedoch keine Tabellenvariablen.

Die Präzisions- und Determinismuseigenschaften von Transact-SQL-Funktionen werden automatisch von SQL Server bestimmt. Weitere Informationen finden Sie unter Richtlinien zum Entwerfen von benutzerdefinierten Funktionen. Die Datenzugriffs- und Determinismuseigenschaften von CLR-Funktionen können vom Benutzer angegeben werden. Weitere Informationen finden Sie unter Overview of CLR Integration Custom Attributes.

Verwenden Sie OBJECTPROPERTYEX, um die aktuellen Werte für diese Eigenschaften anzuzeigen.

Indizieren berechneter Spalten, die eine benutzerdefinierte Funktion aufrufen

Eine berechnete Spalte, die eine benutzerdefinierte Funktion aufruft, kann in einem Index verwendet werden, sofern die benutzerdefinierte Funktion über folgende Eigenschaftswerte verfügt:

  • IsDeterministic = true
  • IsSystemVerified = true (es sei denn, es handelt sich um eine dauerhaft berechnete Spalte)
  • UserDataAccess = false
  • SystemDataAccess = false

Weitere Informationen finden Sie unter Erstellen von Indizes für berechnete Spalten.

Aufrufen erweiterter gespeicherter Prozeduren aus Funktionen

Eine erweiterte gespeicherte Prozedur kann, wenn sie aus einer Funktion heraus aufgerufen wird, keine Resultsets an den Client zurückgeben. Jede ODS-API, die Resultsets an den Client zurückgibt, gibt FAIL zurück. Eine erweiterte gespeicherte Prozedur kann eine Verbindung zurück zu einer Instanz von SQL Server herstellen. Diese Prozedur sollte allerdings nicht versuchen, Bestandteil der gleichen Transaktion zu werden, zu der die Funktion gehört, die die erweiterte gespeicherte Prozedur aufgerufen hat.

Ähnlich wie Aufrufe aus einem Batch oder einer gespeicherten Prozedur wird auch eine erweiterte gespeicherte Prozedur im Kontext des Windows-Sicherheitskontos ausgeführt, unter dem SQL Server ausgeführt wird. Der Besitzer der gespeicherten Prozedur sollte dies bedenken, wenn er anderen Benutzern für die gespeicherte Prozedur EXECUTE-Berechtigungen erteilt.

Funktionsaufruf

Skalarwertfunktionen können dort aufgerufen werden, wo Skalarausdrücke verwendet werden. Hierzu gehören berechnete Spalten und CHECK-Einschränkungsdefinitionen. Skalarwertfunktionen können auch mithilfe der EXECUTE-Anweisung ausgeführt werden. Skalarwertfunktionen müssen mindestens mit dem zweiteiligen Namen der Funktion aufgerufen werden. Weitere Informationen zu mehrteiligen Namen finden Sie unter Transact-SQL-Syntaxkonventionen (Transact-SQL). Tabellenwertfunktionen können dort aufgerufen werden, wo Tabellenausdrücke in der FROM-Klausel von SELECT-, INSERT-, UPDATE- oder DELETE-Anweisungen zulässig sind. Weitere Informationen finden Sie unter Ausführen benutzerdefinierter Funktionen (Datenbankmodul).

Verwenden von Parametern und Rückgabewerten in CLR-Funktionen

Wenn Parameter in einer CLR-Funktion angegeben werden, sollte es sich dabei um SQL Server-Typen handeln, wie weiter oben für scalar_parameter_data_type definiert. Weitere Informationen zum Vergleichen von SQL Server-Systemdatentypen mit CLR-Integrationsdatentypen oder .NET Framework Common Language Runtime-Datentypen finden Sie unter SQL Server Data Types and Their .NET Framework Equivalents.

Damit SQL Server auf die richtige Methode verweist, wenn es in einer Klasse überlastet ist, muss die in <method_specifier> angegebene Methode folgende Merkmale aufweisen:

  • Die Anzahl von Parametern wie in [ ,...n ].
  • Alle Parameter nach Wert erhalten, nicht nach Verweis.
  • Parametertypen verwenden, die mit den in der SQL Server-Funktion angegebenen kompatibel sind.

Wenn der Datenrückgabetyp der CLR-Funktion einen Tabellentyp (RETURNS TABLE) angibt, sollte der Datenrückgabetyp der Methode in <method_specifier> dem Typ IEnumerator oder IEnumerable entsprechen. Es wird zudem angenommen, dass die Schnittstelle vom Ersteller der Funktion implementiert wird. Im Gegensatz zu Transact-SQL-Funktionen können CLR-Funktionen keine PRIMARY KEY-, UNIQUE- oder CHECK-Einschränkungen in <table_type_definition> einschließen. Die Datentypen der Spalten, die in <table_type_definition> angegeben werden, müssen mit den Typen der entsprechenden Spalten des Resultsets übereinstimmen, das von der in <method_specifier> angegebenen Methode zur Ausführungszeit zurückgegeben wird. Diese Typprüfung wird zum Zeitpunkt der Funktionserstellung nicht durchgeführt.

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

Unzulässige SQL-Anweisungen

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

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

Anzeigen von Informationen zu Funktionen

Verwenden Sie zum Anzeigen der Definition benutzerdefinierter Transact-SQL-Funktionen die sys.sql_modules-Katalogsicht in der Datenbank, in der sich die Funktion befindet.

Beispiel:

USE AdventureWorks;
GO
SELECT definition, type 
FROM sys.sql_modules AS m
JOIN sys.objects AS o ON m.object_id = o.object_id 
    AND type IN ('FN', 'IF', 'TF');
GO
ms186755.note(de-de,SQL.90).gifHinweis:
Die Definition von Funktionen, die mithilfe der ENCRYPTION-Option erstellt wurden, können nicht mit sys.sql_modules angezeigt werden. Im Gegensatz dazu werden andere Informationen zu den verschlüsselten Funktionen jedoch angezeigt.

Verwenden Sie zum Anzeigen von Informationen zu benutzerdefinierten CRL-Funktionen die sys.assembly_modules-Katalogsicht in der Datenbank, in der sich die Funktion befindet.

Verwenden Sie zum Anzeigen von Informationen zu den in benutzerdefinierten Funktionen definierten Parametern die sys.parameters-Katalogsicht in der Datenbank, in der sich die Funktion befindet.

Um einen Bericht über die Objekte zu erhalten, auf die in einer Funktion verwiesen wird, verwenden Sie sys.sql_dependencies.

Berechtigungen

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

Beispiele

A. Verwenden einer benutzerdefinierten Skalarwertfunktion, die die ISO-Woche berechnet

Im folgenden Beispiel wird die benutzerdefinierte Funktion ISOweek erstellt. Diese Funktion nimmt ein Datumsargument und berechnet die Nummer der ISO-Woche. Damit diese Funktion richtig rechnet, muss SET DATEFIRST 1 aufgerufen werden, bevor die Funktion aufgerufen wird.

Das Beispiel zeigt auch das Verwenden der EXECUTE AS-Klausel zur Angabe des Sicherheitskontexts, in dem eine gespeicherte Prozedur ausgeführt werden kann. In dem gezeigten Beispiel gibt die Option CALLER an, dass die Prozedur im Kontext des Benutzers, der die Prozedur aufruft, ausgeführt wird. Zusätzlich können Sie die Optionen SELF, OWNER und user_name angeben.

Im Folgenden wird der Funktionsaufruf aufgeführt. Beachten Sie, dass DATEFIRST auf 1 festgelegt ist.

USE AdventureWorks;
GO
IF OBJECT_ID (N'dbo.ISOweek', N'FN') IS NOT NULL
    DROP FUNCTION dbo.ISOweek;
GO
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';

Dies ist das Resultset.

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

B. Erstellen einer Inline-Tabellenwertfunktion

Das folgende Beispiel gibt eine Inline-Tabellenwertfunktion zurück. Die Funktion gibt drei Spalten ProductID, Name und das Aggregat der gesamten Verkäufe des Jahres (nach Filiale sortiert) als YTD Total für jedes Produkt zurück, das an die Filiale verkauft wurde.

USE AdventureWorks;
GO
IF OBJECT_ID (N'Sales.ufn_SalesByStore', N'IF') IS NOT NULL
    DROP FUNCTION Sales.ufn_SalesByStore;
GO
CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int)
RETURNS TABLE
AS
RETURN 
(
    SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'YTD Total'
    FROM Production.Product AS P 
      JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID
      JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID
    WHERE SH.CustomerID = @storeid
    GROUP BY P.ProductID, P.Name
);
GO

Rufen Sie die Funktion mit dieser Abfrage auf.

SELECT * FROM Sales.ufn_SalesByStore (602);

C. Erstellen einer Tabellenwertfunktion mit mehreren Anweisungen

Im folgenden Beispiel wird die Tabellenwertfunktion fn_FindReports(InEmpID) erstellt. Wenn der Funktion eine gültige Mitarbeiter-ID bereitgestellt wird, gibt sie eine Tabelle zurück, die allen Mitarbeitern entspricht, die dem Mitarbeiter entweder direkt oder indirekt unterstellt sind. Die Funktion verwendet eine rekursive Abfrage (Common Table Expression, CTE), um eine hierarchische Mitarbeiterliste zu erstellen. Weitere Informationen zu rekursiven CTEs finden Sie unter WITH common_table_expression (Transact-SQL).

USE AdventureWorks;
GO
IF OBJECT_ID (N'dbo.ufn_FindReports', N'TF') IS NOT NULL
    DROP FUNCTION dbo.ufn_FindReports;
GO
CREATE FUNCTION dbo.ufn_FindReports (@InEmpID INTEGER)
RETURNS @retFindReports TABLE 
(
    EmployeeID int primary key NOT NULL,
    Name nvarchar(255) NOT NULL,
    Title nvarchar(50) NOT NULL,
    EmployeeLevel int NOT NULL,
    Sort nvarchar (255) NOT NULL
)
--Returns a result set that lists all the employees who report to the 
--specific employee directly or indirectly.*/
AS
BEGIN
   WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort) AS
    (SELECT CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName),
        e.Title,
        e.EmployeeID,
        1,
        CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName)
     FROM HumanResources.Employee AS e
          JOIN Person.Contact AS c ON e.ContactID = c.ContactID 
     WHERE e.EmployeeID = @InEmpID
   UNION ALL
     SELECT CONVERT(Varchar(255), REPLICATE ('| ' , EmployeeLevel) +
        c.FirstName + ' ' + c.LastName),
        e.Title,
        e.EmployeeID,
        EmployeeLevel + 1,
        CONVERT (Varchar(255), RTRIM(Sort) + '| ' + FirstName + ' ' + 
                 LastName)
     FROM HumanResources.Employee as e
          JOIN Person.Contact AS c ON e.ContactID = c.ContactID
          JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
    )
-- copy the required columns to the result of the function 
   INSERT @retFindReports
   SELECT EmployeeID, Name, Title, EmployeeLevel, Sort
   FROM DirectReports 
   RETURN
END;
GO
-- Example invocation
SELECT EmployeeID, Name, Title, EmployeeLevel
FROM dbo.ufn_FindReports(109)
ORDER BY Sort;
GO

D. Erstellen einer CLR-Funktion

Im folgenden Beispiel wird angenommen, dass die Beispiele für SQL Server Database Engine im Standardspeicher des lokalen Computers installiert sind und die Beispielanwendung "StringManipulate.csproj" kompiliert wurde. Weitere Informationen finden Sie unter Zeichenfolgendarstellung mit ergänzenden Zeichen.

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

DECLARE @SamplesPath nvarchar(1024);
-- You may have to modify the value of the this variable if you have
--installed the sample someplace other than the default location.
SELECT @SamplesPath = REPLACE(physical_name, 'Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf', 'Microsoft SQL Server\90\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 zum Erstellen einer CLR-Tabellenwertfunktionen finden Sie unter CLR Table-Valued Functions.

Siehe auch

Verweis

ALTER FUNCTION (Transact-SQL)
DROP FUNCTION (Transact-SQL)
OBJECTPROPERTYEX (Transact-SQL)
sys.sql_modules (Transact-SQL)
sys.assembly_modules (Transact-SQL)
EXECUTE (Transact-SQL)
EVENTDATA (Transact-SQL)

Andere Ressourcen

Benutzerdefinierte Funktionen (Datenbankmodul)
CLR User-Defined Functions

Hilfe und Informationen

Informationsquellen für SQL Server 2005

Änderungsverlauf

Version Verlauf

14. April 2006
Neuer Inhalt:
  • Im Abschnitt mit den Hinweisen wurde bei unter dem Funktionsaufruf ein Hinweis hinzugefügt, dass Skalarwertfunktionen mindestens mit dem zweiteiligen Namen der Funktion aufgerufen werden müssen.

05. Dezember 2005
Geänderter Inhalt:
  • Die Definition der Option ENCRYPTION wurde verdeutlicht.