Export (0) Print
Expand All

ALTER FUNCTION (Azure SQL Database)

This topic is OBSOLETE. You can find the most current version in the SQL 14 Transact-SQL Reference.

ImportantImportant
This topic is not maintained. For the current version, see ALTER FUNCTION.

Alters an existing Transact-SQL function that was previously created by executing the CREATE FUNCTION statement, without changing permissions and without affecting any dependent functions, stored procedures, or triggers. 

Syntax Conventions (Azure SQL Database)

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
[ ; ]



Function Options<function_option>::= 
{
    [ SCHEMABINDING ]
  | [ 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 ]
)<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 } 
}

This syntax diagram demonstrates the supported arguments and options in Microsoft Microsoft Azure SQL Database.

Microsoft Azure SQL Database does not support the following function types with the ALTER FUNCTION statement:

  • CLR scalar functions

  • CLR table-valued functions

Microsoft Azure SQL Database supports scalar functions, inline table-valued functions, and multistatement table-valued functions with the ALTER FUNCTION statement as is. For such functions, function_body is subject to the general Microsoft Azure SQL Database Transact-SQL data manipulation language (DML) limitations. 

For multistatement table-valued functions, the following data definition languages (DDLs) are subject to the general Microsoft Azure SQL Database Transact-SQL DDL limitations:

  • table_type_definition

  • column_definition

  • column_constraint

  • computed_column_definition

  • index_option

For more information about the arguments and the ALTER FUNCTION statement, see ALTER FUNCTION in SQL Server Books Online.

Community Additions

Show:
© 2014 Microsoft