Skip to content

Latest commit

 

History

History
280 lines (173 loc) · 14.2 KB

supported-features-for-natively-compiled-t-sql-modules.md

File metadata and controls

280 lines (173 loc) · 14.2 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic monikerRange
Features for natively compiled T-SQL modules
Learn about T-SQL surface area and supported features in the body of natively compiled T-SQL modules, like stored procedures and scalar user-defined functions.
MikeRayMSFT
mikeray
07/01/2020
sql
in-memory-oltp
conceptual
=azuresqldb-current||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current

Supported Features for Natively Compiled T-SQL Modules

[!INCLUDE 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: [!INCLUDEsssql17-md].
    Beginning with [!INCLUDEsssql17-md], 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: [!INCLUDEsssql15-md]. Beginning with [!INCLUDEsssql15-md], scalar subqueries are now supported in natively compiled modules.

  • TOP*

  • SELECT DISTINCT

    • Applies to: [!INCLUDEsssql15-md]. Beginning with [!INCLUDEsssql15-md], the DISTINCT operator is supported in natively compiled modules.

      • DISTINCT aggregates are not supported.

  • UNION and UNION ALL

    • Applies to: [!INCLUDEsssql15-md]. Beginning with [!INCLUDEsssql15-md], 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: [!INCLUDEsssql15-md]. Beginning with [!INCLUDEsssql15-md], JOINS are now supported in natively compiled modules.

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

    • Applies to: [!INCLUDEsssql15-md]. Beginning with [!INCLUDEsssql15-md], Subqueries are now supported in natively compiled modules.

WHERE clause:

  • Filter predicate IS [NOT] NULL

  • AND, BETWEEN

  • OR, NOT, IN, EXISTS

    • Applies to: [!INCLUDEsssql15-md]. Beginning with [!INCLUDEsssql15-md], 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 [!INCLUDEtsql] do not apply to interpreted [!INCLUDEtsql] 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: [!INCLUDE sssql17-md].
      Beginning with [!INCLUDE sssql17-md], 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: [!INCLUDE sssql17-md].
      Beginning with [!INCLUDE sssql17-md], 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: [!INCLUDE sssql17-md].
      Beginning with [!INCLUDE sssql17-md], 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