CREATE FUNCTION (Transact-SQL)

發行項
04/27/2024

適用於：SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體

建立使用者定義函式（UDF），這是 Transact-SQL 或 Common Language Runtime （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 語法慣例

Syntax

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 個參數。除非定義了參數的預設值，否則在執行函數時，使用者必須提供每個已宣告之參數的值。

使用 @ 記號當做第一個字元來指定參數名稱。參數名稱必須符合識別碼的規則。對函數而言，參數必須是本機參數；相同的參數名稱可以用在其他函數中。參數只能取代常數;無法使用它們，而不是數據表名稱、數據行名稱或其他資料庫物件的名稱。

ANSI_WARNINGS 當您在預存程式、使用者定義函式中傳遞參數，或在批次語句中宣告和設定變數時，則不會接受。例如，若將變數定義為 char(3) ，然後將其設為大於三個字元的值，資料便會被截斷成定義的大小，且 INSERT 或 UPDATE 陳述式會執行成功。

[ type_schema_name. ] parameter_data_type

參數數據類型，並選擇性地選擇其所屬的架構。就 Transact-SQL 函式而言，所有資料類型 (包括 CLR 使用者定義類型和使用者定義資料表類型) 都是允許的資料類型，但 timestamp 資料類型除外。針對 CLR 函式，除了 text、ntext、image、user-defined 數據表類型和 timestamp 數據類型之外，允許所有數據類型，包括 CLR 使用者定義型別。在 Transact-SQL 或 CLR 函式中，不可將非索引型 別數據 指標和 數據表指定為參數數據類型。

如果未指定type_schema_name，資料庫引擎會依下列順序尋找scalar_parameter_data_type：

內含 SQL Server 系統資料類型的結構描述。
目前資料庫中之目前使用者的預設結構描述。
目前資料庫中的 dbo 結構描述。

[ = 預設值 ]

參數的預設值。如果已定義 default 值，則不需為該參數指定值，即可執行函式。

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

當函式的參數具有預設值時，必須在呼叫函式以擷取預設值時指定關鍵詞 DEFAULT 。這個行為與使用預存程序中具有預設值的參數不一樣，因為在預存程序中，省略參數也意味著使用預設值。不過， DEFAULT 使用語句叫用 EXECUTE 純量函式時，不需要關鍵詞。

READONLY

表示參數無法在函式的定義內更新或修改。 READONLY 使用者定義數據表類型參數需要，而且不能用於任何其他參數類型。

return_data_type

純量使用者定義函數的傳回值。就 Transact-SQL 函式而言，所有資料類型 (包括 CLR 使用者定義型別) 都是允許的資料類型，但 timestamp 資料類型除外。就 CLR 函式而言，所有資料類型 (包括 CLR 使用者定義類型) 都是允許的資料類型，但 textntextimage 及 timestamp 資料類型除外。在 Transact-SQL 或 CLR 函式中，無法將非可傳回型 別數據 指標和 數據表指定為傳回數據類型。

function_body

指定一連串的 Transact-SQL 語句不會產生副作用，例如修改數據表、定義函式的值。 function_body 僅用於純量函式和多重陳述式資料表值函式 (MSTVF) 中。

在純量函式中，function_body 是一系列的 Transact-SQL 陳述式，這些陳述式會一起評估為純量值。

在 MSTVF 中， function_body 是一系列的 Transact-SQL 語句，可填入 TABLE 傳回變數。

scalar_expression

指定純量函數傳回的純量值。

TABLE

指定資料表值函式 (TVF) 的傳回值是資料表。只有常數和 @local_variables 才能傳遞給 TVF。

在內嵌TVF中，傳 TABLE 回值是透過單 SELECT 一語句來定義。內嵌函式沒有相關聯的傳回變數。

在 MSTVF 中， @return_variable 是變數 TABLE ，用來儲存和累積應該傳回為函式值的數據列。您只能針對 Transact-SQL 函式指定 @return_variable，不能針對 CLR 函式指定。

select_stmt

單 SELECT 一語句，定義內嵌數據表值函式（TVF）的傳回值。

ORDER (<order_clause>)

指定從資料表值函式中傳回結果的順序。如需詳細資訊，請參閱本文稍後的<在CLR數據表值函式中使用排序順序>一節。

EXTERNAL NAME <method_specifier>assembly_name.class_name.method_name

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

指定建立函式名稱時應該要參考的組件和方法。

assembly_name - 必須符合 name 資料行中的值，此資料行屬於 SELECT * FROM sys.assemblies;。

語句上使用 CREATE ASSEMBLY 的名稱。
class_name - 必須符合 assembly_name 資料行中的值，此資料行屬於 SELECT * FROM sys.assembly_modules;。

值常包含內嵌的句號或點。在這種情況下，Transact-SQL 語法會要求值系結成一對方括弧（[]），或具有雙引號（""）。
method_name - 必須符合 method_name 資料行中的值，此資料行屬於 SELECT * FROM sys.assembly_modules;。

方法必須為靜態。

在的 MyFood.dll一般範例中，所有型別都在命名空間中 MyFood ， EXTERNAL NAME 值可以是 MyFood.[MyFood.MyClass].MyStaticMethod。

依預設，SQL Server 無法執行 CLR 程式碼。您可以建立、修改及卸除參考 Common Language Runtime 模組的資料庫物件。不過，在啟用 clr 啟用選項之前，您無法在 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

只有針對原生編譯的純量使用者定義函式才支援。如需詳細資訊，請參閱記憶體內部 OLTP 的純量使用者定義函數。

NATIVE_COMPILATION

指出使用者定義函式是否為原生編譯函式。此引數是原生編譯之純量使用者定義函式的必要引數。

BEGIN ATOMIC WITH

原生編譯純量使用者定義函式的必要和僅支援。如需詳細資訊，請參閱原生程式中的不可部分完成區塊。

SCHEMABINDING

原生編譯純量使用者定義函式需要自 SCHEMABINDING 變數。

EXECUTE AS

EXECUTE AS 原生編譯純量使用者定義函式是必要的。

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

指定函式具有下列一或多個選項。

ENCRYPTION

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

表示資料庫引擎會將語句的原始CREATE FUNCTION文字轉換成模糊格式。混淆的輸出不會直接顯示在任何目錄檢視中。無法存取系統數據表或資料庫檔案的用戶無法擷取模糊的文字。不過，您可以透過資料庫管理員的診斷連線存取系統數據表，或直接存取資料庫檔案的特殊許可權使用者使用文字。另外，可將偵錯工具附加至伺服器處理序的使用者，可以在執行階段從記憶體擷取原始程序。如需如何存取系統中繼資料的詳細資訊，請參閱中繼資料可見性組態。

使用此選項可防止在 SQL Server 複寫中發行這個函數。無法為 CLR 函式指定此選項。

SCHEMABINDING

指定函數必須繫結到它所參考的資料庫物件。指定時 SCHEMABINDING ，基底對象無法以會影響函式定義的方式來修改。您必須先修改或卸除函數定義才能移除對於要修改之物件的相依性。

只有在發生以下其中一個動作時，才會移除函式與其所參考物件之間的繫結：

已卸除這個函數。
您可以使用未指定 SCHEMABINDING 選項的 ALTER 陳述式來修改函式。

只有當下列條件成立時，函數才可繫結結構描述：

該函式是 Transact-SQL 函式。
函數參考的使用者定義函數和檢視表也繫結結構描述。
函數參考的物件是利用兩部分名稱來參考。
函數及其參考的物件屬於相同的資料庫。
執行 CREATE FUNCTION 陳述式的使用者在函式所參考資料庫物件上具備 REFERENCES 權限。

RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT

指定 OnNULLCall 純量函式的屬性。如果未指定， CALLED ON NULL INPUT 則預設為隱含。換句話說，即使 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 資料表值函式指定屬性。

EXECUTE AS

指定執行使用者定義函數時所在的安全性內容。因此，您可以控制 SQL Server 要使用哪個使用者帳戶來驗證由函數所參考的任何資料庫物件其權限。

EXECUTE AS 無法為內嵌資料表值函式指定。

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

INLINE = { ON | OFF }

適用於：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 函式而言，允許所有資料類型 (包括 CLR 使用者定義型別)，但 timestamp 除外。針對 CLR 函式，除了 text、ntext、image、char、varchar、varchar（max）和時間戳之外，允許所有數據類型，包括 CLR 使用者定義型別。在 Transact-SQL 或 CLR 函式中，無法將非calar 類型數據指標指定為數據行數據類型。

DEFAULT constant_expression

指定在插入期間未明確提供值時，提供給資料行的值。 constant_expression是常數、NULL或系統函式值。 DEFAULT 定義可以套用至屬性 IDENTITY 以外的任何數據行。 DEFAULT 無法指定 CLR 資料表值函式。

COLLATE 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 語句上的函 INSERT 式。您可以指定預設值;不過， NEWID 無法指定為預設值。

IDENTITY

指出新資料行是識別欄位。當新資料列加入資料表時，SQL Server 會提供資料行的唯一累加值。識別數據行通常與條件約束搭配 PRIMARY KEY 使用，做為數據表的唯一數據列標識符。屬性IDENTITY可以指派給 tinyint、smallint、int、bigint、decimal（p，0）或 numeric（p，0） 數據行。每份資料表都只能建立一個識別欄位。系結的預設值和 DEFAULT 條件約束無法與識別數據行搭配使用。您必須同時指定 seed 和 increment，或兩者都不指定。如果同時不指定這兩者，預設值便是 (1,1)。

IDENTITY 無法指定 CLR 資料表值函式。

seed

要指派給數據表中第一列的整數值。

increment

要加入數據表 中後續數據列之種子 值的整數值。

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

定義指定之資料行或資料表的條件約束。針對 CLR 函式，唯一允許的條件約束類型是 NULL。不允許具名條件約束。

NULL | NOT NULL

判斷資料行中是否允許 Null 值。 NULL 不是嚴格限制式，但可以像一樣 NOT NULL指定。 NOT NULL 無法指定 CLR 資料表值函式。

PRIMARY KEY

條件約束，可透過唯一索引強制執行指定數據行的實體完整性。在數據表值的使用者定義函式中， PRIMARY KEY 每個數據表只能建立一個數據行的條件約束。 PRIMARY KEY 無法指定 CLR 資料表值函式。

UNIQUE

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

CLUSTERED | NONCLUSTERED

表示已針對 PRIMARY KEY 或 UNIQUE 條件約束建立叢集或非叢集索引。 PRIMARY KEY 條件約束使用 CLUSTERED、和 UNIQUE 條件約束使用 NONCLUSTERED。

CLUSTERED 只能針對一個條件約束指定。如果 CLUSTERED 為 UNIQUE 條件約束指定，而且 PRIMARY KEY 也指定條件約束，則會 PRIMARY KEY 使用 NONCLUSTERED。

CLUSTERED 和 NONCLUSTERED 無法針對 CLR 資料表值函式指定。

CHECK

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

logical_expression

傳回 TRUE 或 FALSE的邏輯表達式。

<computed_column_definition> ：：=

指定計算資料行。如需有關計算資料行的詳細資訊，請參閱 CREATE TABLE (Transact-SQL)。

column_name

計算數據行的名稱。

computed_column_expression

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

<index_option> ::=

指定或 UNIQUE 索引的PRIMARY KEY索引選項。如需索引選項的詳細資訊，請參閱 CREATE INDEX (Transact-SQL)。

PAD_INDEX = { ON | OFF }

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

FILLFACTOR = fillfactor

指定百分比，以表示 Database Engine 在索引建立或變更期間應該將每個索引頁面的分葉層級填滿的程度。 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 預存程序。

如需有關內嵌數據表值函式（inline TVF）和多重語句數據表值函式（MSTVF）的詳細資訊和效能考慮，請參閱建立使用者定義函式（資料庫引擎）。

資料類型

如果在 CLR 函式中指定參數，這些參數應該是 SQL Server 類型，如同先前針對 scalar_parameter_data_type 所下的定義。如需比較 SQL Server 系統數據類型與 CLR 整合數據類型或 .NET Framework Common Language Runtime 數據類型的詳細資訊，請參閱對應 CLR 參數數據。

若要讓 SQL Server 在類別中多載時參考正確的方法，在 <method_specifier> 中指出的方法必須具有下列特性：

接收與中指定的 [ , ...n ]參數數目相同。
依值 (而不是依參考) 接收所有參數。
使用與 SQL Server 函式中指定的類型相容的參數類型。

如果 CLR 函式的 return 數據類型指定資料表類型（RETURNS TABLE），中 <method_specifier> 方法的傳回數據類型應為 IEnumerator 或 IEnumerable類型，並假設介面是由函式的建立者實作。不同於 Transact-SQL 函式，CLR 函式不能在 PRIMARY KEY中包含 <table_type_definition>、 UNIQUE或 CHECK 條件約束。中指定的 <table_type_definition> 數據行數據類型必須符合方法 <method_specifier> 在運行時間所傳回之結果集之對應數據行的類型。此類型檢查不會在建立函式時執行。

如需有關如何設計 CLR 函式的詳細資訊，請參閱 CLR 使用者定義函式。

備註

您可以在使用純量表示式的位置叫用純量函式，其中包括計算數據行和 CHECK 條件約束定義。您也可以使用 EXECUTE （Transact-SQL）語句來執行純量函式。叫用純量函式必須至少使用函式的兩部分名稱 (<schema>.<function>)。如需多部分名稱的詳細資訊，請參閱 Transact-SQL 語法慣例 (Transact-SQL)。資料表值函式可在 SELECT、INSERT、UPDATE 或 DELETE 陳述式的 FROM 子句中允許資料表運算式時叫用。如需詳細資訊，請參閱執行使用者定義函式。

互通性

以下是函數中的有效陳述式：

指派陳述式。
除 TRY...CATCH 陳述式之外的流程控制陳述式。
定義區域資料變數和區域資料指標的 DECLARE 陳述式。
包含選取清單的 SELECT 陳述式，其中含有將值指派給區域變數的運算式。
資料指標作業 - 參考函數中之已宣告、已開啟、已關閉及已取消配置的本機資料指標。只 FETCH 允許使用 INTO 子句將值指派給局部變數的語句; FETCH 不允許將數據傳回至客戶端的語句。
修改區域資料表變數的 INSERT、UPDATE 和 DELETE 陳述式。
呼叫擴充預存程序的 EXECUTE 陳述式。

如需詳細資訊，請參閱建立使用者定義函式（資料庫引擎）。

計算數據行互操作性

函數具有下列屬性。這些屬性的值會決定是否可以在可保存或索引的計算資料行中使用函數。

屬性	描述	附註
`IsDeterministic`	函數可分為具決定性或不具決定性。	具決定性函數中允許本機資料存取。例如，每當使用一組特定的輸入值呼叫時，一律會傳回相同結果的函式，且具有相同的資料庫狀態會標示為具決定性。
`IsPrecise`	函數可分為精確或不精確。	不精確函數內含浮點作業之類的作業。
`IsSystemVerified`	SQL Server 可以驗證函數的有效位數和決定性屬性。
`SystemDataAccess`	函數會存取 SQL Server 本機執行個體中的系統資料 (系統目錄或虛擬系統資料表)。
`UserDataAccess`	函數會存取 SQL Server 本機執行個體中的使用者資料。	包含使用者定義資料表和暫存資料表，但不包含資料表變數。

SQL Server 會自動判斷 Transact-SQL 函數的有效位數和決定性屬性。使用者可以指定 CLR 函數的資料存取和決定性屬性。如需詳細資訊，請參閱 CLR整合：CLR 例程的自訂屬性。

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

重要

建立函式時，必須使用具決定性的 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 使用者定義函數之 Managed 程式碼的任何參考都算是一個層級。若是從受控碼內叫用的方法，則不計入這項限制。

在 CLR 資料表值函式中使用排序順序

當您在 CLR 資料表值函式中使用 ORDER 子句時，請遵循以下指導方針：

您必須確保結果一定會以指定的順序來排序。如果結果不是以指定的順序，SQL Server 會在執行查詢時產生錯誤訊息。
如果指定了 ORDER 子句，資料表值函式的輸出必須根據資料行的定序 (明確或隱含) 來排序。例如，如果數據行定序是中文，則傳回的結果必須根據中文排序規則進行排序。（定序是在數據表值函式的 DDL 中指定，或是從資料庫定序取得。
如果指定，SQL Server 一律會驗證 ORDER 子句，同時傳回結果，查詢處理器是否使用它來執行進一步的優化。只有在您知道查詢處理器很有用時，才使用 ORDER 子句。
SQL Server 查詢處理器會在下列情況下自動利用 ORDER 子句：
- ORDER 子句與索引相容的插入查詢。
- 與 ORDER 子句相容的 ORDER BY 子句。
- GROUP BY 與 ORDER 子句相容的彙總。
- 相異資料行與 ORDER 子句相容的 DISTINCT 彙總。

除非ORDER在查詢中也指定，ORDER BY否則子句不保證執行查詢時的SELECT已排序結果。如需有關如何查詢資料表值函式之排序次序中所含資料行的資訊，請參閱 sys.function_order_columns (Transact-SQL)。

中繼資料

下表列出您可以用來傳回使用者定義函數之中繼資料的系統目錄檢視表。

系統檢視	描述
sys.sql_modules	請參閱範例一節中的範例 E。
sys.assembly_modules	顯示 CLR 使用者定義函數的相關資訊。
sys.parameters	顯示使用者定義函數中定義之參數的相關資訊。
sys.sql_expression_dependencies	顯示函數所參考的基礎物件。

權限

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

範例

如需 UDF 的更多範例和效能考慮，請參閱建立使用者定義函式（資料庫引擎）。

A. 使用計算 ISO 周的純量值使用者定義函數

下列範例會建立使用者定義函數 ISOweek。這個函數採用日期引數並計算 ISO 週數。若要使函數能夠正確計算，必須先叫用 SET DATEFIRST 1，才能呼叫該函數。

此範例也會示範如何使用 EXECUTE AS 子句（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. 建立多重語句數據表值函式

下列範例會在資料庫中建立數據表值函式fn_FindReports(InEmpID)AdventureWorks2022。當提供有效的員工識別碼時，此函數會傳回對應於所有員工的資料表，該資料表會直接或間接報告給員工。此函數會利用遞迴通用資料表運算式 (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 資料表值函式。

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 FUNCTION (Transact-SQL)

Syntax

引數

OR ALTER

schema_name

function_name

@parameter_name

[ type_schema_name. ] parameter_data_type

[ = 預設值 ]

READONLY

return_data_type

function_body

scalar_expression

TABLE

select_stmt

ORDER (<order_clause>)

EXTERNAL NAME <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

BEGIN ATOMIC WITH

SCHEMABINDING

EXECUTE AS

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

ENCRYPTION

SCHEMABINDING

RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT

EXECUTE AS

INLINE = { ON | OFF }

<column_definition> ：：=

column_name

data_type

DEFAULT constant_expression

COLLATE collation_name

ROWGUIDCOL

IDENTITY

seed

increment

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

NULL | NOT NULL

PRIMARY KEY

UNIQUE

CLUSTERED | NONCLUSTERED

CHECK

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. 顯示使用者定義函數的定義

相關內容

意見反應

其他資源