Supported Features for Natively Compiled T-SQL Modules

 

Updated: June 1, 2016

Applies To: SQL Server 2016

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2014)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

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.

For 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. For more information about unsupported features, see Transact-SQL Constructs Not Supported by In-Memory OLTP.

The following query constructs are supported:

SELECT clause:

  • Columns and name aliases (using either AS or = syntax).

  • Scalar subqueries

  • TOP*

  • SELECT DISTINCT

    DISTINCT aggregates are not supported.

  • UNION and UNION ALL

  • Variable assignments

FROM clause:

  • FROM <memory optimized table or table variable>

  • FROM <natively compiled inline TVF>

  • LEFT OUTER JOIN, RIGHT OUTER JOIN, CROSS JOIN and INNER JOIN.

  • Subqueries [AS] table_alias. For more information, see FROM (Transact-SQL).

WHERE clause:

  • Filter predicate IS [NOT] NULL

  • AND, OR, NOT, IN, EXISTS, BETWEEN

GROUP BY clause:

  • Aggregate functions AVG, COUNT, COUNT_BIG, MIN, MAX, and SUM.

  • MIN and MAX are not supported for types nvarchar, char, varchar, varchar, varbinary, and binary.

ORDER BY clause:

  • There is no support for DISTINCT in the ORDER BY clause.
  • Is supported with GROUP BY (Transact-SQL) if an expression in the ORDER BY list appears verbatim in the GROUP BY list.
    • For example, GROUP BY a + b ORDER BY a + b is supported, but GROUP BY a, b ORDER BY a + b is not.

HAVING clause:

  • Is subject to the same expression limitations as the WHERE clause.

ORDER BY and TOP are supported in natively compiled modules, with some restrictions

  • There is no support for WITH TIES or PERCENT in the TOP clause.
  • There is no support for DISTINCT in the ORDER BY clause.
  • 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. (For example, with one join (two tables), the limit is 4,096 rows. With two joins (three tables), the limit is 2,730 rows.)
    • 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 …

However, a constant in the TOP clause results in better performance compared to using a variable.

These restrictions on natively compiled Transact-SQL do not apply to interpreted Transact-SQL access on memory-optimized tables.

The following DML statements are supported.

  • INSERT VALUES (one row per statement) and INSERT ... SELECT

  • UPDATE

  • DELETE

  • WHERE is supported with UPDATE and DELETE statements.

The following control-of-flow language constructs are supported.

The following operators are supported.

  • Comparison Operators (Transact-SQL) (for example, >, <, >=, and <=)

  • Unary operators (+, -).

  • Binary operators (*, /, +, -, % (modulo)).

    The plus operator (+) is supported on both numbers and strings.

  • Logical operators (AND, OR, NOT).

  • Bitwise operators ~, &, |, and ^

The following functions are supported in constraints on memory-optimized tables and in natively compiled T-SQL modules.

  • All Mathematical Functions (Transact-SQL)

  • Date functions: CURRENT_TIMESTAMP, DATEADD, DATEDIFF, DATEFROMPARTS, DATEPART, DATETIME2FROMPARTS, DATETIMEFROMPARTS, DAY, EOMONTH, GETDATE, GETUTCDATE, MONTH, SMALLDATETIMEFROMPARTS, SYSDATETIME, SYSUTCDATETIME, and YEAR.

  • String functions: LEN, LTRIM, RTRIM, and SUBSTRING

  • Identity functions: SCOPE_IDENTITY

  • NULL functions: ISNULL

  • Uniqueidentifier functions: NEWID and NEWSEQUENTIALID

  • Error functions: ERROR_LINE, ERROR_MESSAGE, ERROR_NUMBER, ERROR_PROCEDURE, ERROR_SEVERITY, and ERROR_STATE

  • System Functions: @@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. However, @@rowcount is reset to 0 at the start and at the end of the execution of a natively compiled stored procedure.

  • 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.

Procedure level auditing is supported in natively compiled stored procedures.

For more information about auditing, see Create a Server Audit and Database Audit Specification.

The following are supported:

For more information, see Query Hints (Transact-SQL).

You can sort greater than 8,000 rows in a query that uses TOP (Transact-SQL) and an ORDER BY Clause (Transact-SQL). However, without ORDER BY Clause (Transact-SQL), TOP (Transact-SQL) can sort up to 8,000 rows (fewer rows if there are joins).

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. 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.

If you do not have a TOP clause, you can sort any number of rows with ORDER BY.

If you do not use an ORDER BY clause, you can use any integer value with the TOP operator.

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  

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  

The 8192 row limitation only applies to TOP N where N is a constant, as in the preceding examples. If 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  

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:

  • Using JOINs in the query. The influence of JOINs on the limitation depends on the query plan.

  • Using aggregate functions or references to aggregate functions in the ORDER BY clause.

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 ).

Natively Compiled Stored Procedures
Migration Issues for Natively Compiled Stored Procedures

Community Additions

ADD
Show: