本机编译的 T-SQL 模块支持的功能Supported Features for Natively Compiled T-SQL Modules

适用于: 是SQL Server是Azure SQL 数据库否Azure SQL 数据仓库否并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

本主题列出了 T-SQL 的外围应用以及本机编译 T-SQL 模块主体支持的功能,如存储过程 (CREATE PROCEDURE (Transact-SQL))、标量用户定义函数、内联表值函数和触发器。This topic contains a list of T-SQL surface area and supported features in the body of natively compiled T-SQL modules, such as stored procedures (CREATE PROCEDURE (Transact-SQL)), scalar user-defined functions, inline table-valued functions, and triggers.

有关本机模块定义的支持功能,请参阅 对于本机编译的 T-SQL 模块支持的 DDLFor supported features around the definition of native modules, see Supported DDL for Natively Compiled T-SQL modules.

如需了解不支持构造的完整信息以及如何解决本机编译模块中不支持某些功能的问题,请参阅 Migration Issues for Natively Compiled Stored ProceduresFor complete information about unsupported constructs, and for information about how to work around some of the unsupported features in natively compiled modules, see Migration Issues for Natively Compiled Stored Procedures. 有关不支持的功能的详细信息,请参阅 内存中 OLTP 不支持的 Transact-SQL 构造For more information about unsupported features, see Transact-SQL Constructs Not Supported by In-Memory OLTP.

本机模块中的查询外围应用Query Surface Area in Native Modules

支持以下查询构造:The following query constructs are supported:

CASE 表达式:CASE 可用于允许使用有效表达式的任意语句或子句。CASE expression: CASE can be used in any statement or clause that allows a valid expression.

  • 适用于: SQL Server 2017 (14.x)SQL Server 2017 (14.x)Applies to: SQL Server 2017 (14.x)SQL Server 2017 (14.x).
    SQL Server 2017 (14.x)SQL Server 2017 (14.x) 开始,本机编译 T-SQL 模块已支持 CASE 语句。Beginning with SQL Server 2017 (14.x)SQL Server 2017 (14.x), CASE statements are now supported for natively compiled T-SQL modules.

SELECT 子句:SELECT clause:

  • 列名和别名(使用 AS 或 = 语法)。Columns and name aliases (using either AS or = syntax).

  • 标量子查询Scalar subqueries

    • 适用于: SQL Server 2016 (13.x)SQL Server 2016 (13.x)Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x). SQL Server 2016 (13.x)SQL Server 2016 (13.x) 开始,本机编译模块已支持标量子查询。Beginning with SQL Server 2016 (13.x)SQL Server 2016 (13.x), scalar subqueries are now supported in natively compiled modules.
  • TOP*TOP*

  • SELECT DISTINCTSELECT DISTINCT

    • 适用于: SQL Server 2016 (13.x)SQL Server 2016 (13.x)Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x). SQL Server 2016 (13.x)SQL Server 2016 (13.x) 开始,本机编译模块已支持 DISTINCT 运算符。Beginning with SQL Server 2016 (13.x)SQL Server 2016 (13.x), the DISTINCT operator is supported in natively compiled modules.

          DISTINCT aggregates are not supported.  
      
  • UNION 和 UNION ALLUNION and UNION ALL

    • 适用于: SQL Server 2016 (13.x)SQL Server 2016 (13.x)Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x). SQL Server 2016 (13.x)SQL Server 2016 (13.x) 开始,本机编译模块已支持 UNION 和 UNION ALL 运算符。Beginning with SQL Server 2016 (13.x)SQL Server 2016 (13.x), UNION and UNION ALL operators are now supported in natively compiled modules.
  • 表分配Variable assignments

FROM 子句:FROM clause:

  • FROM <内存优化表或表变量>FROM <memory optimized table or table variable>

  • FROM <本机编译的内联 TVF>FROM <natively compiled inline TVF>

  • LEFT OUTER JOIN、RIGHT OUTER JOIN、CROSS JOIN 和 INNER JOIN。LEFT OUTER JOIN, RIGHT OUTER JOIN, CROSS JOIN and INNER JOIN.

    • 适用于: SQL Server 2016 (13.x)SQL Server 2016 (13.x)Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x). SQL Server 2016 (13.x)SQL Server 2016 (13.x) 开始,本机编译模块已支持 JOINS。Beginning with SQL Server 2016 (13.x)SQL Server 2016 (13.x), JOINS are now supported in natively compiled modules.
  • 子查询 [AS] table_aliasSubqueries [AS] table_alias. 有关详细信息,请参阅 FROM (Transact-SQL)For more information, see FROM (Transact-SQL).

    • 适用于: SQL Server 2016 (13.x)SQL Server 2016 (13.x)Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x). SQL Server 2016 (13.x)SQL Server 2016 (13.x) 开始,本机编译模块已支持子查询。Beginning with SQL Server 2016 (13.x)SQL Server 2016 (13.x), Subqueries are now supported in natively compiled modules.

WHERE 子句:WHERE clause:

  • 筛选器谓词 IS [NOT] NULLFilter predicate IS [NOT] NULL

  • AND、BETWEENAND, BETWEEN

  • OR、NOT、IN、EXISTSOR, NOT, IN, EXISTS

    • 适用于: SQL Server 2016 (13.x)SQL Server 2016 (13.x)Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x). SQL Server 2016 (13.x)SQL Server 2016 (13.x) 开始,本机编译模块已支持 OR/NOT/IN/EXISTS 运算符。Beginning with SQL Server 2016 (13.x)SQL Server 2016 (13.x), OR/NOT/IN/EXISTS operators are now supported in natively compiled modules.

GROUP BY 子句:GROUP BY clause:

  • AVG、COUNT、COUNT_BIG、MIN、MAX 和 SUM 聚合函数。Aggregate functions AVG, COUNT, COUNT_BIG, MIN, MAX, and SUM.

  • 类型 nvarchar、char、varchar、varchar、varbinary 和 Binary 不支持 MIN 和 MAX。MIN and MAX are not supported for types nvarchar, char, varchar, varchar, varbinary, and binary.

ORDER BY 子句:ORDER BY clause:

  • ORDER BY 子句中不支持 DISTINCTThere is no support for DISTINCT in the ORDER BY clause.

  • 如果 ORDER BY 列表中的表达式逐字出现在 GROUP BY 列表中,将受 GROUP BY (Transact-SQL) 支持。Is supported with GROUP BY (Transact-SQL) if an expression in the ORDER BY list appears verbatim in the GROUP BY list.

    • 例如,支持 GROUP BY a + b ORDER BY a + b,但不支持 GROUP BY a、b ORDER BY a + b。For example, GROUP BY a + b ORDER BY a + b is supported, but GROUP BY a, b ORDER BY a + b is not.

HAVING 子句:HAVING clause:

  • 具有与 WHERE 子句相同的表达式限制。Is subject to the same expression limitations as the WHERE clause.

本机编译的模块中支持 ORDER BY 和 TOP,但具有某些限制ORDER BY and TOP are supported in natively compiled modules, with some restrictions

  • WITH TIES 子句中不支持 PERCENTTOPThere is no support for WITH TIES or PERCENT in the TOP clause.

  • ORDER BY 子句中不支持 DISTINCTThere is no support for DISTINCT in the ORDER BY clause.

  • TOP 子句中使用常量时,结合使用 ORDER BYTOP 不支持超过 8,192。TOP combined with ORDER BY does not support more than 8,192 when using a constant in the TOP clause.

    • 当查询包含联接或聚合函数时,该限制数可能降低。This limit may be lowered in case the query contains joins or aggregate functions. (例如,对于一个联接(两个表),限制为 4,096 行。(For example, with one join (two tables), the limit is 4,096 rows. 对于两个联接(三个表),限制为 2,730 行。)With two joins (three tables), the limit is 2,730 rows.)
    • 可通过将行数存储在变量中来获得多于 8,192 的结果:You can obtain results greater than 8,192 by storing the number of rows in a variable:
DECLARE @v INT = 9000;
SELECT TOP (@v) ... FROM ... ORDER BY ...

不过,与使用变量相比,在 TOP 子句中使用常量将提供更好的性能。However, a constant in the TOP clause results in better performance compared to using a variable.

这些本机编译的 Transact-SQLTransact-SQL 上的限制不适用于内存优化表上解释的 Transact-SQLTransact-SQL 访问。These restrictions on natively compiled Transact-SQLTransact-SQL do not apply to interpreted Transact-SQLTransact-SQL access on memory-optimized tables.

数据修改Data Modification

支持以下 DML 语句。The following DML statements are supported.

  • INSERT VALUES(每条语句一行)和 INSERT ...SELECTINSERT VALUES (one row per statement) and INSERT ... SELECT

  • UPDATEUPDATE

  • 删除DELETE

  • UPDATE 和 DELETE 语句支持 WHERE。WHERE is supported with UPDATE and DELETE statements.

控制流语言Control-of-flow language

支持以下控制流语言构造。The following control-of-flow language constructs are supported.

支持的运算符Supported Operators

支持下列运算符。The following operators are supported.

  • 比较运算符 (Transact SQL)(例如,>、<、>= 和 <=)Comparison Operators (Transact-SQL) (for example, >, <, >=, and <=)

  • 一元运算符(+、-)。Unary operators (+, -).

  • 二元运算符(*、/、+、-、%(取模))。Binary operators (*, /, +, -, % (modulo)).

           The plus operator (+) is supported on both numbers and strings.  
    
  • 逻辑运算符(AND、OR、NOT)。Logical operators (AND, OR, NOT).

  • 按位运算符 ~、&、| 和 ^Bitwise operators ~, &, |, and ^

  • APPLY 运算符APPLY operator

    • 适用于: SQL Server 2017 (14.x)SQL Server 2017 (14.x)Applies to: SQL Server 2017 (14.x)SQL Server 2017 (14.x).
      SQL Server 2017 (14.x)SQL Server 2017 (14.x) 开始,本机编译模块支持 APPLY 运算符。Beginning with SQL Server 2017 (14.x)SQL Server 2017 (14.x), the APPLY operator is supported in natively compiled modules.

本机编译模块中的内置函数Built-in Functions in Natively Compiled Modules

内存优化表的约束中以及本机编译的 T-SQL 模块中支持以下函数。The following functions are supported in constraints on memory-optimized tables and in natively compiled T-SQL modules.

  • 所有数学函数 (Transact-SQL)All Mathematical Functions (Transact-SQL)

  • 日期函数:CURRENT_TIMESTAMP、DATEADD、DATEDIFF、DATEFROMPARTS、DATEPART、DATETIME2FROMPARTS、DATETIMEFROMPARTS、DAY、EOMONTH、GETDATE、GETUTCDATE、MONTH、SMALLDATETIMEFROMPARTS、SYSDATETIME、SYSUTCDATETIME 和 YEAR。Date functions: CURRENT_TIMESTAMP, DATEADD, DATEDIFF, DATEFROMPARTS, DATEPART, DATETIME2FROMPARTS, DATETIMEFROMPARTS, DAY, EOMONTH, GETDATE, GETUTCDATE, MONTH, SMALLDATETIMEFROMPARTS, SYSDATETIME, SYSUTCDATETIME, and YEAR.

  • 字符串函数:LEN、LTRIM、RTRIM 和 SUBSTRING。String functions: LEN, LTRIM, RTRIM, and SUBSTRING.

    • 适用于: SQL Server 2017 (14.x)SQL Server 2017 (14.x)Applies to: SQL Server 2017 (14.x)SQL Server 2017 (14.x).
      SQL Server 2017 (14.x)SQL Server 2017 (14.x) 开始,还支持下列内置函数:TRIM、TRANSLATE 和 CONCAT_WS。Beginning with SQL Server 2017 (14.x)SQL Server 2017 (14.x), the following built-in functions are also supported: TRIM, TRANSLATE, and CONCAT_WS.
  • 标识函数:SCOPE_IDENTITYIdentity functions: SCOPE_IDENTITY

  • NULL 函数:ISNULLNULL functions: ISNULL

  • Uniqueidentifier 函数:NEWID 和 NEWSEQUENTIALIDUniqueidentifier functions: NEWID and NEWSEQUENTIALID

  • JSON 函数JSON functions

    • 适用于: SQL Server 2017 (14.x)SQL Server 2017 (14.x)Applies to: SQL Server 2017 (14.x)SQL Server 2017 (14.x).
      SQL Server 2017 (14.x)SQL Server 2017 (14.x) 开始,本机编译模块支持 JSON 函数。Beginning with SQL Server 2017 (14.x)SQL Server 2017 (14.x), the JSON functions are supported in natively compiled modules.
  • 错误函数:ERROR_LINE、ERROR_MESSAGE、ERROR_NUMBER、ERROR_PROCEDURE、ERROR_SEVERITY 和 ERROR_STATEError functions: ERROR_LINE, ERROR_MESSAGE, ERROR_NUMBER, ERROR_PROCEDURE, ERROR_SEVERITY, and ERROR_STATE

  • 系统函数:@@rowcount。System Functions: @@rowcount. 本机编译存储过程中的语句会更新 @@rowcount,因此,可使用本机编译存储过程中的 @@rowcount 来确定受在该本机编译存储过程中执行的上条语句影响的行数。Statements inside natively compiled stored procedures update @@rowcount and you can use @@rowcount in a natively compiled stored procedure to determine the number of rows affected by the last statement executed within that natively compiled stored procedure. 但是,@@rowcount 在本机编译存储过程执行开始和结束时会重置为 0。However, @@rowcount is reset to 0 at the start and at the end of the execution of a natively compiled stored procedure.

  • 安全性函数:IS_MEMBER({'group' | 'role'})、IS_ROLEMEMBER ('role' [, 'database_principal'])、IS_SRVROLEMEMBER ('role' [, 'login'])、ORIGINAL_LOGIN()、SESSION_USER、CURRENT_USER、SUSER_ID(['login'])、SUSER_SID(['login'] [, Param2])、SUSER_SNAME([server_user_sid])、SYSTEM_USER、SUSER_NAME、USER、USER_ID(['user'])、USER_NAME([id])、CONTEXT_INFO()。Security functions: IS_MEMBER({'group' | 'role'}), IS_ROLEMEMBER ('role' [, 'database_principal']), IS_SRVROLEMEMBER ('role' [, 'login']), ORIGINAL_LOGIN(), SESSION_USER, CURRENT_USER, SUSER_ID(['login']), SUSER_SID(['login'] [, Param2]), SUSER_SNAME([server_user_sid]), SYSTEM_USER, SUSER_NAME, USER, USER_ID(['user']), USER_NAME([id]), CONTEXT_INFO().

  • 可以嵌套本机模块的执行。Executions of native modules can be nested.

审核Auditing

在本机编译存储过程中支持过程级审核。Procedure level auditing is supported in natively compiled stored procedures.

有关审核的详细信息,请参阅 Create a Server Audit and Database Audit SpecificationFor more information about auditing, see Create a Server Audit and Database Audit Specification.

表提示和查询提示Table and Query Hints

支持以下各项:The following are supported:

有关详细信息,请参阅查询提示 (Transact-SQL)For more information, see Query Hints (Transact-SQL).

排序限制Limitations on Sorting

可以在使用 TOP (Transact-SQL)ORDER BY 子句 (Transact-SQL) 的查询中对 8,000 多行进行排序。You can sort greater than 8,000 rows in a query that uses TOP (Transact-SQL) and an ORDER BY Clause (Transact-SQL). 但是,如果没有 ORDER BY 子句 (Transact-SQL)TOP (Transact-SQL) 最多可对 8,000 行进行排序(如果存在联接,则更少)。However, without ORDER BY Clause (Transact-SQL), TOP (Transact-SQL) can sort up to 8,000 rows (fewer rows if there are joins).

如果查询同时使用 TOP (Transact-SQL) 运算符和 ORDER BY 子句 (Transact-SQL),则可以对 TOP 运算符指定多达 8192 行。If your query uses both the TOP (Transact-SQL) operator and an ORDER BY Clause (Transact-SQL), you can specify up to 8192 rows for the TOP operator. 如果指定超过 8192 行,则将收到错误消息:消息 41398、级别 16、状态 1、程序 <procedureName>、行 <lineNumber>,TOP 运算符最多可返回 8192 行;已请求 <number>。 If you specify more than 8192 rows you get the error message: Msg 41398, Level 16, State 1, Procedure <procedureName>, Line <lineNumber> The TOP operator can return a maximum of 8192 rows; <number> was requested.

如果您没有 TOP 子句,则可以使用 ORDER BY 对任何数目的行进行排序。If you do not have a TOP clause, you can sort any number of rows with ORDER BY.

如果您没有使用 ORDER BY 子句,则可以将任何整数值用于 TOP 运算符。If you do not use an ORDER BY clause, you can use any integer value with the TOP operator.

使用 TOP N = 8192 的示例:编译Example with TOP N = 8192: Compiles

CREATE PROCEDURE testTop  
WITH EXECUTE AS OWNER, SCHEMABINDING, NATIVE_COMPILATION  
  AS  
  BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')  
    SELECT TOP 8192 ShoppingCartId, CreatedDate, TotalPrice FROM dbo.ShoppingCart  
    ORDER BY ShoppingCartId DESC  
  END;  
GO  

使用 TOP N > 8192 的示例:无法编译。Example with TOP N > 8192: Fails to compile.

CREATE PROCEDURE testTop  
WITH EXECUTE AS OWNER, SCHEMABINDING, NATIVE_COMPILATION  
  AS  
  BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')  
    SELECT TOP 8193 ShoppingCartId, CreatedDate, TotalPrice FROM dbo.ShoppingCart  
    ORDER BY ShoppingCartId DESC  
  END;  
GO  

该 8192 行限制仅适用于 TOP N ,其中 N 是常量,如前面的示例中所示。The 8192 row limitation only applies to TOP N where N is a constant, as in the preceding examples. 如果您需要 N 大于 8192,则可以将该值分配给一个变量并且将该变量用于 TOPIf you need N greater than 8192 you can assign the value to a variable and use that variable with TOP.

使用变量的示例:编译Example using a variable: Compiles

CREATE PROCEDURE testTop  
WITH EXECUTE AS OWNER, SCHEMABINDING, NATIVE_COMPILATION  
  AS  
  BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')  
    DECLARE @v int = 8193   
    SELECT TOP (@v) ShoppingCartId, CreatedDate, TotalPrice FROM dbo.ShoppingCart  
    ORDER BY ShoppingCartId DESC  
  END;  
GO  

对返回的行数的限制: 有两种情形可减少可由 TOP 运算符返回的行数:Limitations on rows returned: There are two cases where that can potentially reduce the number of rows that can be returned by the TOP operator:

  • 在查询中使用 JOIN。Using JOINs in the query. JOIN 对该限制的影响依赖于查询计划。The influence of JOINs on the limitation depends on the query plan.

  • 在 ORDER BY 子句中使用聚合函数或对聚合函数的引用。Using aggregate functions or references to aggregate functions in the ORDER BY clause.

用于在 TOP N 中计算最差情形下支持的最大 N 的公式为: N = floor ( 65536 / number_of_tables * 8 + total_size+of+aggs )The formula to calculate a worst case maximum supported N in TOP N is: N = floor ( 65536 / number_of_tables * 8 + total_size+of+aggs ).

另请参阅See Also

本机编译存储过程 Natively Compiled Stored Procedures
本机编译存储过程的迁移问题Migration Issues for Natively Compiled Stored Procedures