Introduction

Natively Compiled Stored Procedures are T-SQL procedures compiled to native code and access memory optimized tables. They allow for the efficient execution of business logic and T-SQL queries within the procedure. Stored Procedures marked as NATIVE_COMPILATION are Natively Compiled Stored Procedures. Natively Compiled Stored Procedures are compiled when they are created. This is the main difference between Natively Compiled Stored Procedures and disk-based (interpreted) Stored Procedure. Disk-based (interpreted) Stored Procedures are compiled when called the first time.

Natively Compiled Stored Procedures are able to identify error conditions like arithmetic overflow, type conversion, and divide-by-zero conditions when they are created. If these errors are present in code, it is not created. A disk-based (interpreted) Stored Procedure are not created due to these errors, they will throw runtime errors. Native compilation allows for faster and more efficient data access than interpreted (traditional or disk based) Transact-SQL.

Examples

The following are examples.

CREATE TABLE TestTable

(

Id INT NOT NULL PRIMARY KEY NONCLUSTERED

HASH WITH (BUCKET_COUNT = 50000),

StartDate DATETIME NOT NULL,

Code VARCHAR(10) NOT NULL,

)

WITH (MEMORY_OPTIMIZED = ON)

GO

CREATE PROCEDURE TestProc

(

@Id INT,

@Code VARCHAR(10)

)

WITH NATIVE_COMPILATION, SCHEMABINDING,

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.

The following expressions are supported by Natively Compiled Stored Procedures.

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

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

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.