In last article “In-Memory OLTP Introduction” we discussed what is in-memory OLTP and how it works. We also got to know memory-optimized tables and natively compiled stored procedures. now we are going to be more familiar with creating in-memory objects.
Create Memory-Optimized Tables
To create memory-optimized tables, your database must contain a filegroup for in-memory OLTP data. To add a filegroup for memory-optimized data to a database you can use the ALTER DATABASE statement, as shown in the following example:[codesyntax lang=”tsql”]
ALTER DATABASE MyDB ADDFILEGROUP mem_data CONTAINS MEMORY_OPTIMIZED_DATA; GO ALTER DATABASE MyDB ADDFILE (NAME='MemData',FILENAME='D:DataMyDB_MemData.ndf') TOFILEGROUP mem_data;
[/codesyntax]After file group and file creation is competed for memory-optimized data, you can create memory-optimized table which can include a hash index and the durability of the table data. By default, the durability option is set to SCHEMA_AND_DATA, so the data is persisted to filestream data which means in case of database server shut down or failure the data will be recovered from memory-optimized filegroup and transaction logs.[info] When the durability option is set to SCHEMA_AND_DATA, the data is written to disk as a stream, not in 8-KB pages as used by disk-based tables. [/info]You can specify the durability option to SCHEMA_ONLY, so only the table definition is persisted that means transactions on these tables do not require any disk I/O, however in the case of database server shut down or failure the data in these tables will be lost. [info]The ability to set the durability option to SCHEMA_ONLY is useful when the table is used for transient data, such as a session state table in a web server farm or data warehouse staging process. [/info]To create a memory-optimized table, execute a CREATE TABLE statement with the MEMORY_OPTIMIZED option set to ON, as shown in the following example:[codesyntax lang=”tsql”]
CREATE TABLE dbo.MemoryTable ( OrderId INTEGER NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1000000), OrderDate DATETIME NOT NULL, ProductCode INTEGER NULL, Quantity INTEGER NULL ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
[/codesyntax][info]All tables with durability option of SCHEMA_AND_DATA must include a primary key either single-column primary key or composite primary key. [/info]
Create Index on Memory-Optimized Tables
To create indexes in addition to the primary key, you must specify the indexes after the column definitions, as shown in the following example:[codesyntax lang=”tsql”]
CREATE TABLE dbo.IndexedMemoryTable ( OrderId INTEGER NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 100000), OrderDate DATETIME NOT NULL, ProductCode INTEGER NULL, Quantity INTEGER NULL INDEX idx_MemTab_OrderDate NONCLUSTERED HASH(OrderDate) WITH (BUCKET_COUNT = 100000) ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
Querying Memory-Optimized Tables
applications can query memory-optimized tables in two ways:
- Query Interop. You can access memory-optimized tables using any Transact-SQL query or DML operation in the same way as traditional disk-based tables. Interop access refers to Transact-SQL batches or stored procedures other than a natively compiled stored procedure. In this way, the SQL Server query engine provides an interop layer to interpret the compiled in-memory table. this technique is useful to access both memory-optimized tables and disk-based tables.
- Native Compilation. Native Compilation increase the performance of workloads that use memory-optimized tables. Memory-optimized tables are compiled when they are created. Natively compiled stored procedures also are compiled when they are loaded to native DLLs. Memory-optimized tables and DLLs are recompiled during the server restart.
[info] Note that the Actual Query Plan is only available for Interop queries, not for Natively Compiled stored procedures. [/info]
Create Natively Compiled Stored Procedure
Stored procedures that are marked with NATIVE_COMPILATION are natively compiled .To define these stored procedures, you must use CREATE PROCEDURE statements that the SQL Server query engine converts to native code, as shown in the following example:[codesyntax lang=”tsql”]
CREATEPROCEDURE [dbo].[usp_InsertData](@c1 int, @c3 int, @c4 int) WITH NATIVE_COMPILATION, SCHEMABINDING AS BEGIN ATOMIC WITH ( TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english' ) DECLARE @c2 datetime = GETDATE(); INSERTINTO [dbo].[MemoryTable](OrderId, OrderDate, ProductCode, Quantity) values (@c1, @c2, @c3, @c4); END GO
[/codesyntax]In this example, NATIVE_COMPILATION determines that this stored procedure is a natively compiled store procedure. In addition, to create a natively compiled stored procedure these options are required:
- BEGIN ATOMIC
The C version of the stored procedure is compiled into a DLL, which is loaded into memory. [info]You can only use natively compiled stored procedures to access memory-optimized tables; they cannot reference disk-based tables. [/info]
In these two articles we discussed about memory-optimized tables and their features. These new data structures are part of SQL Server In-Memory OLTP Engine to achieve significant performance over traditional disk-based data structures. Furthermore, we saw simple examples on how we can define memory-optimized tables and indexes and natively-compiled stored procedures. Hope these two posts were informative, please share it with others if you think it worth to read and stay tuned to learn more about SQL Server.