Supported Features for Natively Compiled T-SQL Modules

  • Article

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

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.

Query Surface Area in Native Modules

The following query constructs are supported:

CASE expression: CASE can be used in any statement or clause that allows a valid expression.

  • Applies to: SQL Server 2017 (14.x).
    Beginning with SQL Server 2017 (14.x), CASE statements are now supported for natively compiled T-SQL modules.

SELECT clause:

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

  • Scalar subqueries

    • Applies to: SQL Server 2016 (13.x). Beginning with SQL Server 2016 (13.x), scalar subqueries are now supported in natively compiled modules.

  • TOP*

  • SELECT DISTINCT

    • Applies to: SQL Server 2016 (13.x). Beginning with SQL Server 2016 (13.x), the DISTINCT operator is supported in natively compiled modules.

      • DISTINCT aggregates are not supported.

  • UNION and UNION ALL

    • Applies to: SQL Server 2016 (13.x). Beginning with SQL Server 2016 (13.x), UNION and UNION ALL operators are now supported in natively compiled modules.

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

    • Applies to: SQL Server 2016 (13.x). Beginning with SQL Server 2016 (13.x), JOINS are now supported in natively compiled modules.

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

    • Applies to: SQL Server 2016 (13.x). Beginning with SQL Server 2016 (13.x), Subqueries are now supported in natively compiled modules.

WHERE clause:

  • Filter predicate IS [NOT] NULL

  • AND, BETWEEN

  • OR, NOT, IN, EXISTS

    • Applies to: SQL Server 2016 (13.x). Beginning with SQL Server 2016 (13.x), OR/NOT/IN/EXISTS operators are now supported in natively compiled modules.

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.

Data Modification

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.

Control-of-flow language

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

Supported Operators

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 ^

  • APPLY operator

    • Applies to: SQL Server 2017 (14.x).
      Beginning with SQL Server 2017 (14.x), the APPLY operator is supported in natively compiled modules.

Built-in Functions in Natively Compiled Modules

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.

    • Applies to: SQL Server 2017 (14.x).
      Beginning with SQL Server 2017 (14.x), the following built-in functions are also supported: TRIM, TRANSLATE, and CONCAT_WS.

  • Identity functions: SCOPE_IDENTITY

  • NULL functions: ISNULL

  • Uniqueidentifier functions: NEWID and NEWSEQUENTIALID

  • JSON functions

    • Applies to: SQL Server 2017 (14.x).
      Beginning with SQL Server 2017 (14.x), the JSON functions are supported in natively compiled modules.

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

Auditing

Procedure level auditing is supported in natively compiled stored procedures.

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

Table and Query Hints

The following are supported:

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

Limitations on Sorting

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

See Also

Natively Compiled Stored Procedures
Migration Issues for Natively Compiled Stored Procedures