CREATE FUNCTION (Windows Azure SQL Database)
Creates a user-defined function. This is a saved Transact-SQL routine that returns a value. User-defined functions cannot be used to perform actions that modify the database state. User-defined functions, like system functions, can be invoked from a query. Scalar functions can be executed by using an EXECUTE statement, as with stored procedures.
Syntax Conventions (Windows Azure SQL Database)
Scalar Functions
CREATE FUNCTION [ schema_name. ] function_name( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type
[ =default ] [ READONLY ] }
[ ,...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 ] [ READONLY ] }
[ ,...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 ] [READONLY] }
[ ,...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 function types in Microsoft Windows Azure SQL Database.
Windows Azure SQL Database does not support creating CLR scalar functions and CLR table-valued functions by using the CREATE FUNCTION statement. Windows Azure SQL Database does support creating scalar functions, inline table-valued functions, and multistatement table-valued functions without any modification.
For scalar functions and multistatement table-valued functions, function_body is subject to the general Windows Azure SQL Database Transact-SQL data manipulation language (DML) limitations. The following list shows the DDLs, which are subject to the general Windows Azure SQL Database Transact-SQL data definition language (DDL) limitations:
-
table_type_definition
-
column_definition
-
column_constraint
-
computed_column_definition
-
index_option
For more information about the arguments and the CREATE FUNCTION statement, see CREATE FUNCTION (Transact-SQL) in SQL Server Books Online.
