Natively Compiled Stored Procedure is a new type of stored procedure compilation introduced in SQL Server 2014.
This type of stored procedure is used for accessing the tables optimized for In-Memory OLTP. They are compiled using native code. The purpose of this type of stored procedures is high performance. As these stored procedures and the tables they are accessing are in memory, they are supposed to be very high performing compared to the regular stored procedures.
There are certain conditions for creating native compiled stored procedure. You have to specify the below options while creating these kind of stored procedure.
- EXECUTE AS [Owner/User/Self]
- BEGIN ATOMIC
Sample Native Compiled Stored procedure:
create procedure dbo.MTB_Insert_Records with native_compilation, schemabinding, execute as owner as begin atomic with (transaction isolation level = snapshot, language = N'English') Declare @count1 as integer Declare @count2 as integer select @count1 = count(*) from MyTecBits_Names_1 select @count2 = count(*) from MyTecBits_Names_2 If (isnull(@count1, 0) = 0 and isnull(@count2, 0) = 0) Begin Declare @i as integer Declare @RandomNo as integer Set @i = 1 while(@i &amp;lt;= 1000000) begin set @RandomNo = cast(rand() * 99 as numeric(2,0)) Insert into MyTecBits_Names_1 values (@i, @RandomNo, 'Name ' + cast(@i as varchar(10))) Insert into MyTecBits_Names_2 values (@i, @RandomNo, 'Name ' + cast(@i as varchar(10))) Set @i = @i+1 set @RandomNo = null End End end go Execute MTB_Insert_Records GO
Features of Natively Compiled Stored Procedure:
- The SQL Server engine identifies this stored procedure to be compiled natively from the option NATIVE_COMPILATION.
- The table referenced inside the native compiled stored procedure cannot be dropped. This is implemented by the SCHEMABINDING.
- You cannot access regular (non memory optimized) tables from these stored procedures. Only memory optimized tables can be accessed. If you try to access them, you will get the below errors. (Read more about memory optimized tables).
- EXECUTE AS [OWNER/<user>/SELF] needs to be specified. Currently in SQL Server 2014 CTP1, EXECUTE AS CALLER, is not supported.
- Natively compiled stored procedures should contain one atomic block using BEGIN ATOMIC option.
- BEGIN ATOMIC needs TRANSACTION ISOLATION LEVEL option and LANGUAGE option.
- BEGIN TRANSACTION and COMMIT TRANSACTION are note needed to be specified explicitly in a natively compiled stored procedure. Transactions are handled by BEGIN ATOMIC option.
- Sub queries are not supported for IF condition and WHILE condition.
- They cannot be altered using ALTER. For modifying a natively compiled stored procedure, it has to be dropped and re-created.
- Natively compiled stored procedures can be created only in a OLTP In-Memory optimized database. If you try to create it in a regular database, it will throw several errors. (Read more about creating memory optimized database and converting regular database to memory optimized database.)