CREATE Part (Transact-SQL)

  • Article
  • 27 minutes to read

Applies to: yesSQL Server (all supported versions) YesAzure SQL Database YesAzure SQL Managed Instance

Creates a user-defined function. A user-defined function is a Transact-SQL or common linguistic communication runtime (CLR) routine that accepts parameters, performs an activeness, such as a complex adding, and returns the result of that activity as a value. The render value can either exist a scalar (single) value or a table. Use this statement to create a reusable routine that can be used in these ways:

  • In Transact-SQL statements such equally SELECT

  • In applications calling the function

  • In the definition of another user-defined part

  • To parameterize a view or improve the functionality of an indexed view

  • To ascertain a column in a table

  • To ascertain a CHECK constraint on a cavalcade

  • To supervene upon a stored procedure

  • Use an inline function as a filter predicate for a security policy

Annotation

  • The integration of .Internet Framework CLR into SQL Server is discussed in this topic. CLR integration does not apply to Azure SQL Database.
  • For Azure Synapse Analytics, run across CREATE FUNCTION (Azure Synapse Analytics).

Topic link icon Transact-SQL Syntax Conventions

Syntax

              -- Transact-SQL Scalar Role Syntax CREATE [ OR Change ] Function [ schema_name. ] function_name ( [ { @parameter_name [ Equally ][ type_schema_name. ] parameter_data_type  [ = default ] [ READONLY ] }     [ ,...n ]   ] ) RETURNS return_data_type     [ WITH <function_option> [ ,...due north ] ]     [ AS ]     BEGIN         function_body         Render scalar_expression     Terminate [ ; ]                          
              -- Transact-SQL Inline Tabular array-Valued Role Syntax CREATE [ OR Modify ] Part [ schema_name. ] function_name ( [ { @parameter_name [ Every bit ] [ type_schema_name. ] parameter_data_type     [ = default ] [ READONLY ] }     [ ,...north ]   ] ) RETURNS Table     [ WITH <function_option> [ ,...n ] ]     [ As ]     Return [ ( ] select_stmt [ ) ] [ ; ]                          
              -- Transact-SQL Multi-Statement Table-Valued Function Syntax CREATE [ OR Change ] 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 ]     Brainstorm         function_body         RETURN     END [ ; ]                          
              -- Transact-SQL Function Clauses <function_option>::= {     [ ENCRYPTION ]   | [ SCHEMABINDING ]   | [ RETURNS NULL ON Aught INPUT | Chosen ON Aught INPUT ]   | [ EXECUTE_AS_Clause ]   | [ INLINE = { ON | OFF }] }  <table_type_definition>:: = ( { <column_definition> <column_constraint>   | <computed_column_definition> }     [ <table_constraint> ] [ ,...northward ] ) <column_definition>::= {     { column_name data_type }     [ [ DEFAULT constant_expression ]       [ COLLATE collation_name ] | [ ROWGUIDCOL ]     ]     | [ IDENTITY [ (seed , increase ) ] ]     [ <column_constraint> [ ...n ] ] }  <column_constraint>::= {     [ Nix | NOT NULL ]     { Master Primal | UNIQUE }       [ Amassed | NONCLUSTERED ]       [ WITH FILLFACTOR = fillfactor         | WITH ( < index_option > [ , ...n ] )       [ ON { filegroup | "default" } ]   | [ CHECK ( logical_expression ) ] [ ,...northward ] }  <computed_column_definition>::= column_name AS computed_column_expression  <table_constraint>::= {     { PRIMARY KEY | UNIQUE }       [ Amassed | 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 } }                          
              -- CLR Scalar Function Syntax CREATE [ OR ALTER ] Role [ schema_name. ] function_name ( { @parameter_name [AS] [ type_schema_name. ] parameter_data_type     [ = default ] }     [ ,...n ] ) RETURNS { return_data_type }     [ WITH <clr_function_option> [ ,...n ] ]     [ As ] EXTERNAL NAME <method_specifier> [ ; ]                          
              -- CLR Tabular array-Valued Office Syntax CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name ( { @parameter_name [AS] [ type_schema_name. ] parameter_data_type     [ = default ] }     [ ,...due north ] ) RETURNS Tabular array <clr_table_type_definition>     [ WITH <clr_function_option> [ ,...n ] ]     [ ORDER ( <order_clause> ) ]     [ As ] EXTERNAL Name <method_specifier> [ ; ]                          
              -- CLR Part Clauses <order_clause> ::= {    <column_name_in_clr_table_type_definition>    [ ASC | DESC ] } [ ,...n]  <method_specifier>::=     assembly_name.class_name.method_name  <clr_function_option>::= }     [ RETURNS NULL ON Cypher INPUT | CALLED ON Zilch INPUT ]   | [ EXECUTE_AS_Clause ] }  <clr_table_type_definition>::= ( { column_name data_type } [ ,...n ] )                          
              -- In-Retentivity OLTP: Syntax for natively compiled, scalar user-defined function CREATE [ OR Modify ] Role [ schema_name. ] function_name  ( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type     [ NULL | Not Naught ] [ = default ] [ READONLY ] }     [ ,...northward ]   ] ) RETURNS return_data_type      WITH <function_option> [ ,...n ]     [ AS ]     Begin ATOMIC WITH (set_option [ ,... due north ])         function_body         RETURN scalar_expression     END  <function_option>::= {   |  NATIVE_COMPILATION   |  SCHEMABINDING   | [ EXECUTE_AS_Clause ]   | [ RETURNS NULL ON NULL INPUT | CALLED ON Null INPUT ] }

Arguments

OR ALTER
Applies to: SQL Server (Starting with SQL Server 2022 (13.x) SP1) and Azure SQL Database

Conditionally alters the function only if it already exists.

Annotation

Optional [OR Alter] syntax for CLR is available starting with SQL Server 2022 (13.x) SP1 CU1.

schema_name
Is the name of the schema to which the user-defined function belongs.

function_name
Is the proper noun of the user-defined role. Function names must comply with the rules for identifiers and must exist unique within the database and to its schema.

Note

Parentheses are required after the function proper noun even if a parameter is not specified.

@parameter_name
Is a parameter in the user-defined function. One or more parameters can be alleged.

A function can have a maximum of two,100 parameters. The value of each declared parameter must be supplied past the user when the role is executed, unless a default for the parameter is divers.

Specify a parameter name past using an at sign (@) every bit the starting time character. The parameter name must comply with the rules for identifiers. Parameters are local to the function; the same parameter names can be used in other functions. Parameters can take the identify only of constants; they cannot be used instead of tabular array names, column names, or the names of other database objects.

Note

ANSI_WARNINGS is not honored when yous pass parameters in a stored procedure, user-defined function, or when you declare and set variables in a batch argument. For case, if a variable is defined as char(3), and then set to a value larger than three characters, the data is truncated to the defined size and the INSERT or UPDATE statement succeeds.

[ type_schema_name. ] parameter_data_type
Is the parameter data type, and optionally the schema to which it belongs. For Transact-SQL functions, all data types, including CLR user-divers types and user-defined table types, are immune except the timestamp data type. For CLR functions, all data types, including CLR user-divers types, are allowed except text, ntext, image, user-divers table types and timestamp data types. The nonscalar types, cursor and table, cannot be specified as a parameter data type in either Transact-SQL or CLR functions.

If type_schema_name is not specified, the Database Engine looks for the scalar_parameter_data_type in the following society:

  • The schema that contains the names of SQL Server system data types.
  • The default schema of the current user in the current database.
  • The dbo schema in the current database.

[ =default ]
Is a default value for the parameter. If a default value is defined, the function tin be executed without specifying a value for that parameter.

Note

Default parameter values can be specified for CLR functions except for the varchar(max) and varbinary(max) data types.

When a parameter of the function has a default value, the keyword DEFAULT must be specified when the office is chosen to retrieve the default value. This behavior is dissimilar from using parameters with default values in stored procedures in which omitting the parameter also implies the default value. Nevertheless, the DEFAULT keyword is non required when invoking a scalar role past using the EXECUTE statement.

READONLY
Indicates that the parameter cannot be updated or modified within the definition of the function. READONLY is required for user-defined table type parameters (TVPs), and cannot exist used for any other parameter blazon.

return_data_type
Is the return value of a scalar user-defined office. For Transact-SQL functions, all data types, including CLR user-defined types, are immune except the timestamp information type. For CLR functions, all information types, including CLR user-defined types, are allowed except the text, ntext, image, and timestamp data types. The nonscalar types, cursor and table, cannot exist specified equally a return data type in either Transact-SQL or CLR functions.

function_body
Specifies that a serial of Transact-SQL statements, which together practise not produce a side effect such as modifying a tabular array, define the value of the function. function_body is used only in scalar functions and multi-statement table-valued functions (MSTVFs).

In scalar functions, function_body is a serial of Transact-SQL statements that together evaluate to a scalar value.

In MSTVFs, function_body is a series of Transact-SQL statements that populate a TABLE render variable.

scalar_expression
Specifies the scalar value that the scalar function returns.

Table
Specifies that the return value of the table-valued function (TVF) is a table. Merely constants and @local_variables tin exist passed to TVFs.

In inline TVFs, the TABLE return value is defined through a single SELECT statement. Inline functions exercise non have associated return variables.

In MSTVFs, @return_variable is a TABLE variable, used to store and accumulate the rows that should be returned as the value of the function. @return_variable can be specified only for Transact-SQL functions and not for CLR functions.

select_stmt
Is the single SELECT statement that defines the render value of an inline tabular array-valued part (TVF).

ORDER (<order_clause>)
Specifies the society in which results are being returned from the table-valued function. For more information, meet the section, "Using Sort Guild in CLR Table-valued Functions", later in this topic.

EXTERNAL Proper name <method_specifier> assembly_name.class_name.method_name
Applies to: SQL Server ( SQL Server 2008 SP1 and later)

Specifies the assembly and method to which the created function proper name shall refer.

  • assembly_name - must match a value in the proper name column of SELECT * FROM sys.assemblies;.

    This is the proper name that was used on the CREATE ASSEMBLY argument.

  • class_name - must match a value in the assembly_name column of SELECT * FROM sys.assembly_modules;.

    Often the value contains an embedded catamenia or dot. In such cases the Transact-SQL syntax requires that the value be divisional with a pair of direct brackets [], or with a pair of double quotation marks "".

  • method_name - must match a value in the method_name cavalcade of SELECT * FROM sys.assembly_modules;.

    The method must be static.

In a typical example, for MyFood.DLL, in which all types are in the MyFood namespace, the EXTERNAL NAME value could exist: MyFood.[MyFood.MyClass].MyStaticMethod

Note

By default, SQL Server cannot execute CLR code. You can create, change, and drop database objects that reference common language runtime modules; even so, you cannot execute these references in SQL Server until yous enable the clr enabled option. To enable this option, use sp_configure. This option is not available in a contained database.

<table_type_definition> ( { <column_definition> <column_constraint>| <computed_column_definition> } [ <table_constraint> ] [ ,...due north ] )
Defines the table data type for a Transact-SQL office. The table declaration includes column definitions and column or table constraints. The table is always put in the primary filegroup.

< clr_table_type_definition > ( { column_name**data_type } [ ,...n ] )
Applies to: SQL Server ( SQL Server 2008 SP1 and later) and Azure SQL Database (Preview in some regions).

Defines the table data types for a CLR function. The table proclamation includes only column names and information types. The table is always put in the primary filegroup.

Naught | NOT NULL
Supported only for natively compiled, scalar user-defined functions. For more information, meet Scalar User-Defined Functions for In-Retentivity OLTP.

NATIVE_COMPILATION
Indicates whether a user-defined role is natively compiled. This argument is required for natively compiled, scalar user-defined functions.

BEGIN ATOMIC WITH
Supported only for natively compiled, scalar user-divers functions, and is required. For more than information, see Atomic Blocks.

SCHEMABINDING
The SCHEMABINDING statement is required for natively compiled, scalar user-defined functions.

EXECUTE Equally
EXECUTE AS is required for natively compiled, scalar user-divers functions.

<function_option>::= and <clr_function_option>::=
Specifies that the function will have 1 or more than of the post-obit options.

ENCRYPTION
Applies to: SQL Server ( SQL Server 2008 SP1 and later)

Indicates that the Database Engine will convert the original text of the CREATE FUNCTION argument to an obfuscated format. The output of the obfuscation is non directly visible in any itemize views. Users that have no access to system tables or database files cannot call back the obfuscated text. However, the text will be bachelor to privileged users that can either access organisation tables over the DAC port or directly admission database files. Also, users that tin can attach a debugger to the server process can retrieve the original procedure from memory at runtime. For more than information about accessing arrangement metadata, see Metadata Visibility Configuration.

Using this selection prevents the function from being published equally function of SQL Server replication. This option cannot be specified for CLR functions.

SCHEMABINDING
Specifies that the function is spring to the database objects that it references. When SCHEMABINDING is specified, the base of operations objects cannot be modified in a way that would touch on the role definition. The function definition itself must commencement exist modified or dropped to remove dependencies on the object that is to be modified.

The binding of the function to the objects information technology references is removed only when ne of the following actions occurs:

  • The function is dropped.
  • The function is modified by using the ALTER statement with the SCHEMABINDING option not specified.

A role tin can exist schema bound only if the following conditions are truthful:

  • The part is a Transact-SQL function.
  • The user-defined functions and views referenced by the function are also schema-bound.
  • The objects referenced past the office are referenced using a two-office name.
  • The function and the objects information technology references belong to the same database.
  • The user who executed the CREATE Function argument has REFERENCES permission on the database objects that the function references.

RETURNS Aught ON NULL INPUT | Called ON NULL INPUT
Specifies the OnNULLCall aspect of a scalar office. If not specified, CALLED ON Zippo INPUT is implied past default. This means that the office torso executes even if Zero is passed as an argument.

If RETURNS NULL ON Nix INPUT is specified in a CLR part, it indicates that SQL Server tin can return NULL when any of the arguments it receives is NULL, without actually invoking the body of the function. If the method of a CLR function specified in <method_specifier> already has a custom attribute that indicates RETURNS Zero ON NULL INPUT, simply the CREATE Role argument indicates Called ON NULL INPUT, the CREATE Part statement takes precedence. The OnNULLCall attribute cannot be specified for CLR table-valued functions.

EXECUTE AS
Specifies the security context under which the user-defined function is executed. Therefore, you can control which user account SQL Server uses to validate permissions on any database objects that are referenced by the part.

Note

EXECUTE Equally cannot be specified for inline tabular array-valued functions.

For more than information, see EXECUTE AS Clause (Transact-SQL).

INLINE = { ON | OFF }
Applies to: SQL Server (Starting with SQL Server 2022 (15.ten)) and Azure SQL Database.

Specifies whether this scalar UDF should be inlined or non. This clause applies only to scalar user-defined functions. The INLINE clause is not mandatory. If INLINE clause is not specified, it is automatically set to ON or OFF based on whether the UDF is inlineable. If INLINE = ON is specified but the UDF is constitute to exist non-inlineable, an fault will be thrown. For more data, see Scalar UDF Inlining.

< column_definition >::=
Defines the table data type. The table announcement includes column definitions and constraints. For CLR functions, only column_name and data_type tin can be specified.

column_name
Is the name of a cavalcade in the table. Column names must comply with the rules for identifiers and must exist unique in the table. column_name tin consist of one through 128 characters.

data_type
Specifies the cavalcade information type. For Transact-SQL functions, all data types, including CLR user-defined types, are allowed except timestamp. For CLR functions, all data types, including CLR user-defined types, are allowed except text, ntext, image, char, varchar, varchar(max), and timestamp.The nonscalar type cursor cannot exist specified as a cavalcade information type in either Transact-SQL or CLR functions.

DEFAULT constant_expression
Specifies the value provided for the column when a value is not explicitly supplied during an insert. constant_expression is a constant, NULL, or a system function value. DEFAULT definitions tin be applied to any column except those that have the IDENTITY property. DEFAULT cannot be specified for CLR tabular array-valued functions.

COLLATE collation_name
Specifies the collation for the column. If non specified, the column is assigned the default collation of the database. Collation name tin be either a Windows collation name or a SQL collation name. For a list of and more information most collations, see Windows Collation Name (Transact-SQL) and SQL Server Collation Name (Transact-SQL).

The COLLATE clause tin be used to alter the collations only of columns of the char, varchar, nchar, and nvarchar data types.

Notation

COLLATE cannot be specified for CLR table-valued functions.

ROWGUIDCOL
Indicates that the new column is a row globally unique identifier cavalcade. Only one uniqueidentifier cavalcade per table tin be designated equally the ROWGUIDCOL column. The ROWGUIDCOL property can be assigned only to a uniqueidentifier column.

The ROWGUIDCOL property does not enforce uniqueness of the values stored in the column. It as well does not automatically generate values for new rows inserted into the table. To generate unique values for each cavalcade, use the NEWID function on INSERT statements. A default value can be specified; however, NEWID cannot be specified every bit the default.

IDENTITY
Indicates that the new cavalcade is an identity cavalcade. When a new row is added to the table, SQL Server provides a unique, incremental value for the column. Identity columns are typically used together with PRIMARY KEY constraints to serve as the unique row identifier for the table. The IDENTITY property tin can be assigned to tinyint, smallint, int, bigint, decimal(p,0), or numeric(p,0) columns. Only one identity column can be created per table. Bound defaults and DEFAULT constraints cannot exist used with an identity column. You lot must specify both the seed and increment or neither. If neither is specified, the default is (ane,one).

IDENTITY cannot exist specified for CLR table-valued functions.

seed
Is the integer value to exist assigned to the starting time row in the table.

increase
Is the integer value to add to the seed value for successive rows in the table.

< column_constraint >::= and < table_constraint>::=
Defines the constraint for a specified column or table. For CLR functions, the only constraint type allowed is Nix. Named constraints are not immune.

NULL | Non Nil
Determines whether naught values are immune in the cavalcade. NULL is not strictly a constraint but can exist specified simply like Not Aught. Not NULL cannot be specified for CLR table-valued functions.

Primary KEY
Is a constraint that enforces entity integrity for a specified column through a unique alphabetize. In tabular array-valued user-divers functions, the PRIMARY KEY constraint can exist created on only 1 column per table. Master KEY cannot be specified for CLR table-valued functions.

UNIQUE
Is a constraint that provides entity integrity for a specified column or columns through a unique index. A table can accept multiple UNIQUE constraints. UNIQUE cannot be specified for CLR tabular array-valued functions.

CLUSTERED | NONCLUSTERED
Indicate that a amassed or a nonclustered index is created for the Master Primal or UNIQUE constraint. PRIMARY Cardinal constraints employ CLUSTERED, and UNIQUE constraints utilize NONCLUSTERED.

Clustered tin can be specified for only i constraint. If Clustered is specified for a UNIQUE constraint and a PRIMARY KEY constraint is also specified, the PRIMARY KEY uses NONCLUSTERED.

CLUSTERED and NONCLUSTERED cannot be specified for CLR table-valued functions.

Check
Is a constraint that enforces domain integrity by limiting the possible values that tin be entered into a column or columns. Cheque constraints cannot be specified for CLR table-valued functions.

logical_expression
Is a logical expression that returns TRUE or Fake.

<computed_column_definition>::=
Specifies a computed cavalcade. For more information about computed columns, see CREATE TABLE (Transact-SQL).

column_name
Is the name of the computed column.

computed_column_expression
Is an expression that defines the value of a computed cavalcade.

<index_option>::=
Specifies the index options for the Chief Primal or UNIQUE index. For more information well-nigh index options, see CREATE Index (Transact-SQL).

PAD_INDEX = { ON | OFF }
Specifies alphabetize padding. The default is OFF.

FILLFACTOR = fillfactor
Specifies a percentage that indicates how full the Database Engine should make the leaf level of each index folio during alphabetize creation or modify. fillfactor must be an integer value from 1 to 100. The default is 0.

IGNORE_DUP_KEY = { ON | OFF }
Specifies the error response when an insert operation attempts to insert duplicate key values into a unique index. The IGNORE_DUP_KEY option applies but to insert operations later on the index is created or rebuilt. The default is OFF.

STATISTICS_NORECOMPUTE = { ON | OFF }
Specifies whether distribution statistics are recomputed. The default is OFF.

ALLOW_ROW_LOCKS = { ON | OFF }
Specifies whether row locks are allowed. The default is ON.

ALLOW_PAGE_LOCKS = { ON | OFF }
Specifies whether page locks are allowed. The default is ON.

All-time Practices

If a user-defined function is not created with the SCHEMABINDING clause, changes that are made to underlying objects can touch the definition of the function and produce unexpected results when information technology is invoked. We recommend that you implement one of the following methods to ensure that the function does not become outdated considering of changes to its underlying objects:

  • Specify the WITH SCHEMABINDING clause when you are creating the part. This ensures that the objects referenced in the function definition cannot be modified unless the office is also modified.
  • Execute the sp_refreshsqlmodule stored procedure after modifying any object that is specified in the definition of the function.

Information Types

If parameters are specified in a CLR function, they should be SQL Server types every bit defined previously for scalar_parameter_data_type. For information near comparing SQL Server organization information types to CLR integration information types or .Cyberspace Framework common linguistic communication runtime data types, meet Mapping CLR Parameter Data.

For SQL Server to reference the correct method when information technology is overloaded in a class, the method indicated in <method_specifier> must take the post-obit characteristics:

  • Receive the same number of parameters as specified in [ ,...northward ].
  • Receive all the parameters past value, non by reference.
  • Employ parameter types that are uniform with those specified in the SQL Server office.

If the return data blazon of the CLR role specifies a table type (RETURNS TABLE), the return data type of the method in <method_specifier> should be of blazon IEnumerator or IEnumerable, and it is assumed that the interface is implemented past the creator of the function. Unlike Transact-SQL functions, CLR functions cannot include PRIMARY KEY, UNIQUE, or Check constraints in <table_type_definition>. The data types of columns specified in <table_type_definition> must match the types of the corresponding columns of the upshot set returned by the method in <method_specifier> at execution time. This type-checking is not performed at the time the function is created.

For more information about how to program CLR functions, see CLR User-Divers Functions.

General Remarks

Scalar functions tin can exist invoked where scalar expressions are used. This includes computed columns and CHECK constraint definitions. Scalar functions tin can besides be executed past using the EXECUTE statement. Scalar functions must be invoked past using at least the two-part name of the function (<schema>.<office>). For more data about multipart names, see Transact-SQL Syntax Conventions (Transact-SQL). Tabular array-valued functions can be invoked where table expressions are allowed in the FROM clause of SELECT, INSERT, UPDATE, or DELETE statements. For more information, see Execute User-defined Functions.

Interoperability

The following statements are valid in a function:

  • Assignment statements.
  • Command-of-Flow statements except Try...Take hold of statements.
  • DECLARE statements defining local data variables and local cursors.
  • SELECT statements that contain select lists with expressions that assign values to local variables.
  • Cursor operations referencing local cursors that are alleged, opened, closed, and deallocated in the office. Just FETCH statements that assign values to local variables using the INTO clause are allowed; FETCH statements that return data to the customer are not immune.
  • INSERT, UPDATE, and DELETE statements modifying local table variables.
  • EXECUTE statements calling extended stored procedures.

For more than information, encounter Create User-defined Functions (Database Engine).

Computed Column Interoperability

Functions accept the following properties. The values of these properties decide whether functions can be used in computed columns that can be persisted or indexed.

Property Description Notes
IsDeterministic Function is deterministic or nondeterministic. Local information access is allowed in deterministic functions. For case, functions that always return the aforementioned result any time they are chosen by using a specific set of input values and with the same state of the database would exist labeled deterministic.
IsPrecise Office is precise or imprecise. Imprecise functions contain operations such as floating point operations.
IsSystemVerified The precision and determinism backdrop of the part tin be verified by SQL Server.
SystemDataAccess Function accesses organization data (system catalogs or virtual system tables) in the local example of SQL Server.
UserDataAccess Function accesses user information in the local case of SQL Server. Includes user-defined tables and temp tables, but not table variables.

The precision and determinism properties of Transact-SQL functions are determined automatically past SQL Server. The data access and determinism properties of CLR functions can be specified by the user. For more information, run into Overview of CLR Integration Custom Attributes.

To display the electric current values for these properties, apply OBJECTPROPERTYEX.

Important

Functions must exist created with SCHEMABINDING to be deterministic.

A computed column that invokes a user-divers function can be used in an index when the user-divers function has the following property values:

  • IsDeterministic = truthful
  • IsSystemVerified = true (unless the computed column is persisted)
  • UserDataAccess = fake
  • SystemDataAccess = false

For more information, run across Indexes on Computed Columns.

Calling Extended Stored Procedures from Functions

The extended stored procedure, when it is called from inside a function, cannot return result sets to the client. Any ODS APIs that return outcome sets to the customer volition return FAIL. The extended stored procedure could connect back to an instance of SQL Server; yet, it should not effort to join the aforementioned transaction as the function that invoked the extended stored process.

Similar to invocations from a batch or stored procedure, the extended stored procedure will be executed in the context of the Windows security account under which SQL Server is running. The owner of the stored process should consider this when giving EXECUTE permission on information technology to users.

Limitations and Restrictions

User-defined functions cannot be used to perform actions that modify the database state.

User-defined functions cannot contain an OUTPUT INTO clause that has a table as its target.

The post-obit Service Banker statements cannot be included in the definition of a Transact-SQL user-defined part:

  • BEGIN DIALOG Conversation
  • Finish Chat
  • GET CONVERSATION GROUP
  • Movement Conversation
  • RECEIVE
  • Send

User-divers functions can be nested; that is, i user-defined role can call another. The nesting level is incremented when the called function starts execution, and decremented when the called function finishes execution. User-defined functions can be nested up to 32 levels. Exceeding the maximum levels of nesting causes the whole calling function concatenation to fail. Whatsoever reference to managed code from a Transact-SQL user-divers office counts as one level against the 32-level nesting limit. Methods invoked from within managed code do not count against this limit.

Using Sort Order in CLR Table-valued Functions

When using the Order clause in CLR table-valued functions, follow these guidelines:

  • You must ensure that results are always ordered in the specified order. If the results are not in the specified club, SQL Server will generate an error message when the query is executed.

  • If an Society clause is specified, the output of the table-valued role must exist sorted according to the collation of the column (explicit or implicit). For example, if the column collation is Chinese (either specified in the DDL for the table-valued function or obtained from the database collation), the returned results must exist sorted co-ordinate to Chinese sorting rules.

  • The ORDER clause, if specified, is always verified by SQL Server while returning results, whether or not it is used past the query processor to perform further optimizations. Just use the Lodge clause if you know it is useful to the query processor.

  • The SQL Server query processor takes advantage of the ORDER clause automatically in following cases:

    • Insert queries where the ORDER clause is compatible with an index.
    • Society BY clauses that are compatible with the ORDER clause.
    • Aggregates, where GROUP By is compatible with Lodge clause.
    • Singled-out aggregates where the singled-out columns are compatible with the ORDER clause.

The Club clause does not guarantee ordered results when a SELECT query is executed, unless Order By is also specified in the query. See sys.function_order_columns (Transact-SQL) for information on how to query for columns included in the sort-club for table-valued functions.

The following table lists the system catalog views that you can use to return metadata almost user-defined functions.

Organization View Description
sys.sql_modules See example E in the Examples department below.
sys.assembly_modules Displays data about CLR user-divers functions.
sys.parameters Displays data near the parameters defined in user-defined functions.
sys.sql_expression_dependencies Displays the underlying objects referenced by a part.

Permissions

Requires CREATE Office permission in the database and Change permission on the schema in which the function is being created. If the function specifies a user-defined type, requires EXECUTE permission on the type.

Examples

A. Using a scalar-valued user-divers office that calculates the ISO calendar week

The post-obit example creates the user-defined function ISOweek. This function takes a date argument and calculates the ISO week number. For this function to calculate correctly, SET DATEFIRST 1 must exist invoked before the part is called.

The example also shows using the EXECUTE Equally clause to specify the security context in which a stored process can be executed. In the example, the option CALLER specifies that the procedure will be executed in the context of the user that calls it. The other options that you can specify are SELF, OWNER, and user_name.

Hither is the function call. Notice that DATEFIRST is gear up to i.

              CREATE FUNCTION dbo.ISOweek (@DATE datetime) RETURNS int WITH EXECUTE AS CALLER AS BEGIN     DECLARE @ISOweek int;     SET @ISOweek= DATEPART(wk,@Date)+i         -DATEPART(wk,Bandage(DATEPART(yy,@DATE) every bit CHAR(4))+'0104'); --Special cases: Jan 1-iii may belong to the previous twelvemonth     IF (@ISOweek=0)         SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@Appointment)-1             Equally CHAR(four))+'12'+ Cast(24+DATEPART(DAY,@DATE) As CHAR(2)))+i; --Special case: Dec 29-31 may belong to the adjacent year     IF ((DATEPART(mm,@DATE)=12) AND         ((DATEPART(dd,@Engagement)-DATEPART(dw,@Engagement))>= 28))     Fix @ISOweek=ane;     RETURN(@ISOweek); Terminate; Go SET DATEFIRST 1; SELECT dbo.ISOweek(Catechumen(DATETIME,'12/26/2004',101)) Every bit 'ISO Week';

Here is the effect set.

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

B. Creating an inline table-valued function

The following example returns an inline table-valued function in the AdventureWorks2012 database. It returns three columns ProductID, Name and the amass of year-to-date totals by store as YTD Total for each production sold to the store.

              CREATE Office Sales.ufn_SalesByStore (@storeid int) RETURNS TABLE As Return (     SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'Total'     FROM Product.Product AS P     JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID     JOIN Sales.SalesOrderHeader Every bit SH ON SH.SalesOrderID = SD.SalesOrderID     JOIN Sales.Customer Equally C ON SH.CustomerID = C.CustomerID     WHERE C.StoreID = @storeid     GROUP Past P.ProductID, P.Proper noun ); GO

To invoke the function, run this query.

              SELECT * FROM Sales.ufn_SalesByStore (602);

C. Creating a multi-statement table-valued function

The following instance creates the table-valued part fn_FindReports(InEmpID) in the AdventureWorks2012 database. When supplied with a valid employee ID, the part returns a table that corresponds to all the employees that report to the employee either direct or indirectly. The role uses a recursive mutual table expression (CTE) to produce the hierarchical listing of employees. For more than information about recursive CTEs, see WITH common_table_expression (Transact-SQL).

              CREATE FUNCTION dbo.ufn_FindReports (@InEmpID INTEGER) RETURNS @retFindReports TABLE (     EmployeeID int chief key NOT NULL,     FirstName nvarchar(255) Not NULL,     LastName nvarchar(255) Not NULL,     JobTitle nvarchar(50) Non NULL,     RecursionLevel int Not NULL ) --Returns a result prepare that lists all the employees who report to the --specific employee directly or indirectly.*/ AS Begin WITH EMP_cte(EmployeeID, OrganizationNode, FirstName, LastName, JobTitle, RecursionLevel) -- CTE proper name and columns     Every bit (         -- Go the initial listing of Employees for Manager due north         SELECT e.BusinessEntityID, OrganizationNode = ISNULL(e.OrganizationNode, Bandage('/' AS hierarchyid))          , p.FirstName, p.LastName, e.JobTitle, 0         FROM HumanResources.Employee e               INNER JOIN Person.Person p               ON p.BusinessEntityID = e.BusinessEntityID         WHERE e.BusinessEntityID = @InEmpID         Matrimony ALL         -- Bring together recursive member to ballast         SELECT east.BusinessEntityID, due east.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, RecursionLevel + 1         FROM HumanResources.Employee e           INNER Bring together EMP_cte           ON e.OrganizationNode.GetAncestor(one) = EMP_cte.OrganizationNode           INNER Bring together Person.Person p           ON p.BusinessEntityID = e.BusinessEntityID         ) -- copy the required columns to the result of the office     INSERT @retFindReports     SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel     FROM EMP_cte     Render Finish; Go -- Example invocation SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel FROM dbo.ufn_FindReports(1);  GO

D. Creating a CLR function

The instance creates CLR function len_s. Before the part is created, the assembly SurrogateStringFunction.dll is registered in the local database.

Applies to: SQL Server ( SQL Server 2008 SP1 and later)

              DECLARE @SamplesPath nvarchar(1024); -- You may have to change the value of this variable if you have -- installed the sample in a location other than the default location. SELECT @SamplesPath = Supercede     (  physical_name      , 'Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\master.mdf'      , 'Microsoft SQL Server\130\Samples\Engine\Programmability\CLR\'     )   FROM main.sys.database_files   WHERE name = 'primary';  CREATE Assembly [SurrogateStringFunction] FROM @SamplesPath + 'StringManipulate\CS\StringManipulate\bin\debug\SurrogateStringFunction.dll' WITH PERMISSION_SET = EXTERNAL_ACCESS; Get  CREATE Role [dbo].[len_s] (@str nvarchar(4000)) RETURNS bigint AS EXTERNAL Proper noun [SurrogateStringFunction].[Microsoft.Samples.SqlServer.SurrogateStringFunction].[LenS]; Go

For an example of how to create a CLR table-valued function, see CLR Tabular array-Valued Functions.

Eastward. Displaying the definition of user-divers functions

              SELECT definition, blazon FROM sys.sql_modules Equally m JOIN sys.objects As o ON m.object_id = o.object_id     AND type IN ('FN', 'IF', 'TF'); Go

The definition of functions created past using the ENCRYPTION option cannot be viewed past using sys.sql_modules; however, other information nearly the encrypted functions is displayed.

See As well

  • Create User-divers Functions (Database Engine)
  • Change FUNCTION (Transact-SQL)
  • Drop Function (Transact-SQL)
  • OBJECTPROPERTYEX (Transact-SQL)
  • sys.sql_modules (Transact-SQL)
  • sys.assembly_modules (Transact-SQL)
  • EXECUTE (Transact-SQL)
  • CLR User-Defined Functions
  • EVENTDATA (Transact-SQL)
  • CREATE SECURITY POLICY (Transact-SQL)