CREATE 函數（Transact-SQL）

2025-06-21

適用於：SQL Server Azure SQL Database Azure SQL 受控實例

創建使用者定義函數（UDF），該函數是 Transact-SQL 或公共語言運行時（CLR）例程。使用者定義的函數接受參數，執行作（如複雜計算），並將該作的結果作為值返回。返回值可以是標量（單個）值或表。使用此語句建立可按以下方式使用的可重用例程：

在 Transact-SQL 語句中，例如 SELECT
在調用函數
在另一個使用者定義函數的定義中
參數化檢視或改進索引視圖的功能
定義表中的列
定義 CHECK 對列的約束
替換存儲過程
使用內聯函數作為安全策略的篩選條件謂詞

本文討論了 .NET Framework CLR 與 SQL Server 的集成。 CLR 集成不適用於 Azure SQL 資料庫。

對於 Azure Synapse Analytics 或 Microsoft Fabric，請參閱 CREATE FUNCTION（Azure Synapse Analytics 和 Microsoft Fabric）。

Transact-SQL 語法慣例

語法

Transact-SQL 標量函數的語法。

CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ NULL ]
 [ = default ] [ READONLY ] }
    [ , ...n ]
  ]
)
RETURNS return_data_type
    [ WITH <function_option> [ , ...n ] ]
    [ AS ]
    BEGIN
        function_body
        RETURN scalar_expression
    END
[ ; ]

Transact-SQL 內聯表值函數的語法。

CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ NULL ]
    [ = default ] [ READONLY ] }
    [ , ...n ]
  ]
)
RETURNS TABLE
    [ WITH <function_option> [ , ...n ] ]
    [ AS ]
    RETURN [ ( ] select_stmt [ ) ]
[ ; ]

Transact-SQL 多語句表值函數的語法。

CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ NULL ]
    [ = default ] [ READONLY ] }
    [ , ...n ]
  ]
)
RETURNS @return_variable TABLE <table_type_definition>
    [ WITH <function_option> [ , ...n ] ]
    [ AS ]
    BEGIN
        function_body
        RETURN
    END
[ ; ]

Transact-SQL 函數子句的語法。

<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 標量函數的語法。

CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ NULL ]
    [ = default ] }
    [ , ...n ]
)
RETURNS { return_data_type }
    [ WITH <clr_function_option> [ , ...n ] ]
    [ AS ] EXTERNAL NAME <method_specifier>
[ ; ]

CLR 表值函數的語法。

CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ NULL ]
    [ = default ] }
    [ , ...n ]
)
RETURNS TABLE <clr_table_type_definition>
    [ WITH <clr_function_option> [ , ...n ] ]
    [ ORDER ( <order_clause> ) ]
    [ AS ] EXTERNAL NAME <method_specifier>
[ ; ]

CLR 函數子句的語法。

<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 ] )

本機編譯的標量使用者定義函數的記憶體中 OLTP 語法。

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

論點

OR ALTER

適用於：SQL Server 2016 （13.x） SP 1 及更高版本，以及 Azure SQL 資料庫。

僅當函數已存在時，才有條件地更改該函數。

可選 OR ALTER 語法可用於 CLR，從 SQL Server 2016 （13.x） SP 1 CU 1 開始。

schema_name

使用者定義函數所屬的架構的名稱。

function_name

用戶定義函數的名稱。函數名稱必須符合標識符規則，並且在資料庫及其架構中必須是唯一的。

函數名稱后需要括弧，即使未指定參數也是如此。

@parameter_name

用戶定義函數中的參數。可以聲明一個或多個參數。

一個函數最多可以有 2,100 個參數。執行函數時，用戶必須提供每個聲明的參數的值，除非定義了參數的預設值。

通過使用 at 符號（@）作為第一個字元來指定參數名稱。參數名稱必須符合標識符的規則。參數是函數的本地參數;其他函數中可以使用相同的參數名稱。參數只能代替常量;它們不能用於代替表名、列名或其他資料庫對象的名稱。

ANSI_WARNINGS 在存儲過程、使用者定義函數中傳遞參數或在 Batch 語句中聲明和設置變數時，不遵循。例如，若將變數定義為 char(3) ，然後將其設為大於三個字元的值，資料便會被截斷成定義的大小，且 INSERT 或 UPDATE 陳述式會執行成功。

[ type_schema_name. ] parameter_data_type

參數數據類型，以及它所屬的架構（可選）。對於 Transact-SQL 函數，允許除 timestamp 數據類型之外的所有資料類型，包括 CLR 使用者定義類型和使用者定義表類型。對於 CLR 函數，允許所有數據類型，包括 CLR 使用者定義類型，但 text、 ntext、 image、使用者定義的表類型和 timestamp 數據類型除外。非標量類型（遊標和表）不能在 Transact-SQL 或 CLR 函數中指定為參數數據類型。

如果未指定 type_schema_name ，則資料庫引擎將按以下順序尋找 scalar_parameter_data_type ：

包含 SQL Server 系統數據類型名稱的架構。
目前資料庫中之目前使用者的預設結構描述。
目前資料庫中的 dbo 架構。

[ = 預設 ]

參數的預設值。如果定義了 預設值 ，則可以在不為該參數指定值的情況下執行該函數。

可以為 CLR 函數指定預設參數值， 但 varchar（max） 和 varbinary（max） 數據類型除外。

當函數的參數具有預設值時，必須在調用函數以檢索預設值時指定關鍵字 DEFAULT 。此行為與在存儲過程中使用具有預設值的參數不同，在存儲過程中，省略參數也意味著預設值。但是， DEFAULT 當使用 EXECUTE 語句調用標量函數時，該關鍵字不是必需的。

唯讀

指示無法在函數定義中更新或修改參數。 READONLY 對於使用者定義的表類型參數（TVP）是必需的，不能用於任何其他參數類型。

return_data_type

標量使用者定義函數的返回值。對於 Transact-SQL 函數，允許除 timestamp 數據類型之外的所有數據類型，包括 CLR 使用者定義類型。對於 CLR 函數，允許使用除 text、 ntext、 image 和 timestamp 數據類型之外的所有數據類型，包括 CLR 使用者定義類型。非標量類型（ cursor 和 table）不能在 Transact-SQL 或 CLR 函數中指定為返回資料類型。

function_body

指定一系列 Transact-SQL 語句（它們一起不會產生副作用，例如修改表）定義函數的值。 function_body 僅用於標量函數和多語句表值函數（MSTVF）。

在標量函數中， function_body 是一系列 Transact-SQL 語句，這些語句一起計算為標量值。

在 MSTVF 中， function_body 是一系列填充 TABLE return 變數的 Transact-SQL 語句。

scalar_expression

指定標量函數返回的標量值。

表格

指定表值函數（TVF）的返回值為表。只有常量和 @local_variables 可以傳遞給TVF。

在內聯TVF中， TABLE 返回值是通過單個 SELECT 語句定義的。內聯函數沒有關聯的 return 變數。

在 MSTVF 中， @return_variable 是一個 TABLE 變數，用於存儲和累積應作為函數值返回的行。 @ return_variable 只能為 Transact-SQL 函數指定，而不能為CLR函數指定。

select_stmt

定義內聯表值函數（TVF）的返回值的單個 SELECT 語句。

訂購（<order_clause>）

指定從表值函數返回結果的順序。有關詳細資訊，請參閱本文後面的在 CLR表值函數中使用排序順序部分。

外部名稱 <method_specifier>assembly_name。class_name。method_name

適用於：SQL Server 2008 （10.0.x） SP 1 及更高版本。

指定創建的函數名稱應引用的程式集和方法。

assembly_name - 必須與列中SELECT * FROM sys.assemblies;的值name匹配。

對帳單上使用 CREATE ASSEMBLY 的名稱。
class_name - 必須與列中SELECT * FROM sys.assembly_modules;的值assembly_name匹配。

該值通常包含嵌入的句點或點。在這種情況下，Transact-SQL 語法要求值用一對方括弧（）[] 或一對雙引號（）"" 括起來。
method_name - 必須與列中SELECT * FROM sys.assembly_modules;的值method_name匹配。

方法必須是靜態的。

在的典型範例中 MyFood.dll，所有類型都位於命名空間中 MyFood ， EXTERNAL NAME 該值可以是 MyFood.[MyFood.MyClass].MyStaticMethod。

默認情況下，SQL Server 無法執行 CLR 代碼。您可以建立、修改和刪除引用公共語言運行時模組的資料庫物件。但是，在啟用 clr enabled 選項之前，您無法在 SQL Server 中執行這些引用。要啟用此選項，請使用 sp_configure。自主資料庫中無法使用此選項。

< > table_type_definition （ { <column_definition><column_constraint | <> computed_column_definition } [ <table_constraint> ] [ ， ...n ] ）

定義 Transact-SQL 函數的表數據類型。表聲明包括列定義和列或表約束。該表始終放在主檔組中。

< > clr_table_type_definition （ { column_namedata_type } [ ， ...n ] ）

適用於：SQL Server 2008 （10.0.x） SP 1 及更高版本，以及 Azure SQL 資料庫（在某些區域提供預覽版）。

定義 CLR 函數的表數據類型。表聲明僅包括列名和數據類型。該表始終放在主檔組中。

NULL |NOT NULL

僅支援本機編譯的標量用戶定義函數。有關詳細資訊，請參閱適用於 In-Memory OLTP 的標量 User-Defined 函數。

NATIVE_COMPILATION

指示使用者定義的函數是否是本機編譯的。本機編譯的標量使用者定義函數需要此參數。

從 ATOMIC 開始

對於本機編譯的標量使用者定義函數是必需的，並且僅支援。有關更多資訊，請參見本機過程中的原子塊。

SCHEMABINDING

該 SCHEMABINDING 參數對於本機編譯的標量用戶定義函數是必需的。

以指定身分執行

EXECUTE AS 對於本機編譯的標量用戶定義函數是必需的。

< > function_option ：：= 和 <clr_function_option> ：：=

指定函數具有以下一個或多個選項。

加密

適用於：SQL Server 2008 （10.0.x） SP 1 及更高版本。

指示資料庫引擎將語句的原始 CREATE FUNCTION 文本轉換為模糊格式。模糊處理的輸出在任何目錄檢視中都不會直接可見。無權訪問系統表或資料庫檔的用戶無法檢索經過混淆處理的文本。但是，該文本可供特權使用者使用，這些使用者可以通過資料庫管理員的 Diagnostic 連接訪問系統表，也可以直接存取資料庫檔。此外，可以將除錯器附加到伺服器進程的使用者可以在運行時從記憶體中檢索原始過程。有關訪問系統元數據的更多資訊，請參閱元數據可見性配置。

使用此選項可防止函數作為 SQL Server 複製的一部分發佈。不能為 CLR 函數指定此選項。

SCHEMABINDING

指定函數綁定到它引用的資料庫物件。指定后 SCHEMABINDING ，不能以影響函數定義的方式修改基物件。必須首先修改或刪除函數定義本身，以刪除對要修改的對象的依賴關係。

僅當發生以下作之一時，才會刪除函數與其引用的對象的綁定：

該函數被刪除。
使用 ALTER 帶有 SCHEMABINDING option not specified的語句修改函數。

僅當滿足以下條件時，函數才能進行架構綁定：

該函數是一個 Transact-SQL 函數。
函數引用的使用者定義函數和檢視也是架構綁定的。
函數引用的物件使用由兩部分組成的名稱進行引用。
該函數及其引用的對象屬於同一個資料庫。
執行 CREATE FUNCTION 該語句的使用者對函數引用的資料庫物件具有 REFERENCES 許可權。

在 NULL 輸入時返回 NULL |在 NULL 輸入時調用

OnNULLCall指定標量函數的屬性。如果未指定， CALLED ON NULL INPUT 則預設為 implation。換句話說，即使 NULL 作為參數傳遞，函數體也會執行。

如果在 RETURNS NULL ON NULL INPUT CLR 函數中指定，則表示 SQL Server 可以在它收到的任何參數為 NULL時返回NULL，而無需實際調用函數的主體。如果中指定的 <method_specifier> CLR 函數的方法已經具有指示 RETURNS NULL ON NULL INPUT的自定義屬性， CREATE FUNCTION 但該語句指示 CALLED ON NULL INPUT，則該 CREATE FUNCTION 語句優先。 OnNULLCall不能為 CLR 表值函數指定該屬性。

以指定身分執行

指定在其下執行使用者定義函數的安全上下文。因此，您可以控制 SQL Server 使用哪個使用者帳戶來驗證函數引用的任何資料庫對象的許可權。

EXECUTE AS 不能為內聯表值函數指定。

如需詳細資訊，請參閱 EXECUTE AS 子句（Transact-SQL）。

內聯 = { ON |關閉 }

適用於： SQL Server 2019 （15.x）及更高版本，以及 Azure SQL 資料庫。

指定是否應內聯此標量 UDF。此子句僅適用於標量使用者定義函數。該 INLINE 子句不是必需的。如果未指定子句， INLINE 則會自動將其設置為 ON 或 OFF 基於 UDF 是否可內聯。如果 INLINE = ON 指定了，但發現UDF不可內聯，則會引發錯誤。如需詳細資訊，請參閱純量 UDF 內嵌。

< > column_definition ：：=

定義表數據類型。表聲明包括列定義和約束。對於 CLR 函數，只能指定 column_name 和 data_type 。

column_name

表中列的名稱。列名必須符合標識碼的規則，並且在表中必須是唯一的。 column_name 可以由 1 到 128 個字元組成。

data_type

指定資料行資料類型。對於 Transact-SQL 函數，允許除 timestamp 之外的所有數據類型，包括 CLR 使用者定義類型。對於 CLR 函數，允許除 text、ntext、image、char、varchar、varchar（max） 和 timestamp 之外的所有數據類型，包括 CLR 使用者定義類型。不能在 Transact-SQL 或 CLR 函數中將非標量類型遊標指定為列資料類型。

默認 constant_expression

指定在插入期間未明確提供值時，提供給資料行的值。 constant_expression 是常量或 NULL系統函數值。 DEFAULT 定義可以應用於任何列，但具有 property 的 IDENTITY 列除外。 DEFAULT 不能為 CLR 表值函數指定。

分套 collation_name

指定資料行的定序。如果未指定，則為該列分配資料庫的預設排序規則。定序名稱可以是 Windows 定序名稱或 SQL 定序名稱。有關排序規則的清單和詳細資訊，請參閱 Windows 排序規則名稱（Transact-SQL）和 SQL Server 排序規則名稱（Transact-SQL）。

該 COLLATE 子句只能用於更改 char、 varchar、 nchar 和 nvarchar 數據類型的列的排序規則。 COLLATE 不能為 CLR 表值函數指定。

ROWGUIDCOL

指示新列是行全域唯一標識符列。每個表只能將一個 uniqueidentifier 列指定為該 ROWGUIDCOL 列。該 ROWGUIDCOL 屬性只能分配給 uniqueidentifier 列。

該 ROWGUIDCOL 屬性不強制列中存儲的值的唯一性。它也不會自動生成插入到表中的新行的值。要為每個列生成唯一值，請使用 NEWID 函數 on INSERT statements。可以指定預設值;但是， NEWID 不能指定為 default。

身份

指出新資料行是識別欄位。向表中添加新行時，SQL Server 會為該列提供唯一的增量值。標識列通常與 PRIMARY KEY 約束一起使用，以用作表的唯一行標識碼。該 IDENTITY 屬性可以分配給 tinyint、 smallint、 int、 bigint、 decimal（p，0） 或 numeric（p，0） 列。每份資料表都只能建立一個識別欄位。綁定預設值和 DEFAULT 約束不能與標識列一起使用。您必須同時指定 seed 和 increment ，或者兩者都不指定。如果同時不指定這兩者，預設值便是 (1,1)。

IDENTITY 不能為 CLR 表值函數指定。

種子

要分配給表中第一行的整數值。

增加

要添加到表中連續行的種子值的整數值。

< > column_constraint ：：= 和 <table_constraint> ：：=

定義指定列或表的約束。對於 CLR 函數，唯一允許的約束類型是 NULL. 不允許使用命名約束。

NULL |NOT NULL

確定列中是否允許 null 值。 NULL 不是嚴格意義上的約束，但可以像 NOT NULL. NOT NULL 不能為 CLR 表值函數指定。

主鍵

通過唯一索引對指定列強制執行實體完整性的約束。在表值使用者定義函數中， PRIMARY KEY 只能對每個表的一列創建約束。 PRIMARY KEY 不能為 CLR 表值函數指定。

獨特

這項限制式會透過唯一索引為指定的一個或多個資料行提供實體完整性。一個表可以有多個 UNIQUE 約束。 UNIQUE 不能為 CLR 表值函數指定。

CLUSTERED |NONCLUSTERED

指示為 or UNIQUE 約束創建PRIMARY KEY聚集索引或非聚集索引。 PRIMARY KEY 約束使用 CLUSTERED， UNIQUE 約束使用 NONCLUSTERED。

CLUSTERED 只能為一個約束指定。如果CLUSTERED為約束指定了約束，並且PRIMARY KEY還指定了約束，則使用 PRIMARY KEYNONCLUSTERED。UNIQUE

CLUSTERED ， NONCLUSTERED 並且不能為 CLR 表值函數指定。

請檢查

透過限制可能輸入一或多個資料行的值來強制執行值域完整性的限制式。 CHECK 不能為 CLR 表值函數指定約束。

logical_expression

返回 TRUE or 的 FALSE邏輯表達式。

< > computed_column_definition ：：=

指定計算列。有關計算列的更多資訊，請參閱 CREATE TABLE （Transact-SQL）。

column_name

計算列的名稱。

computed_column_expression

定義計算資料行值的運算式。

<index_option> ：：=

指定 or UNIQUE 索引的PRIMARY KEY索引選項。有關索引選項的更多資訊，請參閱 CREATE INDEX （Transact-SQL）。

PAD_INDEX = { ON |OFF }

指定索引填補。預設值為 OFF。

FILLFACTOR = fillfactor

指定一個百分比，該百分比指示在索引創建或更改期間，資料庫引擎應使每個索引頁的葉級別填充的程度。 fillfactor 必須是從 1 到 100 的整數值。預設值為 0。

IGNORE_DUP_KEY = { ON |OFF }

指定當插入作業嘗試將重複的索引鍵值插入唯一索引時所產生的錯誤回應。 IGNORE_DUP_KEY 選項只適用於在建立或重新編製索引之後所發生的插入作業。預設值為 OFF。

STATISTICS_NORECOMPUTE = { ON |OFF }

指定是否重新計算分佈統計資訊。預設值為 OFF。

ALLOW_ROW_LOCKS = { ON |OFF }

指定是否允許數據列鎖定。預設值為 ON。

ALLOW_PAGE_LOCKS = { ON |OFF }

指定是否允許頁面鎖定。預設值為 ON。

最佳做法

如果未使用 SCHEMABINDING 子句創建使用者定義的函數，則對基礎物件所做的更改可能會影響函數的定義，並在調用該函數時產生意外結果。建議您實作下列其中一個方法，以確保函式不會因為其基礎物件的變更而變成過期：

WITH SCHEMABINDING在創建函數時指定子句。此選項可確保無法修改函數定義中引用的物件，除非該函數也被修改。
在修改函數定義中指定的任何物件後執行 sp_refreshsqlmodule 存儲過程。

有關內聯表值函數（內聯TVF）和多語句表值函數（MSTVF）的詳細資訊和性能注意事項，請參閱創建使用者定義的函數（資料庫引擎）。

數據類型

如果在 CLR 函數中指定了參數，則它們應為前面為 scalar_parameter_data_type 定義的 SQL Server 類型。有關將 SQL Server 系統數據類型與 CLR 集成數據類型或 .NET Framework 公共語言運行時數據類型進行比較的更多資訊，請參見映射 CLR 參數數據。

要使 SQL Server 在類中重載時引用正確的方法，中 <method_specifier> 指示的方法必須具有以下特徵：

接收與中指定的相同數量的參數 [ , ...n ]。
按值而不是按引用接收所有參數。
使用與 SQL Server 函數中指定的類型相容的參數類型。

如果 CLR 函數的傳回資料類型指定了表型態（），RETURNS TABLE則中的 <method_specifier> 方法的返回資料類型應為 IEnumerator 或 IEnumerable類型，並且它假定該介面由函數的建立者實現。與 Transact-SQL 函數不同，CLR 函數不能在 PRIMARY KEYUNIQUECHECK<table_type_definition>. 中指定的 <table_type_definition> 列的數據類型必須與執行時 method 返回 <method_specifier> 的結果集的相應列的類型匹配。創建函數時不會執行此類型檢查。

有關如何對 CLR 函數進行程式設計的更多資訊，請參見 CLR User-Defined 函數。

備註

可以在使用標量表達式（包括計算列和 CHECK 約束定義）的位置調用標量函數。標量函數也可以使用 EXECUTE （Transact-SQL）語句來執行。叫用純量函式必須至少使用函式的兩部分名稱 (<schema>.<function>)。有關分段名稱的更多資訊，請參閱 Transact-SQL 語法約定（Transact-SQL）。如果、 UPDATEINSERTDELETE 、或 statements 的子句SELECT中FROM允許使用表運算式，則可以調用表值函數。有關更多資訊，請參閱執行使用者定義的函數。

互操作性

以下語句在函數中有效：

賦值語句。
Control-of-Flow 語句，語句除外 TRY...CATCH 。
DECLARE 定義局部數據變數和局部遊標的語句。
SELECT 包含選擇清單的語句，這些清單具有為局部變數賦值的值的表達式。
引用函數中聲明、打開、關閉和釋放的本地游標的遊標作。只 FETCH 允許使用子句 assign 值給局部變數 INTO 的語句; FETCH 不允許使用將數據返回給客戶端的語句。
INSERT、 UPDATE和 DELETE 修改局部表變數的語句。
EXECUTE 調用擴展存儲過程的語句。

有關詳細資訊，請參閱創建使用者定義的函數（資料庫引擎）。

計算列互作性

函數具有以下屬性。這些屬性的值確定函數是否可以在可以持久保存或編製索引的計算列中使用。

房產	說明	註釋
`IsDeterministic`	函數是確定性的或非確定性的。	在確定性函數中允許本地數據訪問。例如，每當使用一組特定的輸入值調用函數時，如果函數始終返回相同的結果，並且具有相同的資料庫狀態，則這些函數將被標記為 deterministic。
`IsPrecise`	函數是精確的還是不精確的。	不精確函數包含浮點運算等作。
`IsSystemVerified`	函數的精度和確定性屬性可由 SQL Server 驗證。
`SystemDataAccess`	函數訪問 SQL Server 本地實例中的系統資料（系統目錄或虛擬系統表）。
`UserDataAccess`	函數訪問 SQL Server 本地實例中的用戶數據。	包括使用者定義的表和臨時表，但不包括表變數。

Transact-SQL 函數的精度和確定性屬性由 SQL Server 自動確定。 CLR 函數的數據訪問和確定性屬性可由使用者指定。有關更多資訊，請參見 CLR集成：CLR 例程的自定義屬性。

要顯示這些屬性的當前值，請使用 OBJECTPROPERTYEX （Transact-SQL）。

這很重要

必須創建 Functions SCHEMABINDING 才能具有確定性。

當使用者定義函數具有以下屬性值時，可以在索引中使用數據使用者定義函數的計算欄：

IsDeterministic 是 true
IsSystemVerified 是 true （除非持久儲存計算列）
UserDataAccess 是 false
SystemDataAccess 是 false

如需詳細資訊，請參閱計算資料行的索引。

從函數調用擴展存儲過程

從函數內部調用擴展存儲過程時，無法將結果集返回給用戶端。任何將結果集傳回給用戶端的 ODS API 都會傳回 FAIL. 擴展存儲過程可以連接回 SQL Server 的實例;但是，它不應嘗試聯接與調用擴展存儲過程的函數相同的事務。

與從批處理或存儲過程進行調用類似，擴展存儲過程在運行SQL Server 的 Windows 安全帳戶的上下文中執行。存儲過程的所有者在向使用者授予 EXECUTE 存儲過程的許可權時應考慮此方案。

局限性

使用者定義的函式不能用來執行修改資料庫狀態的動作。

使用者定義的函式不得包含具有資料表作為其目標的 OUTPUT INTO 子句。

以下 Service Broker 語句不能包含在 Transact-SQL 使用者定義函數的定義中：

BEGIN DIALOG CONVERSATION
END CONVERSATION
GET CONVERSATION GROUP
MOVE CONVERSATION
RECEIVE
SEND

使用者定義函數可以具有巢狀結構；也就是說，某個使用者定義函數可以呼叫另一個使用者定義函數。被呼叫的函數開始執行時，巢狀層級會遞增；被呼叫的函數完成執行時，巢狀層級會遞減。使用者定義函數所建立的巢狀結構最多可以有 32 個層級。超過巢狀層級上限會導致整個呼叫函數鏈失敗。根據 32 級巢狀限制，任何對 Transact-SQL 使用者自訂函數中的受控程式碼的參考都算作一個層級。若是從受控碼內叫用的方法，則不計入這項限制。

在 CLR 表值函數中使用排序順序

ORDER在 CLR 表值函數中使用子句時，請遵循以下準則：

您必須確保結果始終按指定順序排序。如果結果未按指定順序排列，則 SQL Server 會在執行查詢時生成錯誤消息。
ORDER如果指定了子句，則必須根據列的排序規則（顯式或隱式）對表值函數的輸出進行排序。例如，如果列排序規則為中文，則返回的結果必須按照中文排序規則進行排序。（排序規則在 DDL 中為表值函數指定，或從資料庫排序規則中獲取。
SQL Server 在返回結果時始終驗證 ORDER 子句（如果已指定），無論查詢處理器是否使用它來執行進一步的優化。僅當您知道該子句對查詢處理器有用時，才使用 ORDER 該子句。
在以下情況下，SQL Server 查詢處理器會自動利用該 ORDER 子句：
- 插入子句與索引相容的查詢 ORDER 。
- ORDER BY 與 ORDER 子句相容的子句。
- 聚合，其中 GROUP BY is compatible with ORDER 子句。
- DISTINCT 聚合，其中 distinct 列與 ORDER 子句相容。

該 ORDER 子句不保證在執行查詢時 SELECT 有序的結果，除非 ORDER BY 在查詢中也指定了它。有關如何查詢表值函數的排序順序中包含的列的資訊，請參閱 sys.function_order_columns （Transact-SQL）。

後設資料

下表列出了可用於返回有關用戶定義函數的元數據的系統目錄檢視。

系統檢視	說明
sys.sql_modules	請參閱 Examples 部分中的範例 E。
sys.assembly_modules	顯示有關 CLR 使用者定義函數的資訊。
sys.parameters	顯示有關在用戶定義的函數中定義的參數的資訊。
sys.sql_expression_dependencies	顯示函數引用的基礎物件。

權限

需要資料庫中的 CREATE FUNCTION 權限，以及建立此函式所在結構描述上的 ALTER 權限。如果此函式指定使用者定義型別，則需要該型別的 EXECUTE 權限。

範例

有關UDF的更多範例和性能注意事項，請參閱創建使用者定義的函數（資料庫引擎）。

A。使用計算 ISO 周的標量值使用者定義函數

以下示例建立 user-defined function ISOweek。此函式會採用日期自變數並計算 ISO 周數。要使此函數正確計算， SET DATEFIRST 1 必須在調用該函數之前調用該函數。

該範例還演示如何使用 EXECUTE AS Clause （Transact-SQL）子句來指定可在其中執行儲存過程的安全上下文。在該範例中，該選項 CALLER 指定在調用過程的使用者的上下文中執行該過程。您可以指定的其他選項包括 SELF、 OWNER和 user_name。

下面是函數調用。 DATEFIRST 設定為 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';

結果如下。

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

B. 創建內聯表值函數

以下示例返回 AdventureWorks2022 資料庫中的內聯表值函數。它返回三列 ProductID、 Name，以及按商店 YTD Total 向商店銷售的每種產品的年初至今總計的聚合。

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
    INNER JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID
    INNER JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID
    INNER JOIN Sales.Customer AS C ON SH.CustomerID = C.CustomerID
    WHERE C.StoreID = @storeid
    GROUP BY P.ProductID, P.Name
);
GO

要調用該函數，請運行此查詢。

SELECT * FROM Sales.ufn_SalesByStore (602);

C. 創建多語句表值函數

以下範例在AdventureWorks2022資料庫中建立表值函數fn_FindReports(InEmpID)。當提供有效的員工 ID 時，該函數將返回一個表，該表對應於直接或間接向員工報告的所有員工。該函數使用遞歸公用表表達式（CTE）生成員工的分層清單。有關遞歸 CTE 的更多資訊，請參閱 WITH common_table_expression （Transact-SQL）。

CREATE FUNCTION dbo.ufn_FindReports (@InEmpID INT)
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,
            OrganizationNode = ISNULL(e.OrganizationNode, CAST('/' 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
        
        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. 建立 CLR 函式

該範例建立 CLR 函數 len_s。在創建函數之前， SurrogateStringFunction.dll 程式集將在本地資料庫中註冊。

適用於：SQL Server 2008 （10.0.x） SP 1 及更高版本。

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 Table-Valued 函數。

E. 顯示使用者定義函數的定義

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

使用該 ENCRYPTION 選項建立的函數的定義不能通過使用 sys.sql_modules;但是，將顯示有關加密函數的其他資訊。

共用方式為

CREATE 函數 （Transact-SQL）

語法

論點

OR ALTER

schema_name

function_name

@parameter_name

[ type_schema_name. ] parameter_data_type

[ = 預設 ]

唯讀

return_data_type

function_body

scalar_expression

表格

select_stmt

訂購 （<order_clause>）

外部名稱 <method_specifier>assembly_name。class_name。method_name

< > table_type_definition （ { <column_definition><column_constraint | <> computed_column_definition } [ <table_constraint> ] [ ， ...n ] ）

< > clr_table_type_definition （ { column_namedata_type } [ ， ...n ] ）

NULL |NOT NULL

NATIVE_COMPILATION

從 ATOMIC 開始

SCHEMABINDING

以指定身分執行

< > function_option ：：= 和 <clr_function_option> ：：=

加密

SCHEMABINDING

在 NULL 輸入時返回 NULL |在 NULL 輸入時調用

以指定身分執行

內聯 = { ON |關閉 }

< > column_definition ：：=

column_name

data_type

默認 constant_expression

分套 collation_name

ROWGUIDCOL

身份

種子

增加

< > column_constraint ：：= 和 <table_constraint> ：：=

NULL |NOT NULL

主鍵

獨特

CLUSTERED |NONCLUSTERED

請檢查

logical_expression

< > computed_column_definition ：：=

column_name

computed_column_expression

<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 表值函數中使用排序順序

後設資料

權限

範例

A。 使用計算 ISO 周的標量值使用者定義函數

B. 創建內聯表值函數

C. 創建多語句表值函數

D. 建立 CLR 函式

E. 顯示使用者定義函數的定義

相關內容

意見反應

其他資源

CREATE 函數（Transact-SQL）

訂購（<order_clause>）

A。使用計算 ISO 周的標量值使用者定義函數