SQL Server 2014 数据内存优化表详解

不同于disk-based table，内存优化表驻留在内存中，使用 Hekaton 内存数据库引擎实现。在查询时，从内存中读取数据行；在更新时，将数据的更新直接写入到内存中。内存优化表能够在disk上维护一个副本，用于持久化数据集。

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

一，创建数据库

内存优化表的必须存储在一个 包含memory-optimized-data的File Group中，一个db只能包含一个，该File Group可以有多个File（File实际上是folder）。

use master
go 

--create database
create database TestMemoryDB

--add filegroup
alter database TestMemoryDB
add filegroup FG_TestMemoryDB
contains memory_optimized_data;

--add file directory go filegroup
alter database TestMemoryDB
add file
(
name='TestMemoryDBDirectory',
filename='D:MSSQLServerDataMSSQL12.MSSQLSERVERMSSQLDATATestMemoryDBDirectory'
)
to filegroup FG_TestMemoryDB;
CONTAINS MEMORY_OPTIMIZED_DATA 子句指定用于存储内存优化表数据的FileGroup。

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

二，创建内存优化表

在SQL Server 2014 的内存优化表中，只能创建nonclustered index 或nonclustered hash index，每个内存优化表中至少创建一个index，最多创建8个index。在内存优化表中，Index必须在Create Table语句中创建，不能使用Create index 命令创建索引，也不能使用drop index 命令删除索引。

You must specify column and table indexes as part of the CREATE TABLE statement. CREATE INDEX and DROP INDEX are not supported for memory-optimized tables.

--create memory optimized table
CREATE TABLE [dbo].[Products]
(
    [ProductID] [bigint] NOT NULL,
    [Name] [varchar](64) not NULL,
    [Price] decimal(10,2) not NULL,
    [Unit] varchar(16) collate Latin1_General_100_BIN2 not null,
    [Description] [varchar](1000) NULL,
CONSTRAINT [PK__Products_ProductID] PRIMARY KEY
nonclustered hash
(    [ProductID] )
WITH (BUCKET_COUNT=2000000)
,index idx_Products_Price  nonclustered([Price] desc)
,index idx_Products_Unit nonclustered hash(Unit) with(bucket_count=40000)
)
with(MEMORY_OPTIMIZED=ON,DURABILITY= SCHEMA_AND_DATA)
GO
1，Durability 持久性

DURABILITY = {SCHEMA_ONLY | SCHEMA_AND_DATA}

The value of SCHEMA_AND_DATA indicates that the table is durable, meaning that changes are persisted on disk and survive restart or failover.  SCHEMA_AND_DATA is the default value.

The value of SCHEMA_ONLY indicates that the table is non-durable. The table schema is persisted but any 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_OPTIMIZED = {ON | OFF}]

The value ON indicates that the table is memory optimized. The default value OFF indicates that the table is disk-based.

3，Hash index

Hash indexes are supported only on memory-optimized tables.

BUCKET_COUNT Indicates the number of buckets that should be created in the hash index.

创建内存优化表的限制

Restriction1：不支持blob 数据类型，每行的size不能超过8060B（一个buffer的大小）

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

Restriction2：Index key不可为null

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

Restriction3：在字符列上创建Index，那么字符列必须使用 BIN2 collation。

Indexes on character columns that do not use a *_BIN2 collation are not supported with indexes on memory optimized tables.

Restriction4：数据类型为（var）char 的column，其code page必须是1252

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

三，创建natively compiled SP

本地编译SP在创建时编译，整个SP以原子方式执行，这意味着，以SP为单位，整个SP中的所有操作是一个原子操作，要么执行成功，要么执行失败。

--create sp
create procedure dbo.usp_GetProduct
@ProductID bigint not null
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
as
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english') 

select  [ProductID]
      ,[Name]
      ,[Price]
      ,[Unit]
      ,[Description]
from [dbo].[Products]
where ProductID=@ProductID

end
go
1，本地编译SP的参数定义中，存在nullability属性，如果参数 指定 not null，那么不能给参数指定null。

NOT NULLconstraints on parameters of and variables in natively compiled stored procedures. You cannot assign NULL values to parameters or variables declared as NOT NULL .

CREATE PROCEDURE dbo.myproc (@myVarchar  varchar(32)  not null ) ...

DECLARE @myVarchar  varchar(32)  not null = "Hello" ; -- (Must initialize to a value.)

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

2，本地编译的SP必须包含两个选项： SCHEMABINDING 和 ATOMIC block

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

ATOMIC block：All statements in ATOMIC blocks, which are required with natively compiled stored procedures, always run as part of a single transaction - either the actions of the atomic block as a whole are committed, or they are all rolled 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 will start a new transaction, which commits at the end of the atomic block.

Atomic blocks in natively compiled stored procedures have two required options:

TRANSACTION ISOLATION LEVEL . See Transaction Isolation Levels for Memory-Optimized Tables for supported isolation levels.

LANGUAGE. The language for the stored procedure must be set to one of the available languages or language aliases.

3，解释型SP和本地编译SP的区别是Interpreted SP在第一次执行时编译，而natively compiled SP是在创建时编译。

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.

4，延迟持久化

在本地编译SP中，设置 DELAYED_DURABILITY = ON ，那么SP对内存优化表的更新操作，将会延迟持久到Disk。这意味着，如果内存优化表维护了一个Disk-based 的副本，数据行在内存中修改之后，不会立即更新到Disk-based 的副本中，这有丢失数据的可能性，但是能够减少Disk IO，提高数据更新的性能。

Appendix：创建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 ] 
  WITH NATIVE_COMPILATION, SCHEMABINDING [ , EXECUTE AS clause ] 
AS 
{ 
  BEGIN ATOMIC WITH (set_option [ ,... n ] ) 
sql_statement [;] [ ... n ] 
 [ END ] 
} 
 [;] 
 
::= 
    LANGUAGE =  [ N ] 'language' 
  | TRANSACTION ISOLATION LEVEL =  { SNAPSHOT | REPEATABLE READ | SERIALIZABLE } 
  | [ DATEFIRST = number ] 
  | [ DATEFORMAT = format ] 
  | [ DELAYED_DURABILITY = { OFF | ON } ]