What is Natively Compiled Stored Procedure in SQL Server

The Natively Compiled Stored Procedure is a new type of stored procedure offered by SQL Server 2014 with In Memory OLTP. Available in Memory Optimized Tables only. I suggest you read the article “In Memory OLTP in SQL Server” for “In Memory OLTP” and “Memory Optimized Tables”.

Difference Between Stored Procedure and Natively Compiled Stored Procedure

In the Natively Compiled Stored Procedure, TSQL codes in the Procedure are converted to native code to provide better performance.

In the article “In Memory OLTP in SQL Server“, we have said that we can achieve up to 30x more performance with memory optimized tables than disk based tables.

When we use Memory Optimized Tables in combination with the Natively Compiled Stored Procedure, this difference increases. I suggest you test to see the speed. I will share the necessary code.

The normal stored procedures we know are compiled when they first run. Therefore, they can receive errors during the first run even if they do not receive errors during creation.

Because Natively Compiled Stored Procedures are compiled when they are created, you cannot create if there is an error.

Create Natively Compiled Stored Procedure

If you want to see how fast nativel compiled stored procedure is, you should test it with the below script.

The script creates a natively compiled stored procedure that insert 50000 records to the table that I have created in the article “In Memory OLTP in SQL Server