How to create Natively compiled stored procedures.

   

     Microsoft's definition for Natively compiled stored procedures is, Natively compiled stored procedures are T-SQL strored procedures compiled to native code that access memory-optimized tables. Native compilation is introduced by In-Memory OLTP tables. SQL Server can natively compile memory-optimized tables and stored procedures that use memory-optimized tables.

    Natively compile stored procedures can access to data in memory-optimized tables faster by using more efficient query executions. SQL Server produces DLL files when you create native compilations of tables and Stored Procedures. DLL files will get produced when you create the stored procedures. This doesn't mean that these DLL files are part of your database. DLLs will get recreated/recompiled after database or server restart. Also DLL files will not be included any backups. DLL Files will be deleted automatically if you will delete the stored procedure or the in-memory table it uses. In this way, DBAs do not need to maintain them.After a backup, all required DDLs will be created by SQL Server.

To make a stored procedure Natively compiled stored procedure, you need to mark it as NATIVE_COMPILATION. That will force SQL Server to compile the stored procedure statements to native code for efficient execution. Natively Compiled DLLs stays under the folder MSSQL\Binn\Xtp.

    You should consider using natively compiled stored procedure in performance-critical parts of your applications. You will see difference specially If you have frequently executed stored procedures. In-Memory tables have a lot of limitations and your T-SQL must be supported by In-Memory tables to be able to compile the stored procedures in native code.

Here is an example of Natively compiled stored procedure.

CREATE PROCEDURE dbo.LoadProduct (@ProductId int)
    with native_compilation, schemabinding,
    execute as owner
as
begin atomic
   with (transaction isolation level= snapshot, language = N'us_engligh')
   SELECT ProductId, ProductName, ProductPrice
   FROM Products
   WHERE ProductId = @productid
END
GO