Freigeben über

  • Artikel

ALTER FUNCTION (Transact-SQL)

Ändert eine vorhandene Transact-SQL- oder CLR-Funktion, die vorher durch Ausführen der CREATE FUNCTION-Anweisung erstellt wurde, ohne Berechtigungen zu ändern und ohne abhängige Funktionen, gespeicherte Prozeduren oder Trigger zu beeinflussen.

Themenlink (Symbol)Transact-SQL-Syntaxkonventionen

Syntax

Scalar Functions
ALTER 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
ALTER 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
ALTER 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
ALTER 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>::=
    assembly_name.class_name.method_nameFunction 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
    Die zu ändernde benutzerdefinierte Funktion.

    HinweisHinweis

    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 2.100 Parameter haben. Der Benutzer muss beim Ausführen einer Funktion den Wert jedes deklarierten Parameters angeben (sofern kein Standardwert für den betreffenden 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. Die gleichen 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 Namen anderer Datenbankobjekte verwendet werden.

    HinweisHinweis

    ANSI_WARNINGS wird beim Übergeben von Parametern in einer gespeicherten Prozedur oder 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, einschließlich benutzerdefinierter CLR-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 Parameterdatentyp angegeben werden.

    Wenn type_schema_name nicht angegeben ist, sucht SQL Server 2005 Database Engine (Datenbankmodul) nach 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 default-Wert definiert ist, kann die Funktion ausgeführt werden, ohne dass ein Wert für diesen Parameter angegeben werden muss.

    HinweisHinweis

    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. das Ä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 an, dass die Skalarfunktion einen skalaren Wert 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 einzelne 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.

    HinweisHinweis

    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.

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

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

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

  • ENCRYPTION
    Gibt an, dass die Database Engine (Datenbankmodul) die Katalogsichtspalten, die den Text der ALTER FUNCTION-Anweisung enthalten, verschlüsselt. Mit ENCRYPTION verhindern Sie, dass die Funktion als Teil der SQL Server-Replikation veröffentlicht wird. ENCRYPTION kann nicht für CLR-Funktionen angegeben werden.

  • SCHEMABINDING
    Gibt an, dass die Funktion an die Datenbankobjekte gebunden ist, auf die sie verweist. Dadurch wird 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 Liste der Bedingungen, die erfüllt sein müssen, um eine Funktion an ein Schema zu binden, finden Sie unter CREATE FUNCTION (Transact-SQL).

  • 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 in <method_specifier> angegebene Methode bereits über ein benutzerdefiniertes Attribut verfügt, das RETURNS NULL ON NULL INPUT anzeigt, die ALTER FUNCTION-Anweisung jedoch CALLED ON NULL INPUT anzeigt, hat die ALTER 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 zu überprüfen, auf die in der Funktion verwiesen wird.

    HinweisHinweis

    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, einschließlich benutzerdefinierter CLR-Typen, zulässig. Für CLR-Funktionen sind abgesehen von text, ntext, image, char, varchar, varchar(max) und timestamp alle Datentypen, einschließlich benutzerdefinierter CLR-Typen, zulässig. 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 finden Sie unter Name der Windows-Sortierreihenfolge (Transact-SQL) und SQL Server-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 angegeben 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 auf einer Spalte pro Tabelle erstellt werden. PRIMARY KEY kann nicht für CLR-Tabellenwertfunktionen angegeben werden.

  • UNIQUE
    Eine Einschränkung, die Entitätsintegrität durch einen eindeutigen Index für eine bestimmte Spalte (oder Spalten) bietet. 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-Einschränkung 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 Database Engine (Datenbankmodul) die Blattebene jeder Indexseite während der Indexerstellung oder -änderung auffüllen soll. fillfactor muss ein ganzzahliger Wert zwischen 1 und 100 sein. Die Standardeinstellung ist 0.

  • IGNORE_DUP_KEY = { ON | OFF }
    Gibt die Fehlermeldung an, wenn ein Einfügevorgang versucht, doppelte Schlüsselwerte in einen eindeutigen Index einzufügen. Die Option IGNORE_DUP_KEY gilt nur für Einfügevorgänge nach dem Erstellen oder Neuerstellen des Index. 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

ALTER FUNCTION kann nicht verwendet werden, um eine Skalarwertfunktion in eine Tabellenwertfunktion oder umgekehrt zu ändern. Ebenso kann ALTER FUNCTION nicht verwendet werden, um eine Inlinefunktion in eine Funktion mit mehreren Anweisungen oder umgekehrt zu ändern. ALTER FUNCTION kann nicht zum Ändern einer Transact-SQL-Funktion in eine CLR-Funktion und umgekehrt verwendet werden.

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

Berechtigungen

Erfordert die ALTER-Berechtigung auf der Funktion oder auf dem Schema. Wenn die Funktion einen benutzerdefinierten Typ angibt, wird die EXECUTE-Berechtigung für den Typ benötigt.