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”
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
USE [Test] GO CREATE PROCEDURE NativelyCompiledSP_Example WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'English') DECLARE @i INT = 0 WHILE @i < 50000 BEGIN INSERT INTO dbo.MemoryOptimizedTable_Example VALUES (@i,N'My_Name') SET @i += 1 END END GO --EXEC NativelyCompiledSP_Example --SQL Server 2016 does not require EXECUTE AS OWNER. |
