Condividi tramite

  • Articolo

ALTER FUNCTION (Transact-SQL)

Viene modificata una funzione Transact-SQL o CLR esistente, creata in precedenza tramite l'istruzione CREATE FUNCTION, senza modificare le autorizzazioni o alterare le funzioni, le stored procedure e i trigger associati.

Icona di collegamento a un argomentoConvenzioni della sintassi Transact-SQL

Sintassi

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

Argomenti

  • schema_name
    Nome dello schema a cui appartiene la funzione definita dall'utente.

  • function_name
    Funzione definita dall'utente che si desidera modificare.

    [!NOTA]

    È necessario inserire le parentesi dopo il nome della funzione anche se non viene specificato alcun parametro.

  • **@**parameter_name
    Parametro della funzione definita dall'utente. È possibile dichiarare uno o più parametri.

    Una funzione può avere al massimo 2.100 parametri. Il valore di ciascun parametro dichiarato deve essere specificato dall'utente quando viene eseguita la funzione, a meno che non venga definito un valore predefinito per tale parametro.

    Specificare un nome di parametro utilizzando come primo carattere il simbolo di chiocciola (@). I nomi di parametro devono essere conformi alle regole per gli identificatori. I parametri sono locali rispetto alla funzione. È pertanto possibile utilizzare gli stessi nomi di parametro in altre funzioni. I parametri possono rappresentare solo costanti, non nomi di tabella, di colonna o di altri oggetti del database.

    [!NOTA]

    ANSI_WARNINGS non viene applicata quando vengono passati parametri a una stored procedure, una funzione definita dall'utente oppure in caso di dichiarazione e impostazione delle variabili in un'istruzione batch. Se, ad esempio, la variabile viene definita come char(3) e quindi impostata su un valore maggiore di tre caratteri, i dati verranno troncati alla dimensione definita e l'istruzione INSERT o UPDATE ha esito positivo.

  • [ type_schema_name. ] parameter_data_type
    Tipo di dati del parametro e, facoltativamente, lo schema a cui appartiene. Per le funzioni Transact-SQL sono consentiti tutti i tipi di dati, compresi i tipi CLR definiti dall'utente, eccetto il tipo di dati timestamp. Per le funzioni CLR sono consentiti tutti i tipi di dati, compresi i tipi CLR definiti dall'utente, eccetto i tipi di dati text, ntext, image e timestamp. Non è possibile specificare i tipi non scalari cursor e table come tipo di dati del parametro nelle funzioni Transact-SQL o CLR.

    Se type_schema_name viene omesso, SQL Server 2005 Database Engine (Motore di database) ricerca parameter_data_type in base all'ordine seguente:

    • Schema contenente i nomi dei tipi di dati di sistema di SQL Server.

    • Schema predefinito dell'utente corrente nel database corrente.

    • Schema dbo nel database corrente.

  • [ **=**default ]
    Valore predefinito per il parametro. Se un valore viene impostato su default, è possibile eseguire la funzione senza specificare un valore per il parametro.

    [!NOTA]

    È possibile specificare valori predefiniti per i parametri delle funzioni CLR ad eccezione dei tipi di dati varchar(max) e varbinary(max).

    Se a un parametro della funzione è associato un valore predefinito, quando si richiama la funzione per ottenere il valore predefinito è necessario specificare la parola chiave "default". Questa funzionalità risulta diversa dall'utilizzo di parametri con valore predefinito nelle stored procedure in cui l'omissione del parametro implica l'utilizzo del valore predefinito.

  • return_data_type
    Valore restituito di una funzione scalare definita dall'utente. Per le funzioni Transact-SQL sono consentiti tutti i tipi di dati, compresi i tipi CLR definiti dall'utente, eccetto il tipo di dati timestamp. Per le funzioni CLR sono consentiti tutti i tipi di dati, compresi i tipi CLR definiti dall'utente, eccetto i tipi di dati text, ntext, image e timestamp. Non è possibile specificare i tipi non scalari cursor e table come tipo di dati restituito nelle funzioni Transact-SQL o CLR.

  • function_body
    Specifica che il valore della funzione è definito da una serie di istruzioni Transact-SQL, le quali non hanno alcun effetto di rilievo, ad esempio la modifica di una tabella. function_body è utilizzato solo in funzioni scalari e in funzioni a istruzioni multiple con valori di tabella.

    Nelle funzioni scalari, function_body corrisponde a una serie di istruzioni Transact-SQL che in combinazione restituiscono un valore scalare.

    Nelle funzioni a istruzioni multiple con valori di tabella function_body corrisponde a una serie di istruzioni Transact-SQL che popolano una variabile TABLE restituita.

  • scalar_expression
    Specifica che la funzione scalare restituisce un valore scalare.

  • TABLE
    Specifica che il valore restituito della funzione con valori di tabella è una tabella. Alle funzioni con valori di tabella è possibile passare solo costanti e variabili **@**local_variables.

    Nelle funzioni inline con valori di tabella, il valore restituito TABLE viene definito tramite una sola istruzione SELECT. Alle funzioni inline non sono associate variabili restituite.

    Nelle funzioni composte da più istruzioni con valori di tabella **@**return_variable è una variabile TABLE utilizzata per l'archiviazione e l'accumulo delle righe da restituire come valore della funzione. È possibile specificare **@**return_variable solo per le funzioni Transact-SQL e non per le funzioni CLR.

  • select-stmt
    Istruzione SELECT che definisce il valore restituito di una funzione inline con valori di tabella.

  • EXTERNAL NAME <method_specifier>assembly_name.class_name.method_name
    Specifica il metodo di associazione tra un assembly e la funzione. assembly_name deve corrispondere a un assembly esistente in SQL Server nel database corrente con la visibilità attivata. class_name deve essere un identificatore SQL Server valido, nonché esistere come classe nell'assembly. Se la classe dispone di un nome qualificato con lo spazio dei nomi che utilizza un punto (.) per separare le varie parti dello spazio dei nomi, il nome della classe deve essere delimitato da parentesi quadre ([]) oppure da virgolette (""). method_name deve essere un identificatore di SQL Server valido, nonché esistere come metodo statico nella classe specificata.

    [!NOTA]

    Per impostazione predefinita, SQL Server non può eseguire il codice CLR. È possibile creare, modificare ed eliminare gli oggetti di database che fanno riferimento a moduli CLR. È tuttavia possibile eseguire questi riferimenti in SQL Server solo dopo aver attivato l'opzione clr enabled. Per abilitare questa opzione, utilizzare sp_configure.

  • <table_type_definition>( { <column_definition> <column_constraint>   | <computed_column_definition> }   [ <table_constraint> ] [ ,...n ])
    Definisce il tipo di dati della tabella per una funzione Transact-SQL. La dichiarazione di tabella include definizioni di colonna, nonché vincoli di colonna o tabella.

  • < clr_table_type_definition > ( { column_namedata_type } [ ,...n ] )
    Definisce i tipi di dati della tabella per una funzione CLR. La dichiarazione di tabella include solo nomi di colonna e tipi di dati.

<function_option>::= e <clr_function_option>::=

Specifica che la funzione avrà una o più opzioni seguenti.

  • ENCRYPTION
    Indica che Motore di database eseguirà la crittografia delle colonne della vista del catalogo contenenti il testo dell'istruzione ALTER FUNCTION. Tramite il parametro ENCRYPTION è possibile evitare la pubblicazione della funzione come parte della replica di SQL Server. Non è possibile specificare l'opzione ENCRYPTION per le funzioni CLR.

  • SCHEMABINDING
    Specifica che la funzione è associata agli oggetti di database a cui fa riferimento. Questa condizione impedisce la modifica della funzione se altri oggetti associati allo schema vi fanno riferimento.

    L'associazione della funzione agli oggetti cui fa riferimento viene rimossa solo quando viene eseguita una delle azioni seguenti:

    • La funzione viene eliminata.

    • La funzione viene modificata tramite l'istruzione ALTER senza specificare l'opzione SCHEMABINDING.

    Per un elenco delle condizioni che devono essere soddisfatte per consentire l'associazione di una funzione a uno schema, vedere CREATE FUNCTION (Transact-SQL).

  • RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT
    Viene specificato l'attributo OnNULLCall di una funzione a valori scalari. Se omesso, viene utilizzata l'opzione CALLED ON NULL INPUT per impostazione predefinita. Ciò significa che viene eseguito il corpo della funzione anche se come argomento viene passato NULL.

    Se in una funzione CLR si specifica l'opzione RETURNS NULL ON NULL INPUT, SQL Server restituisce NULL se uno qualsiasi degli argomenti ricevuti è NULL senza effettivamente richiamare il corpo della funzione. Se per il metodo specificato in <method_specifier> è stato definito un attributo personalizzato impostato su RETURNS NULL ON NULL INPUT, ma l'istruzione ALTER FUNCTION include CALLED ON NULL INPUT, l'istruzione ALTER FUNCTION risulta prioritaria. Non è possibile specificare l'attributo OnNULLCall per le funzioni CLR con valori di tabella.

  • Clausola EXECUTE AS
    Specifica il contesto di protezione nel quale viene eseguita la funzione definita dall'utente. Sarà pertanto possibile controllare l'account utente utilizzato da SQL Server per convalidare le autorizzazioni per qualsiasi oggetto di database a cui la funzione fa riferimento.

    [!NOTA]

    Non è possibile specificare la clausola EXECUTE AS per le funzioni inline definite dall'utente.

    Per ulteriori informazioni, vedere Clausola EXECUTE AS (Transact-SQL).

< column_definition >::=

Definisce il tipo di dati della tabella. La dichiarazione di tabella include definizioni di colonna e vincoli. Per le funzioni CLR è possibile specificare solo column_name e data_type.

  • column_name
    Nome di una colonna della tabella. I nomi di colonna devono essere conformi alle regole per gli identificatori ed essere univoci nella tabella. column_name può essere contenere da 1 a 128 caratteri.

  • data_type
    Specifica il tipo di dati della colonna. Per le funzioni Transact-SQL sono consentiti tutti i tipi di dati, compresi i tipi CLR definiti dall'utente, eccetto il tipo di dati timestamp. Per le funzioni CLR sono consentiti tutti i tipi di dati, compresi i tipi CLR definiti dall'utente, eccetto i tipi di dati text, ntext, image, char, varchar, varchar(max) e timestamp. Non è possibile specificare il tipo non scalare cursor come tipo di dati della colonna nelle funzioni Transact-SQL o CLR.

  • DEFAULT constant_expression
    Specifica il valore definito per la colonna quando un valore non viene specificato in modo esplicito durante un inserimento. constant_expression è una costante, NULL oppure un valore della funzione di sistema. Le definizioni dell'opzione DEFAULT possono essere applicate a qualsiasi colonna eccetto quelle che includono la proprietà IDENTITY. Non è possibile specificare l'opzione DEFAULT per le funzioni CLR con valori di tabella.

  • COLLATE collation_name
    Specifica le regole di confronto per la colonna. Se omesso, alla colonna vengono assegnate le regole di confronto predefinite del database. È possibile utilizzare nomi di regole di confronto di Windows o SQL. Per un elenco completo e per ulteriori informazioni, vedere Windows_collation_name (Transact-SQL) e Nome delle regole di confronto di SQL Server (Transact-SQL).

    La clausola COLLATE consente di modificare le regole di confronto solo delle colonne con tipi di dati char, varchar, nchar e nvarchar.

    Non è possibile specificare l'opzione COLLATE per le funzioni CLR con valori di tabella.

  • ROWGUIDCOL
    Indica che la nuova colonna è un identificatore univoco di riga globale. È possibile designare come colonna ROWGUIDCOL una sola colonna di tipo uniqueidentifier per ogni tabella. La proprietà ROWGUIDCOL può essere assegnata solo a una colonna uniqueidentifier.

    La proprietà ROWGUIDCOL non impone l'univocità dei valori archiviati nella colonna e non genera automaticamente valori per le nuove righe inserite nella tabella. Per generare valori univoci per ogni colonna, utilizzare la funzione NEWID nelle istruzioni INSERT. È possibile specificare un valore predefinito. Non è tuttavia possibile specificare l'opzione NEWID come valore predefinito.

  • IDENTITY
    Indica che la nuova colonna è una colonna Identity. Quando si aggiunge una nuova riga alla tabella, SQL Server assegna alla colonna un valore univoco e incrementale. Le colonne Identity vengono comunemente utilizzate in combinazione con vincoli PRIMARY KEY e svolgono la funzione di identificatore di riga univoco per la tabella. È possibile assegnare la proprietà IDENTITY a colonne di tipo tinyint, smallint, int, bigint, decimal(p,0) o numeric(p,0). È possibile creare una sola colonna Identity per ogni tabella. Non è possibile associare valori predefiniti e vincoli DEFAULT alle colonne Identity. È necessario specificare sia il valore per seed che il valore per increment oppure nessuno dei due valori. In questo secondo caso, il valore predefinito è (1,1).

    Non è possibile specificare l'opzione IDENTITY per le funzioni CLR con valori di tabella.

    • seed
      Intero da assegnare alla prima riga della tabella.

    • increment
      Intero da aggiungere al valore seed per le righe successive della tabella.

< column_constraint >::= e < table_constraint>::=

Definisce il vincolo per una colonna o tabella specificata. Per le funzioni CLR l'unico tipo di vincolo consentito è NULL. I vincoli denominati non sono consentiti.

  • NULL | NOT NULL
    Determina se i valori NULL sono supportati nella colonna. L'opzione NULL non è esattamente un vincolo, ma può essere specificata allo stesso modo di NOT NULL. Non è possibile specificare l'opzione NOT NULL per le funzioni CLR con valori di tabella.

  • PRIMARY KEY
    Vincolo che impone l'integrità di entità per una o più colonne specificate tramite un indice univoco. Nelle funzioni definite dall'utente con valori di tabella, il vincolo PRIMARY KEY può essere creato solo su una colonna per tabella. Non è possibile specificare il vincolo PRIMARY KEY per le funzioni CLR con valori di tabella.

  • UNIQUE
    Vincolo che impone l'integrità di entità per una o più colonne specificate tramite un indice univoco. Una tabella può includere più vincoli UNIQUE. Non è possibile specificare il vincolo UNIQUE per le funzioni CLR con valori di tabella.

  • CLUSTERED | NONCLUSTERED
    Definisce la creazione di un indice cluster o non cluster per il vincolo PRIMARY KEY o UNIQUE. I vincoli PRIMARY KEY utilizzano l'opzione CLUSTERED, mentre i vincoli UNIQUE utilizzano l'opzione NONCLUSTERED.

    L'opzione CLUSTERED può essere specificata solo per un vincolo. Se per un vincolo UNIQUE si specifica CLUSTERED e viene specificato anche un vincolo PRIMARY KEY, quest'ultimo utilizza l'opzione NONCLUSTERED.

    Non è possibile specificare le opzioni CLUSTERED e NON CLUSTERED per le funzioni CLR con valori di tabella.

  • CHECK
    Vincolo che impone l'integrità di dominio tramite la limitazione dei valori che è possibile inserire in una o più colonne. Non è possibile specificare i vincoli CHECK per le funzioni CLR con valori di tabella.

    • logical_expression
      Espressione logica che restituisce TRUE o FALSE.

<computed_column_definition>::=

Specifica una colonna calcolata. Per ulteriori informazioni sulle colonne calcolate, vedere CREATE TABLE (Transact-SQL).

  • column_name
    Nome della colonna calcolata.

  • computed_column_expression
    Espressione che definisce il valore di una colonna calcolata.

<index_option>::=

Specifica le opzioni per l'indice PRIMARY KEY o UNIQUE. Per ulteriori informazioni sulle opzioni per gli indici, vedere CREATE INDEX (Transact-SQL).

  • PAD_INDEX = { ON | OFF }
    Specifica il riempimento dell'indice. Il valore predefinito è OFF.

  • FILLFACTOR = fillfactor
    Specifica una percentuale che indica il livello di riempimento del livello foglia di ogni pagina di indice applicato dal Motore di database durante la creazione o la modifica dell'indice. fillfactor deve essere un intero compreso tra 1 e 100. Il valore predefinito è 0.

  • IGNORE_DUP_KEY = { ON | OFF }
    Specifica l'errore restituito quando un'operazione di inserimento tenta di inserire valori di chiave duplicati in un indice univoco. L'opzione IGNORE_DUP_KEY viene applicata solo alle operazioni di inserimento eseguite dopo la creazione o la ricostruzione dell'indice. Il valore predefinito è OFF.

  • STATISTICS_NORECOMPUTE = { ON | OFF }
    Specifica se devono essere ricalcolate le statistiche di distribuzione. Il valore predefinito è OFF.

  • ALLOW_ROW_LOCKS = { ON | OFF }
    Specifica se sono consentiti blocchi di riga. Il valore predefinito è ON.

  • ALLOW_PAGE_LOCKS = { ON | OFF }
    Specifica se sono consentiti blocchi a livello di pagina. Il valore predefinito è ON.

Osservazioni

L'istruzione ALTER FUNCTION non può essere utilizzata per trasformare una funzione a valori scalari in una funzione con valori di tabella e viceversa, né per convertire una funzione inline in una funzione a più istruzioni e viceversa. Non può inoltre essere utilizzata per convertire una funzione Transact-SQL in una funzione CLR e viceversa.

Nella definizione di una funzione Transact-SQL definita dall'utente non è possibile includere le istruzioni di Service Broker seguenti:

  • BEGIN DIALOG CONVERSATION

  • END CONVERSATION

  • GET CONVERSATION GROUP

  • MOVE CONVERSATION

  • RECEIVE

  • SEND

Autorizzazioni

È necessario disporre dell'autorizzazione ALTER per la funzione o lo schema. Se per la funzione viene specificato un tipo definito dall'utente, è necessario disporre dell'autorizzazione EXECUTE per tale tipo.