SQL Server 2014 memory-Optimized tables

Unlike disk-based table, memory-optimized tables reside in memory and are implemented using the Hekaton in-memory database engine. When the query is queried, the data rows are read from memory, and updates are written directly to memory when they are updated. Memory-Optimized tables can maintain a copy on disk for persisting datasets.

memory-optimized tables reside in memory. The Rows in the table is read from and written to memory. The entire table resides in memory. A second copy of the table data is maintained on disk and only for durability purposes.

One, create a database

Memory-Optimized tables must be stored in a file group containing memory-optimized-data, where one db can contain only one, and the file group may have multiple file (file is actually folder).

 UseMasterGo  --CREATE DATABASECreate DatabaseTestmemorydb--Add FilegroupAlter DatabaseTestmemorydbAddFilegroup Fg_testmemorydbcontainsMemory_optimized_data;--Add File directory go filegroupAlter DatabaseTestmemorydbAdd file(Name='testmemorydbdirectory', filename='D:\MSSQLServerData\MSSQL12. Mssqlserver\mssql\data\testmemorydbdirectory') toFilegroup Fg_testmemorydb;

The CONTAINS memory_optimized_data clause specifies the filegroup used to store memory-optimized table data.

Specifies the filegroup stores memory_optimized data in the file system. Only one memory_optimized_data filegroup is allowed per database.

Second, create a memory-optimized table

In SQL Server 2014 memory-optimized tables, you can only create nonclustered index or nonclustered hash index, creating at least one index per memory-optimized table and creating up to 8 index. In a memory-optimized table, index must be created in the CREATE TABLE statement, cannot be indexed using the CREATE INDEX command, or can be dropped using the DROP INDEX command.

You must specify column and table indexes as part of the CREATE table statement. CREATE Index and DROP index is not supported for memory-optimized tables.

--Create memory optimized tableCREATE TABLE [dbo].[ Products](    [ProductID] [bigint]  not NULL,    [Name] [varchar]( -) not NULL,    [ Price] decimal(Ten,2) not NULL,    [Unit] varchar( -) Collate latin1_general_100_bin2 not NULL,    [Description] [varchar]( +)NULL,CONSTRAINT [Pk__products_productid] PRIMARY KEY nonclusteredHash ([ProductID] ) with(Bucket_count=2000000),IndexIdx_products_pricenonclustered([ Price] desc),IndexIdx_products_unitnonclusteredHash (Unit) with(Bucket_count=40000)) with(memory_optimized= on, durability=schema_and_data)GO

1,durability Persistence

Durability = {Schema_only | Schema_and_data}

The value of Schema_and_data indicates, the table is durable, meaning that changes be persisted on disk and survive R  Estart or failover. Schema_and_data is the default value.

The value of Schema_only indicates, the table is non-durable. The table schema is persisted if the data updates are not persisted upon a restart or failover of the database. Durability=schema_only is only allowed with Memory_optimized=on.

2,memory_optimized memory optimization Properties

[memory_optimized = {on | OFF}]

The value on indicates this table is memory optimized. The default value OFF indicates the table is disk-based.

3,Hash index

Hash indexes is supported only on memory-optimized tables.

Bucket_count indicates the number of buckets that should is created in the hash index.

Limitations of creating memory-optimized tables

Restriction1: BLOB data type not supported, size of each row cannot exceed 8060B (one buffer)

The row size limit of 8060 bytes for memory optimized tables have been exceeded. Please simplify the table definition.

Restriction2:index key cannot be null

Nullable columns in the index key is not supported with indexes on memory optimized tables.

Restriction3: Create index on the character column, the character columns must use BIN2 collation.

Indexes on character columns This does not use a *_bin2 collation is not supported with Indexes on memory optimized tables.

Restriction4: Column with a data type of (Var) char, whose code page must be 1252

The data types char (n) and varchar (n) using a collation that have a code page other than 1252 is not supported with memory Optimized tables.

Third, create natively compiled SP

The locally compiled SP is compiled at creation time, and the entire SP is executed atomically, which means that all operations in the entire SP are an atomic operation, either successful or failed, in SP.

--Create SPCreate proceduredbo.usp_getproduct@ProductID bigint  not NULL withNative_compilation, SCHEMABINDING,EXECUTE  asOWNER asBEGINATOMIC with(TRANSACTION Isolation  Level =SNAPSHOT, LANGUAGE=N'us_english')  Select  [ProductID]      ,[Name]      ,[ Price]      ,[Unit]      ,[Description] from [dbo].[ Products]whereProductID=@ProductIDEndGo 

1, the nullability attribute exists in the parameter definition of the locally compiled SP, and if the parameter specifies not NULL, the parameter cannot be specified as null.

Not NULL constraints on parameters of and variables in natively compiled stored procedures. You cannot assign null values to parameters or variables declared as is not null.

• CREATE PROCEDURE Dbo.myproc (@myVarchar varchar (+) not null) ...

• DECLARE @myVarchar varchar (+) not null = "Hello"; -- (must initialize to a value.)

• SET @myVarchar = null; -- (compiles, but fails during run time.)

2, the locally compiled SP must contain two options:SCHEMABINDING and ATOMIC block

SCHEMABINDING: A natively compiled stored procedure must is bound to the schema of the objects it references.

ATOMIC block: All statements in ATOMIC blocks, which is required with natively compiled stored procedures, always Run as part of a single transaction-either the actions of the atomic block as a whole is committed, or they is all RO Lled back, in case of a failure.

The natively compiled stored procedure body must consist of exactly one atomic block. Atomic blocks guarantee Atomic execution of the stored procedure. If The procedure is invoked outside the context of an active transaction, it'll start a new transaction, which commits a t the end of the atomic block.

Atomic blocks in natively compiled stored procedures has both required options:
TRANSACTION isolationlevel. See Transaction isolation levels for memory-optimized Tables for supported isolation levels.
LANGUAGE. The language for the stored procedure must is set to one of the available languages or language aliases.

3, the difference between an interpreted SP and a locally compiled SP is that the interpreted SP was compiled the first time it was executed, while the natively compiled SP was compiled at the time of creation.

One difference between interpreted (disk-based) stored procedures and natively compiled stored procedures is this an inter Preted stored procedure is compiled at first execution, whereas a natively compiled stored procedure was compiled when it I S created. With natively compiled stored procedures, many error conditions can is detected at create time and would cause creation of The natively compiled stored procedure to fail (such as arithmetic overflow, type conversion, and some Divide-by-zero cond itions). With interpreted stored procedures, these error conditions typically don't cause a failure when the stored procedure is C reated, but all executions would fail.

4, Delayed persistence

In the locally compiled SP, set delayed_durability = ON, the SP's update to the memory-optimized table will delay persisting to disk. This means that if the memory-optimized table maintains a copy of the disk-based, the data rows are modified in memory and are not immediately updated to the disk-based copy, which has the potential to lose data, but can reduce disk IO and improve the performance of data updates.

Appendix: syntax for creating natively compiled SP

--Syntax for SQL Server natively Compiled Stored procedures  CREATE{PROC | PROCEDURE}[schema_name.]procedure_name[{@parameter data_type} [NULL | Not NULL] [= Default] [Out | OUTPUT] [READONLY]][,... N]     withNative_compilation, SCHEMABINDING[, EXECUTE as clause]   as  {    BEGINATOMIC with(set_option[,... N]) sql_statement[;] [. .. n]   [END]  }   [;]    <Set_option>::=LANGUAGE=  [N] 'language'    | TRANSACTION Isolation  Level ={SNAPSHOT| Repeatable READ | SERIALIZABLE }    | [Datefirst = number]    | [dateformat = Format]    | [delayed_durability = {OFF | on}]  

Reference Documentation:

In-memory OLTP (in-memory optimization)

Introduction to Memory-optimized Tables

Natively Compiled Stored Procedures

Memory-optimized Tables

Try SQLSERVER2014 memory-Optimized tables

SQL Server 2014 memory-optimized tables

SQL Server 2014 Memory-Optimized tables (1) Implement memory-optimized tables

CREATE TABLE (Transact-SQL)

CREATE PROCEDURE (Transact-SQL)

Creating natively Compiled Stored procedures

SQL Server 2014 memory-Optimized tables