Natively Compiled Stored Procedures in SQL Server 2014

EXECUTE AS OWNER

AS

BEGIN ATOMIC WITH

(

TRANSACTION ISOLATION LEVEL = SNAPSHOT,

LANGUAGE = N'English'

)

INSERT INTO TestTable (Id, StartDate, Code)

VALUES (@Id, @Code, GETDATE())

END

SCHEMABINDING: SCHEMABINDING is only supported in SQL Server version 2014. Natively Compiled Stored Procedure must be bound to the schema of the objects used in the procedure. So tables cannot be dropped that are referenced by the procedure.

EXECUTE AS: A Natively Compiled Stored Procedure does not support EXECUTE AS CALLER (default execution context). So it is mandatory to specify the execution context when it is created. EXECUTE AS OWNER, EXECUTE AS [user] and EXECUTE AS SELF are supported.

BEGIN ATOMIC: The Natively Compiled Stored Procedure body must be consisting of exactly one atomic block. This will ensure, if the Stored Procedure is invoked outside the active transaction context, that it will start a new transaction. An ATOMIC block must have two required options TRANSACTION ISOLATION LEVEL and LANGUAGE.

A Natively Compiled Stored Procedure does not support all T-SQL programmability. There are certain T-SQL statements, those that cannot be used within Natively Compiled Stored Procedures.

The following programmability is supported by Natively Compiled Stored Procedures.

• IF and WHILE is supported with Natively Compiled Stored Procedure.
• Sub Query is not supported.
• With WHERE and HAVING clause, operator “AND” and “BETWEEN” are supported but OR, NOT and IN are not supported.
• TRY / CATCH / THROW is supported with Natively Compiled Stored Procedure.
• SELECT, SET and RETURN are supported with Natively Compiled Stored Procedure.
• We can use Memory-optimized table types and table variables with Natively Compiled Stored Procedure.
• We can declare variable as NULL or NOT NULL. If a variable is declared as NOT NULL then the declaration must have an initializer. If a variable is declared as NULL then an initializer is optional.

The following expressions are supported by Natively Compiled Stored Procedures.

• Logical AND, OR and NOT (OR and NOT operator is not supported with WHERE and HAVING clause).
• Comparison Operators: =, <>, > , < , >= and <=
• Binary Operators: +,-, * and /

The following built-in functions are supported by Natively Compiled Stored Procedures:

• Conversion function: CAST and CONVERT (Note: Conversions between Unicode (n(var)char) and non-Unicode character strings ((var)char) are not supported.
• String functions: LTRIM, RTRIM, SUBSTRING and LEN
• Date functions: DATEADD, DATEDIFF, DATEFROMPARTS, DATEPART, SMALLDATETIMEFROMPARTS, DATETIME2FROMPARTS, DATETIMEFROMPARTS, CURRENT_TIMESTAMP, DAY, GETDATE, GETUTCDATE, MONTH, EOMONTH, YEAR, SYSDATETIME and SYSUTCDATETIME
• Unique Identifier functions : NEWID and NEWSEQUENTIALID
• Error functions: ERROR_MESSAGE, ERROR_NUMBER, ERROR_LINE,ERROR_PROCEDURE, ERROR_SEVERITY, and ERROR_STATE
• Math functions: SIN, ASIN, COS, ACOS, TAN, ATAN, ATN2, COT, DEGREES, EXP, LOG, LOG10, PI, POWER, SQRT, SQUARE and RAND
• Other functions: SCOPE_IDENTITY and ISNULL and @@ROWCOUNT

The following Query Statements are supported with Natively Compiled Stored Procedures.

• The keywords BETWEEN, IS NULL and IS NOT NULL are supported in Natively Compiled Stored Procedures.
• INNER JOIN, CROSS JOIN, FORCE ORDER and INNER LOOP JOIN query hints are supported with Natively Compiled Stored Procedure.
• The keywords SELECT, UPDATE and DELETE are supported with Natively Compiled Stored Procedures.
• FROM [memory optimized table] and ORDER BY clauses are supported.
• GROUP BY is supported with the aggregate functions AVG, COUNT, COUNT_BIG, MIN, MAX, and SUM. MIN and MAX are not supported with type nvarchar, char, varchar, varchar, varbinary, and binary.
• INSERT VALUES and INSERT SELECT statement are supported.
• INDEX and FORCESEEK hints (both table hints and OPTION clause of the query) are supported.
• ORDER BY and TOP are supported with some limitation like DISTINCT is not supported with SELECT or ORDER BY clause, WITH TIES or PERCENT is not supported with TOP clause.
• ORDER BY combine with TOP does not supported if result set have more than 8192 rows.
• If result set is more than 8192 rows, it throws the error message:
  
  Msg 41398, Level 16, State 1, Procedure testProc, Line 5 The TOP operator can return a maximum of 8192 rows; 9000 was requested.
  
• Column name aliases are supported
• HAVING clause is supported with some limitation like WHERE clause.

Conclusion

In memory Online Transaction Processing (OLTP) introduced the concept of native compilation. Native compilation allows efficient query execution and faster data access than the disk base (traditional) T-SQL. Native compilation Stored Procedure produce DLLs.