原生編譯的 T-SQL 模組支援的功能Supported Features for Natively Compiled T-SQL Modules

適用於: 是SQL Server 是Azure SQL Database 否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.

如需原生模組定義支援的功能,請參閱 原生編譯的預存程序上支援的建構For 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*

  • 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 (每個陳述式一個資料列) 和 INSERTSELECTINSERT VALUES (one row per statement) and INSERT ... SELECT

  • UPDATEUPDATE

  • DeleteDELETE

  • 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 Specification>。For 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) 的查詢中,您可以排序 8000 多個資料列。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) 最多只能排序 8000 個資料列 (如果有聯結則資料列更少)。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 個資料列,則會收到錯誤訊息:Msg 41398,層級 16,狀態 1、程序<程序名稱> 、行 <行號> 。TOP 運算子最多可以傳回 8192 個資料列;要求 <數字> 個。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