针对内存中 OLTP 的标量用户定义函数Scalar User-Defined Functions for In-Memory OLTP

适用于: 是SQL Server是Azure SQL 数据库否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 构造,请参阅 本机编译的 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.

可以在能够使用解释标量用户定义函数的位置以互操作模式使用这些标量用户定义函数。You can use these scalar user-defined functions in interop mode, wherever an interpreted scalar user-defined function can be used. 这一使用要遵守交叉容器事务限制,如 内存优化表中的事物 中的 交叉容器事务的支持隔离级别部分所述。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. 有关互操作模式的详细信息,请参阅 使用解释型 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). 有关在本机编译存储过程中执行用户定义函数时所支持的语法,请参阅 本机编译的 T-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