CREATE FUNCTION (Transact-SQL)CREATE FUNCTION (Transact-SQL)

適用於: 是SQL Server 是Azure SQL Database 否Azure Synapse Analytics (SQL DW) 否平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

SQL ServerSQL ServerAzure SQL DatabaseAzure SQL Database 中建立使用者定義函數。Creates a user-defined function in SQL ServerSQL Server and Azure SQL DatabaseAzure SQL Database. 使用者定義函數是一種 Transact-SQLTransact-SQL 或 Common Language Runtime (CLR) 常式,它會接受參數、執行動作 (例如複雜計算) 並且將該動作的結果傳回成值。A user-defined function is a Transact-SQLTransact-SQL or common language runtime (CLR) routine that accepts parameters, performs an action, such as a complex calculation, and returns the result of that action as a value. 傳回值可以是純量 (單一) 值或資料表。The return value can either be a scalar (single) value or a table. 您可以使用這個陳述式來建立可用下列方式使用的可重複使用常式:Use this statement to create a reusable routine that can be used in these ways:

  • Transact-SQLTransact-SQL 陳述式中,例如 SELECTIn Transact-SQLTransact-SQL statements such as SELECT

  • 在呼叫函數的應用程式中In applications calling the function

  • 在另一個使用者自訂函數的定義中In the definition of another user-defined function

  • 若要將檢視參數化,或改善索引檢視的功能To parameterize a view or improve the functionality of an indexed view

  • 若要在資料表中定義資料行To define a column in a table

  • 若要在資料行上定義 CHECK 條件約束To define a CHECK constraint on a column

  • 取代預存程序To replace a stored procedure

  • 使用內嵌函式作為安全性原則的篩選述詞Use an inline function as a filter predicate for a security policy

注意

本主題將討論如何將 .NET Framework CLR 整合至 SQL ServerSQL ServerThe integration of .NET Framework CLR into SQL ServerSQL Server is discussed in this topic. CLR 整合不適用於 Azure SQL DatabaseAzure SQL DatabaseCLR integration does not apply to Azure SQL DatabaseAzure SQL Database.

注意

針對 SQL 資料倉儲SQL Data Warehouse,請參閱 CREATE FUNCTION (SQL 資料倉儲)For SQL 資料倉儲SQL Data Warehouse, see CREATE FUNCTION (SQL Data Warehouse).

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax

-- Transact-SQL Scalar Function Syntax  
CREATE [ OR ALTER ] 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  
[ ; ]  
-- Transact-SQL Inline Table-Valued Function Syntax   
CREATE [ OR ALTER ] 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 [ ) ]  
[ ; ]  
  
-- Transact-SQL Multi-Statement Table-Valued Function Syntax  
CREATE [ OR ALTER ] 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  
[ ; ]  
  
-- Transact-SQL Function Clauses   
<function_option>::=   
{  
    [ ENCRYPTION ]  
  | [ SCHEMABINDING ]  
  | [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]  
  | [ EXECUTE_AS_Clause ]  
  | [ INLINE = { ON | OFF }]  
}  
  
<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 }   
}  
-- CLR Scalar Function Syntax  
CREATE [ OR ALTER ] FUNCTION [ 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 Table-Valued Function Syntax  
CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name   
( { @parameter_name [AS] [ type_schema_name. ] parameter_data_type   
    [ = default ] }   
    [ ,...n ]  
)  
RETURNS TABLE <clr_table_type_definition>   
    [ WITH <clr_function_option> [ ,...n ] ]  
    [ ORDER ( <order_clause> ) ]  
    [ AS ] EXTERNAL NAME <method_specifier>  
[ ; ]  
-- CLR Function 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 NULL INPUT | CALLED ON NULL INPUT ]  
  | [ EXECUTE_AS_Clause ]  
}  
  
<clr_table_type_definition>::=   
( { column_name data_type } [ ,...n ] )  
  
-- In-Memory OLTP: Syntax for natively compiled, scalar user-defined function  
CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name   
 ( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type   
    [ NULL | NOT NULL ] [ = default ] [ READONLY ] }   
    [ ,...n ]   
  ]   
)   
RETURNS return_data_type  
     WITH <function_option> [ ,...n ]   
    [ AS ]   
    BEGIN ATOMIC WITH (set_option [ ,... n ])   
        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 ALTEROR ALTER
適用於SQL ServerSQL Server (SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1 及更新版本) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server ( SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1 and later) and Azure SQL DatabaseAzure SQL Database

只有在函數已經存在時,才能有條件地更改它。Conditionally alters the function only if it already exists.

注意

SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1 CU1 開始有提供 CLR 的選擇性 [OR ALTER] 語法。Optional [OR ALTER] syntax for CLR is available starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1 CU1.

schema_nameschema_name
這是使用者定義函數所屬的結構描述名稱。Is the name of the schema to which the user-defined function belongs.

function_namefunction_name
這是使用者定義函數的名稱。Is the name of the user-defined function. 函式名稱必須符合識別碼的規則,且在資料庫內及對其結構描述而言,必須是唯一的。Function names must comply with the rules for identifiers and must be unique within the database and to its schema.

注意

即使沒有指定參數,函數名稱後面仍需要括號。Parentheses are required after the function name even if a parameter is not specified.

@parameter_name@parameter_name
這是使用者定義函數中的參數。Is a parameter in the user-defined function. 您可以宣告一個或多個參數。One or more parameters can be declared.

函數最多可以有 2,100 個參數。A function can have a maximum of 2,100 parameters. 除非定義了參數的預設值,否則在執行函數時,使用者必須提供每個已宣告之參數的值。The value of each declared parameter must be supplied by the user when the function is executed, unless a default for the parameter is defined.

使用 @ 記號當做第一個字元來指定參數名稱。Specify a parameter name by using an at sign (@) as the first 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 place only of constants; they cannot be used instead of table names, column names, or the names of other database objects.

注意

在預存程序或使用者定義函數中傳遞參數,或在批次陳述式中宣告和設定變數時,不接受 ANSI_WARNINGS。ANSI_WARNINGS is not honored when you pass parameters in a stored procedure, user-defined function, or when you declare and set variables in a batch statement. 例如,若將變數定義為 char(3) ,然後將其設為大於三個字元的值,資料便會被截斷成定義的大小,且 INSERTUPDATE 陳述式會執行成功。For example, 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.[ type_schema_name. ] parameter_data_type] parameter_data_type
這是參數資料類型,對於其所屬的結構描述而言為選擇性。Is the parameter data type, and optionally the schema to which it belongs. Transact-SQLTransact-SQL 函式而言,所有資料類型 (包括 CLR 使用者定義類型和使用者定義資料表類型) 都是允許的資料類型,但 timestamp 資料類型除外。For Transact-SQLTransact-SQL functions, all data types, including CLR user-defined types and user-defined table types, are allowed except the timestamp data type. 就 CLR 函式而言,所有資料類型 (包括 CLR 使用者定義類型) 都是允許的資料類型,但 textntextimage、使用者定義資料表類型及 timestamp 資料類型除外。For CLR functions, all data types, including CLR user-defined types, are allowed except text, ntext, image, user-defined table types and timestamp data types. 非純量類型 cursortable 不能指定為 Transact-SQLTransact-SQL 或 CLR 函式中的參數資料類型。The nonscalar types, cursor and table, cannot be specified as a parameter data type in either Transact-SQLTransact-SQL or CLR functions.

如果未指定 type_schema_nameDatabase EngineDatabase Engine就會依照下列順序尋找 scalar_parameter_data_typeIf type_schema_name is not specified, the Database EngineDatabase Engine looks for the scalar_parameter_data_type in the following order:

  • 內含 SQL ServerSQL Server 系統資料類型名稱的結構描述。The schema that contains the names of SQL ServerSQL Server system data types.
  • 目前資料庫中之目前使用者的預設結構描述。The default schema of the current user in the current database.
  • 目前資料庫中的 dbo 結構描述。The dbo schema in the current database.

[ =default ][ =default ]
這是參數的預設值。Is a default value for the parameter. 如果已定義 default 值,則不需為該參數指定值,即可執行函式。If a default value is defined, the function can be executed without specifying a value for that parameter.

注意

您可以針對 CLR 函式指定預設參數值,但 varchar(max)varbinary(max) 資料類型除外。Default parameter values can be specified for CLR functions except for the varchar(max) and varbinary(max) data types.

如果函數的參數有預設值,則必須在呼叫函數來擷取該預設值時指定關鍵字 DEFAULT。When a parameter of the function has a default value, the keyword DEFAULT must be specified when the function is called to retrieve the default value. 這個行為與使用預存程序中具有預設值的參數不一樣,因為在預存程序中,省略參數也意味著使用預設值。This behavior is different from using parameters with default values in stored procedures in which omitting the parameter also implies the default value. 不過,使用 EXECUTE 陳述式來叫用純量函數時,不需要 DEFAULT 關鍵字。However, the DEFAULT keyword is not required when invoking a scalar function by using the EXECUTE statement.

READONLYREADONLY
指示無法在函數的定義內更新或修改參數。Indicates that the parameter cannot be updated or modified within the definition of the function. 如果參數類型是使用者定義資料表類型,應該指定 READONLY。If the parameter type is a user-defined table type, READONLY should be specified.

return_data_typereturn_data_type
這是純量使用者定義函數的傳回值。Is the return value of a scalar user-defined function. Transact-SQLTransact-SQL 函式而言,所有資料類型 (包括 CLR 使用者定義型別) 都是允許的資料類型,但 timestamp 資料類型除外。For Transact-SQLTransact-SQL functions, all data types, including CLR user-defined types, are allowed except the timestamp data type. 就 CLR 函式而言,所有資料類型 (包括 CLR 使用者定義類型) 都是允許的資料類型,但 text ntext imagetimestamp 資料類型除外。For CLR functions, all data types, including CLR user-defined types, are allowed except the text, ntext, image, and timestamp data types. 非純量類型 cursortable 不能指定為 Transact-SQLTransact-SQL 或 CLR 函式中的傳回資料類型。The nonscalar types, cursor and table, cannot be specified as a return data type in either Transact-SQLTransact-SQL or CLR functions.

function_bodyfunction_body
指定一系列 Transact-SQLTransact-SQL 陳述式 (並用這些陳述式並不會造成任何副作用,例如修改資料表等) 定義函數的值。Specifies that a series of Transact-SQLTransact-SQL statements, which together do not produce a side effect such as modifying a table, define the value of the function. function_body 僅用於純量函式和多重陳述式資料表值函式 (MSTVF) 中。function_body is used only in scalar functions and multi-statement table-valued functions (MSTVFs).

在純量函式中,function_body 是一系列的 Transact-SQLTransact-SQL 陳述式,這些陳述式會一起評估為純量值。In scalar functions, function_body is a series of Transact-SQLTransact-SQL statements that together evaluate to a scalar value.

在 MSTVF 中,function_body 是一系列的 Transact-SQLTransact-SQL 陳述式,這些陳述式會填入 TABLE 傳回變數。In MSTVFs, function_body is a series of Transact-SQLTransact-SQL statements that populate a TABLE return variable.

scalar_expressionscalar_expression
指定純量函數傳回的純量值。Specifies the scalar value that the scalar function returns.

TABLETABLE
指定資料表值函式 (TVF) 的傳回值是資料表。Specifies that the return value of the table-valued function (TVF) is a table. 只有常數和 @local_variables 才能傳遞給 TVF。Only constants and @local_variables can be passed to TVFs.

在內嵌 TVF 中,TABLE 傳回值是透過單一 SELECT 陳述式定義。In inline TVFs, the TABLE return value is defined through a single SELECT statement. 內嵌函數沒有相關聯的傳回變數。Inline functions do not have associated return variables.

在 MSTVF 中,@return_variable 是一個 TABLE 變數,可用來儲存及累積應作為函式值傳回的資料列。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. 您只能針對 Transact-SQLTransact-SQL 函式指定 @ return_variable,不能針對 CLR 函式指定。@return_variable can be specified only for Transact-SQLTransact-SQL functions and not for CLR functions.

select_stmtselect_stmt
這是單一 SELECT 陳述式,可定義內嵌資料表值函式 (TVF) 的傳回值。Is the single SELECT statement that defines the return value of an inline table-valued function (TVF).

ORDER (<order_clause>) 指定從資料表值函式傳回結果的順序。ORDER (<order_clause>) Specifies the order in which results are being returned from the table-valued function. 如需詳細資訊,請參閱本主題稍後的在 CLR 資料表值函式中使用排序次序For more information, see the section, "Using Sort Order in CLR Table-valued Functions", later in this topic.

EXTERNAL NAME <method_specifier> assembly_name.class_name.method_name EXTERNAL NAME <method_specifier> assembly_name.class_name.method_name
適用於SQL ServerSQL Server (SQL Server 2008SQL Server 2008 SP1 及更新版本)Applies to: SQL ServerSQL Server ( SQL Server 2008SQL Server 2008 SP1 and later)

指定建立函式名稱時應該要參考的組件和方法。Specifies the assembly and method to which the created function name shall refer.

  • assembly_name - 必須符合 name 資料行中的值,此資料行屬於assembly_name - must match a value in the name column of
    第 1 課:建立 Windows Azure 儲存體物件SELECT * FROM sys.assemblies;SELECT * FROM sys.assemblies;.
    這是 CREATE ASSEMBLY 陳述式中所使用的名稱。This is the name that was used on the CREATE ASSEMBLY statement.

  • class_name - 必須符合 assembly_name 資料行中的值,此資料行屬於class_name - must match a value in the assembly_name column of
    第 1 課:建立 Windows Azure 儲存體物件SELECT * FROM sys.assembly_modules;SELECT * FROM sys.assembly_modules;.
    值常包含內嵌的句號或點。Often the value contains an embedded period or dot. 在這類情況下,TRANSACT-SQL 語法會要求以一組方括弧 [] 括住值,或以一組雙引號 "" 括住值。In such cases the Transact-SQL syntax requires that the value be bounded with a pair of straight brackets [], or with a pair of double quotation marks "".

  • method_name - 必須符合 method_name 資料行中的值,此資料行屬於method_name - must match a value in the method_name column of
    第 1 課:建立 Windows Azure 儲存體物件SELECT * FROM sys.assembly_modules;SELECT * FROM sys.assembly_modules;.
    方法必須為靜態。The method must be static.

在典型的範例中,針對所有類型皆位於 MyFood 命名空間的 MyFood.DLL,EXTERNAL NAME 的值可能是:In a typical example, for MyFood.DLL, in which all types are in the MyFood namespace, the EXTERNAL NAME value could be:
MyFood.[MyFood.MyClass].MyStaticMethod

注意

依預設,SQL ServerSQL Server 不能執行 CLR 程式碼。By default, SQL ServerSQL Server cannot execute CLR code. 您可以建立、修改和卸除參考通用語言執行平台模組的資料庫物件;不過,必須等到您啟用 clr enabled 選項之後,才能在 SQL ServerSQL Server 中執行這些參考。You can create, modify, and drop database objects that reference common language runtime modules; however, you cannot execute these references in SQL ServerSQL Server until you enable the clr enabled option. 若要啟用這個選項,請使用 sp_configureTo 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> ] [ ,...n ] ) 定義 Transact-SQLTransact-SQL 函式的資料表資料類型。< table_type_definition*>* ( { <column_definition> <column_constraint> | <computed_column_definition> } [ <table_constraint> ] [ ,...n ] ) Defines the table data type for a Transact-SQLTransact-SQL function. 資料表宣告包括資料行定義和資料行或資料表條件約束。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 ] )< clr_table_type_definition > ( { column_name**data_type } [ ,...n ] )
適用於SQL ServerSQL Server (SQL Server 2008SQL Server 2008 SP1 及更新版本) 和 Azure SQL DatabaseAzure SQL Database (某些區域為預覽版本)。Applies to: SQL ServerSQL Server ( SQL Server 2008SQL Server 2008 SP1 and later) and Azure SQL DatabaseAzure SQL Database (Preview in some regions).

定義 CLR 函數的資料表資料類型。Defines the table data types for a CLR function. 資料表宣告只包含資料行名稱和資料類型。The table declaration includes only column names and data types. 資料表一律放在主要檔案群組中。The table is always put in the primary filegroup.

NULL|NOT NULLNULL|NOT NULL
只有針對原生編譯的純量使用者定義函式才支援。Supported only for natively compiled, scalar user-defined functions. 如需詳細資訊,請參閱記憶體內部 OLTP 的純量使用者定義函數For more information, see Scalar User-Defined Functions for In-Memory OLTP.

NATIVE_COMPILATIONNATIVE_COMPILATION
指出使用者定義函式是否為原生編譯函式。Indicates whether a user-defined function is natively compiled. 此引數是原生編譯之純量使用者定義函式的必要引數。This argument is required for natively compiled, scalar user-defined functions.

BEGIN ATOMIC WITHBEGIN ATOMIC WITH
只有針對原生編譯的純量使用者定義函式才支援,並且為必要項目。Supported only for natively compiled, scalar user-defined functions, and is required. 如需詳細資訊,請參閱 Atomic BlocksFor more information, see Atomic Blocks.

SCHEMABINDINGSCHEMABINDING
SCHEMABINDING 是原生編譯之純量使用者定義函式的必要引數。The SCHEMABINDING argument is required for natively compiled, scalar user-defined functions.

EXECUTE ASEXECUTE AS
EXECUTE AS 是原生編譯之純量使用者定義函式的必要項目。EXECUTE AS is required for natively compiled, scalar user-defined functions.

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

指定此函數將會有下列其中一個或多個選項。Specifies that the function will have one or more of the following options.

ENCRYPTIONENCRYPTION
適用於SQL ServerSQL Server (SQL Server 2008SQL Server 2008 SP1 及更新版本)Applies to: SQL ServerSQL Server ( SQL Server 2008SQL Server 2008 SP1 and later)

指出 Database EngineDatabase Engine 會將 CREATE FUNCTION 陳述式的原始文字轉換為模糊化格式。Indicates that the Database EngineDatabase Engine will convert the original text of the CREATE FUNCTION statement to an obfuscated format. 無法直接從任何目錄檢視中看見模糊化的輸出。The output of the obfuscation is not directly visible in any catalog views. 對系統資料表或資料庫檔案沒有存取權的使用者,無法擷取混亂格式的文字。Users that have no access to system tables or database files cannot retrieve the obfuscated text. 不過,可透過 DAC 連接埠存取系統資料表或直接存取資料庫檔案的具特殊權限使用者,則可使用該文字。However, the text will be available to privileged users that can either access system tables over the DAC port or directly access database files. 另外,可將偵錯工具附加至伺服器處理序的使用者,可以在執行階段從記憶體擷取原始程序。Also, users that can attach a debugger to the server process can retrieve the original procedure from memory at runtime. 如需如何存取系統中繼資料的詳細資訊,請參閱中繼資料可見性組態For more information about accessing system metadata, see Metadata Visibility Configuration.

使用此選項可防止在 SQL ServerSQL Server 複寫中發行這個函數。Using this option prevents the function from being published as part of SQL ServerSQL Server replication. 無法為 CLR 函數指定此選項。This option cannot be specified for CLR functions.

SCHEMABINDINGSCHEMABINDING
指定函數必須繫結到它所參考的資料庫物件。Specifies that the function is bound to the database objects that it references. 當指定 SCHEMABINDING 時,無法依照會影響函數定義的方式來修改基底物件。When SCHEMABINDING is specified, the base objects cannot be modified in a way that would affect the function definition. 您必須先修改或卸除函數定義才能移除對於要修改之物件的相依性。The function definition itself must first be modified or dropped to remove dependencies on the object that is to be modified.

只有在下發生下列其中一個動作時,才會移除函數與其參考的物件之間的繫結:The binding of the function to the objects it references is removed only when one of the following actions occurs:

  • 已卸除這個函數。The function is dropped.

  • 您可以使用未指定 SCHEMABINDING 選項的 ALTER 陳述式來修改函式。The function is modified by using the ALTER statement with the SCHEMABINDING option not specified.

只有當下列條件成立時,函數才可繫結結構描述:A function can be schema bound only if the following conditions are true:

  • 函數是一個 Transact-SQLTransact-SQL 函數。The function is a Transact-SQLTransact-SQL function.

  • 函數參考的使用者定義函數和檢視表也繫結結構描述。The user-defined functions and views referenced by the function are also schema-bound.

  • 函數參考的物件是利用兩部分名稱來參考。The objects referenced by the function are referenced using a two-part name.

  • 函數及其參考的物件屬於相同的資料庫。The function and the objects it references belong to the same database.

  • 執行 CREATE FUNCTION 陳述式的使用者在函式所參考資料庫物件上具備 REFERENCES 權限。The user who executed the CREATE FUNCTION statement has REFERENCES permission on the database objects that the function references.

RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUTRETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT
指定純量函式的 OnNULLCall 屬性。Specifies the OnNULLCall attribute of a scalar function. 若未指定,預設情況下意味著 CALLED ON NULL INPUT。If not specified, CALLED ON NULL INPUT is implied by default. 這表示,即使傳遞 NULL 做為引數,函數主體仍會執行。This means that the function body executes even if NULL is passed as an argument.

如果在 CLR 函數中指定 RETURNS NULL ON NULL INPUT,它會指出 SQL ServerSQL Server 可以在它接收的任何引數是 NULL 時傳回 NULL,而不必實際叫用函數主體。If RETURNS NULL ON NULL INPUT is specified in a CLR function, it indicates that SQL ServerSQL Server can return NULL when any of the arguments it receives is NULL, without actually invoking the body of the function. 如果 <method_specifier> 中指定的 CLR 函式方法已經有一個指出 RETURNS NULL ON NULL INPUT 的自訂屬性,但 CREATE FUNCTION 陳述式指出 CALLED ON NULL INPUT,則會優先使用 CREATE FUNCTION 陳述式。If the method of a CLR function specified in <method_specifier> already has a custom attribute that indicates RETURNS NULL ON NULL INPUT, but the CREATE FUNCTION statement indicates CALLED ON NULL INPUT, the CREATE FUNCTION statement takes precedence. 無法為 CLR 資料表值函式指定 OnNULLCall 屬性。The OnNULLCall attribute cannot be specified for CLR table-valued functions.

EXECUTE AS 子句EXECUTE AS Clause
指定執行使用者定義函數時所在的安全性內容。Specifies the security context under which the user-defined function is executed. 因此,您可以控制 SQL ServerSQL Server 要利用哪個使用者帳戶來驗證在函數參考的任何資料庫物件上的權限。Therefore, you can control which user account SQL ServerSQL Server uses to validate permissions on any database objects that are referenced by the function.

注意

無法為內嵌資料表值函式指定 EXECUTE ASEXECUTE AS cannot be specified for inline table-valued functions.

如需詳細資訊,請參閱 EXECUTE AS 子句 (Transact-SQL)For more information, see EXECUTE AS Clause (Transact-SQL).

INLINE = { ON | OFF }INLINE = { ON | OFF }
指定是否應該內嵌此純量 UDF。Specifies whether this scalar UDF should be inlined or not. 此子句僅適用於純量使用者定義函式。This clause applies only to scalar user-defined functions. INLINE 子句非為強制。The INLINE clause is not mandatory. 如果未指定 INLINE 子句,它會根據是否可以內嵌 UDF 自動設為 ON/OFF。If INLINE clause is not specified, it is automatically set to ON/OFF based on whether the UDF is inlineable. 如果指定 INLINE=ON,但發現不可內嵌 UDF,則會擲回錯誤。If INLINE=ON is specified but the UDF is found to be non-inlineable, an error will be thrown. 如需詳細資訊,請參閱純量 UDF 內嵌For more information, see Scalar UDF Inlining.

< column_definition >::=< column_definition >::=

定義資料表資料類型。Defines the table data type. 資料表宣告包括資料行定義和條件約束。The table declaration includes column definitions and constraints. 針對 CLR 函式,只能指定 column_namedata_typeFor CLR functions, only column_name and data_type can be specified.

column_namecolumn_name
這是資料表中的資料行名稱。Is the name of a column in the table. 資料行名稱必須符合識別碼規則,在資料表中也必須是唯一的。Column names must comply with the rules for identifiers and must be unique in the table. column_name 可由 1 到 128 個字元組成。column_name can consist of 1 through 128 characters.

data_typedata_type
指定資料行資料類型。Specifies the column data type. Transact-SQLTransact-SQL 函式而言,允許所有資料類型 (包括 CLR 使用者定義型別),但 timestamp除外。For Transact-SQLTransact-SQL functions, all data types, including CLR user-defined types, are allowed except timestamp. 就 CLR 函式而言,允許所有資料類型 (包括 CLR 使用者定義型別),但 textntextimagecharvarcharvarchar(max)timestamp 除外。無法指定非純量類型 cursor 作為 Transact-SQLTransact-SQL 或 CLR 函式中的資料行資料類型。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 be specified as a column data type in either Transact-SQLTransact-SQL or CLR functions.

DEFAULT constant_expressionDEFAULT constant_expression
指定在插入期間未明確提供值時,提供給資料行的值。Specifies the value provided for the column when a value is not explicitly supplied during an insert. constant_expression 是常數、NULL 或系統函式值。constant_expression is a constant, NULL, or a system function value. 除了含有 IDENTITY 屬性的資料行之外,任何資料行都可以套用 DEFAULT 定義。DEFAULT definitions can be applied to any column except those that have the IDENTITY property. 無法為 CLR 資料表值函式指定 DEFAULT。DEFAULT cannot be specified for CLR table-valued functions.

COLLATE collation_nameCOLLATE collation_name
指定資料行的定序。Specifies the collation for the column. 若未指定,就會將資料庫的預設定序指派給資料行。If not specified, the column is assigned the default collation of the database. 定序名稱可以是 Windows 定序名稱或 SQL 定序名稱。Collation name can be either a Windows collation name or a SQL collation name. 如需定序的清單和相關詳細資訊,請參閱 Windows 定序名稱 (Transact-SQL)SQL Server 定序名稱 (Transact-SQL)For a list of and more information about collations, see Windows Collation Name (Transact-SQL) and SQL Server Collation Name (Transact-SQL).

COLLATE 子句只能用來變更 charvarcharncharnvarchar 資料類型之資料行的定序。The COLLATE clause can be used to change the collations only of columns of the char, varchar, nchar, and nvarchar data types.

注意

無法為 CLR 資料表值函式指定 COLLATECOLLATE cannot be specified for CLR table-valued functions.

ROWGUIDCOLROWGUIDCOL
指出新資料行是一個資料列全域唯一識別碼資料行。Indicates that the new column is a row globally unique identifier column. 每個資料表只能有一個 uniqueidentifier 資料行指定為 ROWGUIDCOL 資料行。Only one uniqueidentifier column per table can be designated as the ROWGUIDCOL column. ROWGUIDCOL 屬性只能指派給 uniqueidentifier 資料行。The ROWGUIDCOL property can be assigned only to a uniqueidentifier column.

ROWGUIDCOL 屬性不會強制執行資料行中所儲存之值的唯一性。The ROWGUIDCOL property does not enforce uniqueness of the values stored in the column. 它也不會自動為插入資料表中的新資料列產生值。It also does not automatically generate values for new rows inserted into the table. 若要為每個資料行產生唯一值,請在 INSERT 陳述式上使用 NEWID 函數。To generate unique values for each column, use the NEWID function on INSERT statements. 可以指定預設值;不過,NEWID 不能指定為預設值。A default value can be specified; however, NEWID cannot be specified as the default.

IDENTITYIDENTITY
指出新資料行是識別欄位。Indicates that the new column is an identity column. 新資料列加入至資料表時,SQL ServerSQL Server 會提供資料行的唯一累加值。When a new row is added to the table, SQL ServerSQL Server provides a unique, incremental value for the column. 識別欄位通常用來搭配 PRIMARY KEY 條件約束一起使用,當做資料表的唯一資料列識別碼。Identity columns are typically used together with PRIMARY KEY constraints to serve as the unique row identifier for the table. 可以將 IDENTITY 屬性指派給 tinyintsmallintintbigintdecimal(p,0)numeric(p,0) 資料行。The IDENTITY property 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. 繫結的預設值和 DEFAULT 條件約束無法搭配識別欄位使用。Bound defaults and DEFAULT constraints cannot be used with an identity column. 您必須同時指定 seedincrement,或兩者都不指定。You must specify both the seed and increment or neither. 如果同時不指定這兩者,預設值便是 (1,1)。If neither is specified, the default is (1,1).

無法為 CLR 資料表值函式指定 IDENTITY。IDENTITY cannot be specified for CLR table-valued functions.

seedseed
這是要指派給資料表中第一個資料列的整數值。Is the integer value to be assigned to the first row in the table.

incrementincrement
這是要新增至資料表中後續資料列之 seed 值的整數值。Is the integer value to add to the seed value for successive rows in the table.

< column_constraint >::= 和 < table_constraint>::=< column_constraint >::= and < table_constraint>::=

定義指定之資料行或資料表的條件約束。Defines the constraint for a specified column or table. 就 CLR 函數而言,唯一允許的條件約束類型是 NULL。For CLR functions, the only constraint type allowed is NULL. 不允許具名條件約束。Named constraints are not allowed.

NULL | NOT NULLNULL | NOT NULL
判斷資料行中是否允許 Null 值。Determines whether null values are allowed in the column. 嚴格來說,NULL 並不算是條件約束,但是您可以如同指定 NOT NULL 一樣加以指定。NULL is not strictly a constraint but can be specified just like NOT NULL. 無法為 CLR 資料表值函式指定 NOT NULL。NOT NULL cannot be specified for CLR table-valued functions.

PRIMARY KEYPRIMARY KEY
這是一項條件約束,它利用唯一索引強制執行指定之資料行的實體完整性。Is a constraint that enforces entity integrity for a specified column through a unique index. 在資料表值使用者定義函數中,PRIMARY KEY 條件約束只能建立在每份資料表的一個資料行上。In table-valued user-defined functions, the PRIMARY KEY constraint can be created on only one column per table. 無法為 CLR 資料表值函式指定 PRIMARY KEY。PRIMARY KEY cannot be specified for CLR table-valued functions.

UNIQUEUNIQUE
這是一項條件約束,它透過唯一索引為指定的一個或多個資料行提供實體完整性。Is a constraint that provides entity integrity for a specified column or columns through a unique index. 一份資料表可以有多個 UNIQUE 條件約束。A table can have multiple UNIQUE constraints. 無法為 CLR 資料表值函式指定 UNIQUE。UNIQUE cannot be specified for CLR table-valued functions.

CLUSTERED | NONCLUSTEREDCLUSTERED | NONCLUSTERED
指出針對 PRIMARY KEY 或 UNIQUE 條件約束建立叢集或非叢集索引。Indicate that a clustered or a nonclustered index is created for the PRIMARY KEY or UNIQUE constraint. PRIMARY KEY 條件約束使用 CLUSTERED,UNIQUE 條件約束則使用 NONCLUSTERED。PRIMARY KEY constraints use CLUSTERED, and UNIQUE constraints use NONCLUSTERED.

只能為一個條件約束指定 CLUSTERED。CLUSTERED can be specified for only one constraint. 如果針對 UNIQUE 條件約束指定 CLUSTERED,且指定了 PRIMARY KEY 條件約束,則 PRIMARY KEY 會使用 NONCLUSTERED。If CLUSTERED is specified for a UNIQUE constraint and a PRIMARY KEY constraint is also specified, the PRIMARY KEY uses NONCLUSTERED.

無法為 CLR 資料表值函式指定 CLUSTERED 和 NONCLUSTERED。CLUSTERED and NONCLUSTERED cannot be specified for CLR table-valued functions.

CHECKCHECK
這是一個條件約束,藉由限制可能輸入一個或多個資料行的值,強制執行範圍完整性。Is a constraint that enforces domain integrity by limiting the possible values that can be entered into a column or columns. 無法為 CLR 資料表值函式指定 CHECK 條件約束。CHECK constraints cannot be specified for CLR table-valued functions.

logical_expressionlogical_expression
這是一個傳回 TRUE 或 FALSE 的邏輯運算式。Is a logical expression that returns TRUE or FALSE.

<computed_column_definition>::=<computed_column_definition>::=

指定計算資料行。Specifies a computed column. 如需有關計算資料行的詳細資訊,請參閱 CREATE TABLE (Transact-SQL)For more information about computed columns, see CREATE TABLE (Transact-SQL).

column_namecolumn_name
這是計算資料行的名稱。Is the name of the computed column.

computed_column_expressioncomputed_column_expression
這是定義計算資料行值的運算式。Is an expression that defines the value of a computed column.

<index_option>::=<index_option>::=

指定 PRIMARY KEY 或 UNIQUE 索引的索引選項。Specifies the index options for the PRIMARY KEY or UNIQUE index. 如需索引選項的詳細資訊,請參閱 CREATE INDEX (Transact-SQL)For more information about index options, see CREATE INDEX (Transact-SQL).

PAD_INDEX = { ON | OFF }PAD_INDEX = { ON | OFF }
指定索引填補。Specifies index padding. 預設值為 OFF。The default is OFF.

FILLFACTOR = fillfactorFILLFACTOR = fillfactor
指定指出在建立或變更索引期間,Database EngineDatabase Engine 各索引頁面之分葉層級填滿程度的百分比。Specifies a percentage that indicates how full the Database EngineDatabase Engine should make the leaf level of each index page during index creation or change. fillfactor 必須是 1 到 100 之間的整數值。fillfactor must be an integer value from 1 to 100. 預設值是 0。The default is 0.

IGNORE_DUP_KEY = { ON | OFF }IGNORE_DUP_KEY = { ON | OFF }
指定當插入作業嘗試將重複的索引鍵值插入唯一索引時所產生的錯誤回應。Specifies the error response when an insert operation attempts to insert duplicate key values into a unique index. IGNORE_DUP_KEY 選項只適用於在建立或重建索引之後所發生的插入作業。The IGNORE_DUP_KEY option applies only to insert operations after the index is created or rebuilt. 預設值為 OFF。The default is OFF.

STATISTICS_NORECOMPUTE = { ON | OFF }STATISTICS_NORECOMPUTE = { ON | OFF }
指定是否要重新計算散發統計資料。Specifies whether distribution statistics are recomputed. 預設值為 OFF。The default is OFF.

ALLOW_ROW_LOCKS = { ON | OFF }ALLOW_ROW_LOCKS = { ON | OFF }
指定是否允許資料列鎖定。Specifies whether row locks are allowed. 預設值是 ON。The default is ON.

ALLOW_PAGE_LOCKS = { ON | OFF }ALLOW_PAGE_LOCKS = { ON | OFF }
指定是否允許頁面鎖定。Specifies whether page locks are allowed. 預設值是 ON。The default is ON.

最佳作法Best Practices

如果未以 SCHEMABINDING 子句建立使用者定義函式,叫用該函式時,對基礎物件所進行的變更可能會影響函式定義並產生非預期結果。If a user-defined function is not created with the SCHEMABINDING clause, changes that are made to underlying objects can affect the definition of the function and produce unexpected results when it is invoked. 建議您實作下列其中一個方法,以確保函數不會因為其基礎物件的變更而變成過期:We recommend that you implement one of the following methods to ensure that the function does not become outdated because of changes to its underlying objects:

  • 當您要建立函式時,請指定 WITH SCHEMABINDING 子句。Specify the WITH SCHEMABINDING clause when you are creating the function. 這可以確保系統無法修改函數定義中參考的物件 (除非同時修改函數)。This ensures that the objects referenced in the function definition cannot be modified unless the function is also modified.

  • 在修改函式定義中指定的任何物件之後,執行 sp_refreshsqlmodule 預存程序。Execute the sp_refreshsqlmodule stored procedure after modifying any object that is specified in the definition of the function.

重要

如需內嵌資料表值函式 (內嵌 TVF) 和多重陳述式資料表值函式 (MSTVF) 的詳細資訊和效能考量事項,請參閱建立使用者定義函式 (資料庫引擎)For more information and performance considerations about inline table-valued functions (inline TVFs) and multi-statement table-valued functions (MSTVFs), see Create User-defined Functions (Database Engine).

資料型別Data Types

如果在 CLR 函式中指定參數,這些參數應該是 SQL ServerSQL Server 類型,如同先前針對 scalar_parameter_data_type 所下的定義。If parameters are specified in a CLR function, they should be SQL ServerSQL Server types as defined previously for scalar_parameter_data_type. 如需有關比較 SQL ServerSQL Server 系統資料類型與 CLR 整合資料類型或 .NET Framework.NET Framework 通用語言執行平台資料類型的詳細資訊,請參閱對應 CLR 參數資料For information about comparing SQL ServerSQL Server system data types to CLR integration data types or .NET Framework.NET Framework common language runtime data types, see Mapping CLR Parameter Data.

若要讓 SQL ServerSQL Server 在正確方法於類別中出現多載時參考該正確方法,<method_specifier> 中所指出的方法必須具有下列特性:For SQL ServerSQL Server to reference the correct method when it is overloaded in a class, the method indicated in <method_specifier> must have the following characteristics:

  • 接收的參數數目與 [ ,...n ] 中所指定的數目相同。Receive the same number of parameters as specified in [ ,...n ].

  • 依值 (而不是依參考) 接收所有參數。Receive all the parameters by value, not by reference.

  • 使用與 SQL ServerSQL Server 函數中指定之類型相容的參數類型。Use parameter types that are compatible with those specified in the SQL ServerSQL Server function.

如果 CLR 函式的傳回資料類型指定資料表類型 (RETURNS TABLE),則 <method_specifier> 中方法的傳回資料類型應該屬於 IEnumeratorIEnumerable 類型,且會假設由函式建立者實作介面。If the return data type of the CLR function specifies a table type (RETURNS TABLE), the return data type of the method in <method_specifier> should be of type IEnumerator or IEnumerable, and it is assumed that the interface is implemented by the creator of the function. Transact-SQLTransact-SQL 函式不同,CLR 函式不能在 <table_type_definition> 中包含 PRIMARY KEY、UNIQUE 或 CHECK 條件約束。Unlike Transact-SQLTransact-SQL functions, CLR functions cannot include PRIMARY KEY, UNIQUE, or CHECK constraints in <table_type_definition>. <table_type_definition> 中所指定資料行的資料類型必須與 <method_specifier> 中方法在執行時所傳回的相對應結果集資料行相符。The data types of columns specified in <table_type_definition> must match the types of the corresponding columns of the result set returned by the method in <method_specifier> at execution time. 這項類型檢查作業不會在建立函數時執行。This type-checking is not performed at the time the function is created.

如需有關如何設計 CLR 函式的詳細資訊,請參閱 CLR 使用者定義函式For more information about how to program CLR functions, see CLR User-Defined Functions.

一般備註General Remarks

純量函式可在使用純量運算式的情況下叫用。Scalar functions can be invoked where scalar expressions are used. 這包括計算資料行和 CHECK 條件約束定義。This includes computed columns and CHECK constraint definitions. 您也可以使用 EXECUTE 陳述式來執行純量函式。Scalar functions can also be executed by using the EXECUTE statement. 叫用純量函式必須至少使用函式的兩部分名稱 ( . )。Scalar functions must be invoked by using at least the two-part name of the function (.). 如需多部分名稱的詳細資訊,請參閱 Transact-SQL 語法慣例 (Transact-SQL)For more information about multipart names, see Transact-SQL Syntax Conventions (Transact-SQL). 資料表值函式可在 SELECTINSERTUPDATEDELETE 陳述式的 FROM 子句中允許資料表運算式時叫用。Table-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.

  • TRY...CATCH 陳述式之外的流程控制陳述式。Control-of-Flow statements except TRY...CATCH statements.

  • 定義區域資料變數和區域資料指標的 DECLARE 陳述式。DECLARE statements defining local data variables and local cursors.

  • 包含選取清單的 SELECT 陳述式,其中含有將值指派給區域變數的運算式。SELECT statements that contain select lists with expressions that assign values to local variables.

  • 資料指標作業 - 參考函數中之已宣告、已開啟、已關閉及已取消配置的本機資料指標。Cursor operations referencing local cursors that are declared, opened, closed, and deallocated in the function. 只允許使用 INTO 子句將值指派給區域變數的 FETCH 陳述式;不允許將資料傳回用戶端的 FETCH 陳述式。Only FETCH statements that assign values to local variables using the INTO clause are allowed; FETCH statements that return data to the client are not allowed.

  • 修改區域資料表變數的 INSERTUPDATEDELETE 陳述式。INSERT, UPDATE, and DELETE statements modifying local table variables.

  • 呼叫擴充預存程序的 EXECUTE 陳述式。EXECUTE statements calling extended stored procedures.

如需詳細資訊,請參閱建立使用者定義函式 (資料庫引擎)For more information, see Create User-defined Functions (Database Engine).

計算資料行的互通性Computed Column Interoperability

函數具有下列屬性。Functions have the following properties. 這些屬性的值會決定是否可以在可保存或索引的計算資料行中使用函數。The values of these properties determine whether functions can be used in computed columns that can be persisted or indexed.

屬性Property DescriptionDescription 注意Notes
IsDeterministicIsDeterministic 函數可分為具決定性或不具決定性。Function is deterministic or nondeterministic. 具決定性函數中允許本機資料存取。Local data access is allowed in deterministic functions. 例如,每當利用一組特定輸入值來呼叫函數時都一律傳回相同結果且含有相同資料庫狀態的函數,就會被標示為具決定性。For example, functions that always return the same result any time they are called by using a specific set of input values and with the same state of the database would be labeled deterministic.
IsPreciseIsPrecise 函數可分為精確或不精確。Function is precise or imprecise. 不精確函數內含浮點作業之類的作業。Imprecise functions contain operations such as floating point operations.
IsSystemVerifiedIsSystemVerified SQL ServerSQL Server 可以驗證函數的有效位數和決定性屬性。The precision and determinism properties of the function can be verified by SQL ServerSQL Server.
SystemDataAccessSystemDataAccess 函數會存取 SQL ServerSQL Server 之本機執行個體中的系統資料 (系統目錄或虛擬系統資料表)。Function accesses system data (system catalogs or virtual system tables) in the local instance of SQL ServerSQL Server.
UserDataAccessUserDataAccess 函數會存取 SQL ServerSQL Server 之本機執行個體中的使用者資料。Function accesses user data in the local instance of SQL ServerSQL Server. 包含使用者定義資料表和暫存資料表,但不包含資料表變數。Includes user-defined tables and temp tables, but not table variables.

Transact-SQLTransact-SQL 會自動判斷 SQL ServerSQL Server 函數的有效位數和決定性屬性。The precision and determinism properties of Transact-SQLTransact-SQL functions are determined automatically by SQL ServerSQL Server. 使用者可以指定 CLR 函數的資料存取和決定性屬性。The data access and determinism properties of CLR functions can be specified by the user. 如需詳細資訊,請參閱 CLR 整合自訂屬性的概觀For more information, see Overview of CLR Integration Custom Attributes.

若要顯示這些屬性目前的值,請使用 OBJECTPROPERTYEXTo display the current values for these properties, use OBJECTPROPERTYEX.

重要

建立函式時,必須使用具決定性的 SCHEMABINDING 來建立。Functions must be created with SCHEMABINDING to be deterministic.

當使用者定義函數有下列屬性值時,可以在索引中使用可叫用使用者定義函數的計算資料行。A computed column that invokes a user-defined function can be used in an index when the user-defined function has the following property values:

  • IsDeterministic = trueIsDeterministic = true
  • IsSystemVerified = true (除非保存計算資料行)IsSystemVerified = true (unless the computed column is persisted)
  • UserDataAccess = falseUserDataAccess = false
  • SystemDataAccess = falseSystemDataAccess = false

如需詳細資訊,請參閱 計算資料行的索引For more information, see 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. 任何將結果集傳回用戶端的 ODS API 都會傳回 FAIL。Any ODS APIs that return result sets to the client will return FAIL. 擴充預存程序可能會連回到 SQL ServerSQL Server 的執行個體;不過,它不應該嘗試將相同的交易聯結為叫用擴充預存程序的函數。The extended stored procedure could connect back to an instance of SQL ServerSQL Server; however, it should not try to join the same transaction as the function that invoked the extended stored procedure.

與從批次或預存程序進行的引動過程相似,擴充預存程序會在執行 SQL ServerSQL Server 的 Windows 安全性帳戶所在的內容中執行。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 ServerSQL Server is running. 當預存程序的擁有者將預存程序上的 EXECUTE 權限提供給使用者時,該擁有者應考量前述的情形。The owner of the stored procedure should consider this when giving EXECUTE permission on it to users.

限制事項Limitations and Restrictions

使用者定義函數不能用來執行修改資料庫狀態的動作。User-defined functions cannot be used to perform actions that modify the database state.

使用者定義函式不得包含具有資料表作為其目標的 OUTPUT INTO 子句。User-defined functions cannot contain an OUTPUT INTO clause that has a table as its target.

下列 Service BrokerService Broker 陳述式不能併入 Transact-SQLTransact-SQL 使用者定義函數的定義中:The following Service BrokerService Broker statements cannot be included in the definition of a Transact-SQLTransact-SQL user-defined function:

  • BEGIN DIALOG CONVERSATION

  • END CONVERSATION

  • GET CONVERSATION GROUP

  • MOVE CONVERSATION

  • RECEIVE

  • SEND

使用者定義函數可以具有巢狀結構;也就是說,某個使用者定義函數可以呼叫另一個使用者定義函數。User-defined functions can be nested; that is, one user-defined function can call another. 被呼叫的函數開始執行時,巢狀層級會遞增;被呼叫的函數完成執行時,巢狀層級會遞減。The nesting level is incremented when the called function starts execution, and decremented when the called function finishes execution. 使用者定義函數所建立的巢狀結構最多可以有 32 個層級。User-defined functions can be nested up to 32 levels. 超過巢狀層級上限會導致整個呼叫函數鏈結失敗。Exceeding the maximum levels of nesting causes the whole calling function chain to fail. 依照 32 個層級巢狀限制,Transact-SQLTransact-SQL 使用者定義函數之 Managed 程式碼的任何參考都算是一個層級。Any reference to managed code from a Transact-SQLTransact-SQL user-defined function counts as one level against the 32-level nesting limit. 從 Managed 程式碼內叫用的方法,不列入這項限制。Methods invoked from within managed code do not count against this limit.

在 CLR 資料表值函式中使用排序次序Using Sort Order in CLR Table-valued Functions

當您在 CLR 資料表值函式中使用 ORDER 子句時,請遵循以下指導方針: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. 如果結果未以指定的順序來排序,SQL ServerSQL Server 將會在執行查詢時產生錯誤訊息。If the results are not in the specified order, SQL ServerSQL Server will generate an error message when the query is executed.

  • 如果指定了 ORDER 子句,資料表值函式的輸出必須根據資料行的定序 (明確或隱含) 來排序。If an ORDER clause is specified, the output of the table-valued function must be sorted according to the collation of the column (explicit or implicit). 例如,如果資料行定序為中文 (指定在資料表值函式的 DDL 中或是從資料庫定序取得),傳回的結果必須根據中文排序規則來排序。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 be sorted according to Chinese sorting rules.

  • 如果指定了 ORDER 子句,一律會在傳回結果時由 SQL ServerSQL Server 來加以驗證,不論查詢處理器是否會使用它來執行進一步的最佳化。The ORDER clause, if specified, is always verified by SQL ServerSQL Server while returning results, whether or not it is used by the query processor to perform further optimizations. 只有在您知道 ORDER 子句對查詢處理器很有用時使用它。Only use the ORDER clause if you know it is useful to the query processor.

  • SQL ServerSQL Server 查詢處理器會在下列情況下自動利用 ORDER 子句:The SQL ServerSQL Server query processor takes advantage of the ORDER clause automatically in following cases:

    • ORDER 子句與索引相容的插入查詢。Insert queries where the ORDER clause is compatible with an index.

    • ORDER 子句相容的 ORDER BY 子句。ORDER BY clauses that are compatible with the ORDER clause.

    • GROUP BYORDER 子句相容的彙總。Aggregates, where GROUP BY is compatible with ORDER clause.

    • 相異資料行與 ORDER 子句相容的 DISTINCT 彙總。DISTINCT aggregates where the distinct columns are compatible with the ORDER clause.

除非同時在查詢中指定 ORDER BY 子句,否則 ORDER 子句並不保證在執行 SELECT 查詢時會傳回排序的結果。The ORDER clause does not guarantee ordered results when a SELECT query is executed, unless ORDER BY is also specified in the query. 如需有關如何查詢資料表值函式之排序次序中所含資料行的資訊,請參閱 sys.function_order_columns (Transact-SQL)See sys.function_order_columns (Transact-SQL) for information on how to query for columns included in the sort-order for table-valued functions.

中繼資料Metadata

下表列出您可以用來傳回使用者定義函數之中繼資料的系統目錄檢視表。The following table lists the system catalog views that you can use to return metadata about user-defined functions.

系統檢視表System View DescriptionDescription
sys.sql_modulessys.sql_modules 請參閱下方<範例>一節中的範例 E。See example E in the Examples section below.
sys.assembly_modulessys.assembly_modules 顯示 CLR 使用者定義函數的相關資訊。Displays information about CLR user-defined functions.
sys.parameterssys.parameters 顯示使用者定義函數中定義之參數的相關資訊。Displays information about the parameters defined in user-defined functions.
sys.sql_expression_dependenciessys.sql_expression_dependencies 顯示函數所參考的基礎物件。Displays the underlying objects referenced by a function.

權限Permissions

需要資料庫中的 CREATE FUNCTION 權限,以及建立此函式所在結構描述上的 ALTER 權限。Requires CREATE FUNCTION permission in the database and ALTER permission on the schema in which the function is being created. 如果此函式指定使用者定義型別,則需要該型別的 EXECUTE 權限。If the function specifies a user-defined type, requires EXECUTE permission on the type.

範例Examples

注意

如需 UDF 的更多範例和效能考量事項,請參閱建立使用者定義函式 (資料庫引擎)For more examples and performance considerations about UDFs, see Create User-defined Functions (Database Engine).

A.A. 使用計算 ISO 週的純量值使用者定義函數Using a scalar-valued user-defined function that calculates the ISO week

下列範例會建立使用者定義函數 ISOweekThe following example creates the user-defined function ISOweek. 這個函數採用日期引數並計算 ISO 週數。This function takes a date argument and calculates the ISO week number. 若要使函數能夠正確計算,必須先叫用 SET DATEFIRST 1,才能呼叫該函數。For this function to calculate correctly, SET DATEFIRST 1 must be invoked before the function is called.

此範例也說明如何使用 EXECUTE AS 子句來指定可執行預存程序的安全性內容。The example also shows using the EXECUTE AS clause to specify the security context in which a stored procedure can be executed. 在這個範例中,選項 CALLER 會指定將在呼叫程序之使用者的內容中執行程序。In the example, the option CALLER specifies that the procedure will be executed in the context of the user that calls it. 您可以指定的其他選項為 SELFOWNERuser_nameThe other options that you can specify are SELF, OWNER, and user_name.

以下是函數呼叫。Here is the function call. 請注意,DATEFIRST 是設為 1Notice that DATEFIRST is set to 1.

CREATE FUNCTION dbo.ISOweek (@DATE datetime)  
RETURNS int  
WITH EXECUTE AS CALLER  
AS  
BEGIN  
     DECLARE @ISOweek int;  
     SET @ISOweek= DATEPART(wk,@DATE)+1  
          -DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104');  
--Special cases: Jan 1-3 may belong to the previous year  
     IF (@ISOweek=0)   
          SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1   
               AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1;  
--Special case: Dec 29-31 may belong to the next year  
     IF ((DATEPART(mm,@DATE)=12) AND   
          ((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))  
          SET @ISOweek=1;  
     RETURN(@ISOweek);  
END;  
GO  
SET DATEFIRST 1;  
SELECT dbo.ISOweek(CONVERT(DATETIME,'12/26/2004',101)) AS 'ISO Week';  

以下為結果集:Here is the result set.

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

B.B. 建立內嵌資料表值函式Creating an inline table-valued function

下列範例會傳回 AdventureWorks2012AdventureWorks2012 資料庫中的內嵌資料表值函式。The following example returns an inline table-valued function in the AdventureWorks2012AdventureWorks2012 database. 它會傳回三個資料行:ProductIDName,以及年初至今銷售到商店之每項產品的總計彙總 YTD Total (依商店區分)。It returns three columns ProductID, Name and the aggregate of year-to-date totals by store as YTD Total for each product sold to the store.

CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int)  
RETURNS TABLE  
AS  
RETURN   
(  
    SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'Total'  
    FROM Production.Product AS P   
    JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID  
    JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID  
    JOIN Sales.Customer AS C ON SH.CustomerID = C.CustomerID  
    WHERE C.StoreID = @storeid  
    GROUP BY P.ProductID, P.Name  
);  
GO  

若要叫用函數,請執行這項查詢。To invoke the function, run this query.

SELECT * FROM Sales.ufn_SalesByStore (602);  

C.C. 建立多重陳述式資料表值函式Creating a multi-statement table-valued function

下列範例會在 AdventureWorks2012 資料庫中建立資料表值函式 fn_FindReports(InEmpID)The following example creates the table-valued function fn_FindReports(InEmpID) in the AdventureWorks2012 database. 當提供有效的員工識別碼時,此函數會傳回對應於所有員工的資料表,該資料表會直接或間接報告給員工。When supplied with a valid employee ID, the function returns a table that corresponds to all the employees that report to the employee either directly or indirectly. 此函數會利用遞迴通用資料表運算式 (CTE) 來產生階層式員工清單。The function uses a recursive common table expression (CTE) to produce the hierarchical list of employees. 如需有關遞迴 CTE 的詳細資訊,請參閱 WITH common_table_expression (Transact-SQL)For more information about recursive CTEs, see WITH common_table_expression (Transact-SQL).

CREATE FUNCTION dbo.ufn_FindReports (@InEmpID INTEGER)  
RETURNS @retFindReports TABLE   
(  
    EmployeeID int primary key NOT NULL,  
    FirstName nvarchar(255) NOT NULL,  
    LastName nvarchar(255) NOT NULL,  
    JobTitle nvarchar(50) NOT NULL,  
    RecursionLevel int NOT NULL  
)  
--Returns a result set 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 name and columns  
    AS (  
        -- Get the initial list of Employees for Manager n
        SELECT e.BusinessEntityID, e.OrganizationNode, 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  
        UNION ALL  
        -- Join recursive member to anchor
        SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, RecursionLevel + 1   
        FROM HumanResources.Employee e   
            INNER JOIN EMP_cte  
            ON e.OrganizationNode.GetAncestor(1) = EMP_cte.OrganizationNode  
INNER JOIN Person.Person p   
ON p.BusinessEntityID = e.BusinessEntityID  
        )  
-- copy the required columns to the result of the function   
   INSERT @retFindReports  
   SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel  
   FROM EMP_cte   
   RETURN  
END;  
GO  
-- Example invocation  
SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel  
FROM dbo.ufn_FindReports(1);   
  
GO  

D.D. 建立 CLR 函數Creating a CLR function

此範例會建立 CLR 函式 len_sThe example creates CLR function len_s. 在建立這個函數之前,已在本機資料庫中註冊組件 SurrogateStringFunction.dllBefore the function is created, the assembly SurrogateStringFunction.dll is registered in the local database.

適用於SQL ServerSQL Server (SQL Server 2008SQL Server 2008 SP1 及更新版本)Applies to: SQL ServerSQL Server ( SQL Server 2008SQL Server 2008 SP1 and later)

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

如需如何建立 CLR 資料表值函式的範例,請參閱 CLR 資料表值函式For an example of how to create a CLR table-valued function, see CLR Table-Valued Functions.

E.E. 顯示 Transact-SQLTransact-SQL 使用者定義函式的定義Displaying the definition of Transact-SQLTransact-SQL user-defined functions

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

使用 ENCRYPTION 選項建立的函式定義無法使用 sys.sql_modules 來檢視,但是會顯示與加密函式相關的其他資訊。The definition of functions created by using the ENCRYPTION option cannot be viewed by using sys.sql_modules; however, other information about the encrypted functions is displayed.

另請參閱See Also

建立使用者定義函數 (Database Engine) Create User-defined Functions (Database Engine)
ALTER FUNCTION (Transact-SQL) ALTER FUNCTION (Transact-SQL)
DROP FUNCTION (Transact-SQL) DROP FUNCTION (Transact-SQL)
OBJECTPROPERTYEX (Transact-SQL) OBJECTPROPERTYEX (Transact-SQL)
sys.sql_modules (Transact-SQL) sys.sql_modules (Transact-SQL)
sys.assembly_modules (Transact-SQL) sys.assembly_modules (Transact-SQL)
EXECUTE (Transact-SQL) EXECUTE (Transact-SQL)
CLR 使用者定義函式 CLR User-Defined Functions
EVENTDATA (Transact-SQL) EVENTDATA (Transact-SQL)
CREATE SECURITY POLICY (Transact-SQL)CREATE SECURITY POLICY (Transact-SQL)