記憶體內部 OLTP 的純量使用者定義函數Scalar User-Defined Functions for In-Memory OLTP

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

SQL Server 2016 (13.x)SQL Server 2016 (13.x)中,您可建立和卸除原生編譯的純量使用者定義函數。In SQL Server 2016 (13.x)SQL Server 2016 (13.x), you can create and drop natively compiled, scalar user-defined functions. 您亦可改變這些使用者定義函數。You can also alter these user-defined functions. 原生編譯可改善 Transact-SQL 中使用者定義函數評估的效能。Native compilation improves performance of the evaluation of user-defined functions in Transact-SQL.

若改變原生編譯的純量使用者定義函數,則在執行作業以及編譯新版本函數時仍可繼續使用應用程式。When you alter a natively compiled, scalar user-defined function, the application remains available while the operation is being run and the new version of the function is being compiled.

如需支援的 T-SQL 建構,請參閱 原生編譯的預存程序中支援的建構For supported T-SQL constructs, see Supported Features for Natively Compiled T-SQL Modules.

建立、卸除和改變使用者定義函數Creating, Dropping, and Altering User-Defined Functions

您可使用 CREATE FUNCTION 建立原生編譯的純量使用者定義函數,使用 DROP FUNCTION 移除使用者定義函數,以及使用 ALTER FUNCTION 變更函數。You use the CREATE FUNCTION to create the natively compiled, scalar user-defined function, the DROP FUNCTION to remove the user-defined function, and the ALTER FUNCTION to change the function. 使用者定義函數需要使用 BEGIN ATOMIC WITH。BEGIN ATOMIC WITH is required for the user-defined functions.

如需關於支援語法與所有限制的資訊,請參閱下列主題。For information about the supported syntax and any restrictions, see the following topics.

sp_recompile (Transact-SQL) 預存程序可搭配原生編譯純量使用者定義函數併用。The sp_recompile (Transact-SQL)stored procedure can be used with the natively compiled, scalar user-defined function. 其會導致使用中繼資料的現有定義來重新編譯函數。It will result in the function being recompiled using the definition that exists in metadata.

下列範例展示來自 AdventureWorks2016CTP3 範例資料庫的純量 UDF。The following sample shows a scalar UDF from the AdventureWorks2016CTP3 sample database.

CREATE FUNCTION [dbo].[ufnLeadingZeros_native](@Value int)   
RETURNS varchar(8)   
WITH NATIVE_COMPILATION, SCHEMABINDING  
AS   
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'English')  
  
    DECLARE @ReturnValue varchar(8);  
    SET @ReturnValue = CONVERT(varchar(8), @Value);  
       DECLARE @i int = 0, @count int = 8 - LEN(@ReturnValue)  
  
    WHILE @i < @count  
       BEGIN  
            SET @ReturnValue = '0' + @ReturnValue;  
            SET @i += 1  
       END  
  
    RETURN (@ReturnValue);  
  
END  

呼叫使用者自訂函數Calling User-Defined Functions

在運算式中,原生編譯純量使用者定義函數會使用於與內建純量函數和解譯純量使用者定義函數相同的位置。Natively compiled, scalar user-defined functions can be used in expressions, in the same place as built-in scalar functions and interpreted scalar user-defined functions. 原生編譯純量使用者定義函數,在 Transact-SQL 陳述式與原生編譯預存程序中亦可搭配 EXECUTE 陳述式併用。Natively compiled, scalar user-defined functions can also be used with the EXECUTE statement, in a Transact-SQL statement and in a natively compiled stored procedure.

您可在原生編譯預存程序以及原生編譯使用者定義函數,以及允許內建函數的情況下,使用這些純量使用者定義函數。You can use these scalar user-defined functions in natively compiled store procedures and natively compiled user-defined functions, and wherever built-in functions are permitted. 您亦可在傳統的 Transact-SQL 模組中使用原生編譯純量使用者定義函數。You can also use natively compiled, scalar user-defined functions in traditional Transact-SQL modules.

只要可以使用解譯的純量使用者定義函數,您就可在 interop 模式中使用這些純量使用者定義函數。You can use these scalar user-defined functions in interop mode, wherever an interpreted scalar user-defined function can be used. 此使用方式會受到跨容器交易限制所影響,如 Transactions with Memory-Optimized Tables (使用記憶體最佳化資料表的交易) 的 Supported Isolation Levels for Cross-Container Transactions(跨容器交易支援的隔離等級) 一節中所述。This use is subject to cross-container transaction limitations, as described in Supported Isolation Levels for Cross-Container Transactions section in Transactions with Memory-Optimized Tables. 如需 interop 模式的詳細資訊,請參閱 使用解譯的 Transact-SQL 存取記憶體最佳化的資料表For more information about interop mode, see Accessing Memory-Optimized Tables Using Interpreted Transact-SQL.

原生編譯純量使用者定義函數需要明確的執行內容。Natively compiled, scalar user-defined functions do require an explicit execution context. 如需詳細資訊,請參閱 EXECUTE AS 子句 (Transact-SQL)For more information, see EXECUTE AS Clause (Transact-SQL). 不支援 EXECUTE AS CALLER。EXECUTE AS CALLER is not supported. 如需詳細資訊,請參閱 EXECUTE (Transact-SQL)For more information, see EXECUTE (Transact-SQL).

如需適用於 Transact-SQL Execute 陳述式以及原生編譯純量使用者定義函數的支援語法資訊,請參閱 EXECUTE (Transact-SQL)For the supported syntax for Transact-SQL Execute statements, for natively compiled, scalar user-defined functions, see EXECUTE (Transact-SQL). 如需在原生編譯預存程序中執行使用者定義函數的支援語法資訊,請參閱 原生編譯的預存程序中支援的建構For the supported syntax for executing the user-defined functions in a natively compiled stored procedure, see Supported Features for Natively Compiled T-SQL Modules.

提示和參數Hints and Parameters

適用於原生編譯純量使用者定義函數內部資料表、聯結和查詢提示的支援,等同於這些原生編譯預存程序提示的適用支援。Support for table, join, and query hints inside natively compiled, scalar user-defined functions is equal to support for these hints for natively compiled stored procedures. 如同解譯純量使用者定義函數,查詢提示會隨附於參考原生編譯純量使用者定義函數的 Transact-SQL 查詢,其不會影響此使用者定義函數的查詢計劃。As with interpreted scalar user-defined functions, the query hints included with a Transact-SQL query that reference a natively compiled, scalar user-defined function do not impact the query plan for this user-defined function.

若受純量使用者定義函數允許,則適用於原生編譯純量使用者定義函數的支援參數,皆為適用於原生編譯預存程序的支援參數。The parameters supported for the natively compiled, scalar user-defined functions are all the parameters supported for natively compiled stored procedures, as long as the parameters are allowed for scalar user-defined functions. 資料表值參數即是受支援參數的範例。An example of a supported parameter is the table-valued parameter.

結構描述繫結Schema-Bound

下列項目會套用至原生編譯純量使用者定義函數。The following apply to natively compiled, scalar user-defined functions.

  • 必須使用 CREATE FUNCTION 和 ALTER FUNCTION 中的 WITH SCHEMABINDING 引數,執行結構描述繫結。Must be schema-bound, by using the WITH SCHEMABINDING argument in the CREATE FUNCTION and ALTER FUNCTION.

  • 由結構描述繫結預存程序或使用者定義函數參考時,無法卸除或變更。Cannot be dropped or altered when referenced by a schema-bound stored procedure or user-defined function.

SHOWPLAN_XMLSHOWPLAN_XML

原生編譯純量使用者定義函數支援 SHOWPLAN_XML。Natively compiled, scalar user-defined functions support SHOWPLAN_XML. 由於搭配原生編譯預存程序,因此其符合一般 SHOWPLAN_XML 結構描述。It conforms to the general SHOWPLAN_XML schema, as with natively compiled stored procedures. 使用者定義函數的基底項目為 <UDF>The base element for the user-defined functions is <UDF>.

STATISTICS XML 不支援原生編譯純量使用者定義函數。STATISTICS XML is not supported for natively compiled, scalar user-defined functions. 若您執行參考使用者定義函數且啟用 STATISTICS XML 的查詢,則會傳回無使用者定義函數部分的 XML 內容。When you run a query referencing the user-defined function, with STATISTICS XML enabled, the XML content is returned without the part for the user-defined function.

權限Permissions

如同原生編譯預存程序,建立函數時會檢查參考自原生編譯純量使用者定義函數的物件權限。As with natively compiled stored procedures, the permissions for objects referenced from a natively compiled, scalar user-defined function are checked when the function is created. 若模擬使用者不具正確權限,則 CREATE FUNCTION 將會失敗。The CREATE FUNCTION fails if the impersonated user does not have the correct permissions. 若權限變更導致模擬使用者不再具有正確權限,則後續的使用者定義函數執行作業亦會失敗。If permission changes result in the impersonated user no longer having the correct permissions, subsequent executions of the user-defined function fail.

若您在原生編譯預存程序中使用原生編譯純量使用者定義函數,則在建立外部程序時會檢查執行使用者定義函數的權限。When you use a natively compiled, scalar user-defined function inside a natively compiled stored procedure, the permissions for executing the user-defined function are checked when the outer procedure is created. 由外部程序模擬的使用者若不具備使用者定義函數的 EXEC 權限,則會導致預存程序建立作業失敗。If the user impersonated by the outer procedure does not have EXEC permissions for the user-defined function, the creation of the stored procedure fails. 若權限變更導致使用者不再具有 EXEC 權限,則會導致外部程序執行作業失敗。If permission changes result in the user no longer having the EXEC permissions, the execution of the outer procedure fails.

另請參閱See Also

內建函數 (Transact-SQL) Built-in Functions (Transact-SQL)
以 XML 格式儲存執行計畫Save an Execution Plan in XML Format