SQL Server 2014: Natively Compiled Stored Procedure

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.

• NATIVE_COMPILATION
• SCHEMABINDING
• 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 <= 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:

1. The SQL Server engine identifies this stored procedure to be compiled natively from the option NATIVE_COMPILATION.
2. The table referenced inside the native compiled stored procedure cannot be dropped. This is implemented by the SCHEMABINDING.
3. 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).

   

4. EXECUTE AS [OWNER/<user>/SELF] needs to be specified. Currently in SQL Server 2014 CTP1, EXECUTE AS CALLER, is not supported.
5. Natively compiled stored procedures should contain one atomic block using BEGIN ATOMIC option.
6. BEGIN ATOMIC needs TRANSACTION ISOLATION LEVEL option and LANGUAGE option.
7. 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.
8. Sub queries are not supported for IF condition and WHILE condition.
9. They cannot be altered using ALTER. For modifying a natively compiled stored procedure, it has to be dropped and re-created.
10. 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.)

    

Reference: msdn.