Creating a Memory-Optimized Table and a Natively Compiled Stored Procedure

• 03/16/2017
• 5 minutes to read

Applies to: SQL Server (all supported versions)

This topic contains a sample that introduces you to the syntax for In-Memory OLTP.

To enable an application to use In-Memory OLTP, you need to complete the following tasks:

• Create a memory-optimized data filegroup and add a container to the filegroup.
• Create memory-optimized tables and indexes. For more information, see CREATE TABLE (Transact-SQL).
• Load data into the memory-optimized table and update statistics after loading the data and before creating the compiled stored procedures. For more information, see Statistics for Memory-Optimized Tables.
• Create natively compiled stored procedures to access data in memory-optimized tables. For more information, see CREATE PROCEDURE (Transact-SQL). You can also use a traditional, interpreted Transact-SQL to access data in memory-optimized tables.
• As needed, migrate data from existing tables to memory-optimized tables.

Background on In-Memory objects

For information on how to use SQL Server Management Studio to create memory-optimized tables, see SQL Server Management Studio Support for In-Memory OLTP.

Natively compiled stored procedures

Natively compiled stored procedures are Transact-SQL stored procedures compiled to native code, and that access memory-optimized tables. Natively compiled stored procedures allow for efficient execution of the queries and business logic in the stored procedure. For more details about the native compilation process, see Native Compilation of Tables and Stored Procedures. For more information about migrating disk-based stored procedures to natively compiled stored procedures, see Migration Issues for Natively Compiled Stored Procedures.

 Note

One difference between interpreted (disk-based) stored procedures and natively compiled stored procedures is that an interpreted stored procedure is compiled at first execution, whereas a natively compiled stored procedure is compiled when it is created. With natively compiled stored procedures, many error conditions can be detected at create time and will cause creation of the natively compiled stored procedure to fail (such as arithmetic overflow, type conversion, and some divide-by-zero conditions). With interpreted stored procedures, these error conditions typically do not cause a failure when the stored procedure is created, but all executions will fail.

Link to PDF :  Diagnose and resolve latch contention on SQL Server