SQL Server 2014 Memory table

In-memory database, which refers to the direct manipulation of database data in memory. Compared to the storage on the disk, memory data read and write speed is much higher, so it can improve the performance of the application. Microsoft's SQL Server 2014 was officially released on April 1, 2014, and the SQL 20,141 main feature is the in-memory database.

Currently, database mirroring and replication are not compatible with memory-optimized tables, but AlwaysOn, log shipping, backup restore is full support.

Because the memory table data storage mechanism and the ordinary table (disk-based table) is completely different, so the memory table data needs a special folder (note not file OH) to store

Use [master]--to create Database  --[Testdb]on PRIMARY (NAME = n ' TestDB ', FILENAME = N ' D:\SQL2104\SQLData\TestDB.mdf ', SIZE = 204800KB, MAXSIZE = UNLIMITED, filegrowth = 204800KB) LOG on (NAME = n ' testdb_log ', FILENAME = N ' D:\SQL2104\ Sqldata\testdb_log.ldf ', SIZE = 204800KB, MAXSIZE = 2048GB, filegrowth = 204800KB) go--create memory table using the filegroup alter DATABASE [TestD B] Add FILEGROUP [TESTDB_MFG1] CONTAINS memory_optimized_datago--create memory table using the folder alter DATABASE [TestDB] Add FILE (NAME = N ' Te Stdb_mdir1 ', FILENAME = N ' D:\SQL2104\SQLData\TestDB_MDir1 ') to FILEGROUP [Testdb_mfg1]go

For each database, you can create only one memory-optimized filegroup (memory-optimized Filegroup), and for each memory-optimized filegroup You create multiple associated folders.

PS: Memory-optimized filegroup base with FILESTREAM filegroup, but no need for memory-optimized filegroups to enable FileStream.

Create a Memory table

Create memory table Limits Compare more:

1. Supported Data types: http://msdn.microsoft.com/ZH-CN/library/dn133179 (v=sql.120). aspx

2. Memory-Optimized tables support self-increment, but the only values allowed for seed and increment are 1, and (n) are the default values for seed and increment;

3. The memory-optimized table does not support check constraints, nor on non-hash index columns;

4. Memory-Optimized tables do not support the use of a collation that has a code page that is not 1252 of the data type char (n) and varchar (n);

5. Memory-Optimized tables do not support nvarchar (max) or varchar (max)

6. The memory-optimized table index does not support indexes on character columns that do not use the *_bin2 collation, nor on non-hash indexed columns;

7. Indexes on memory-optimized tables do not support nullable columns in index keys, nor can they be on non-hash indexed columns;

8. Memory-Optimized tables must have at least one index or primary key

Create a memory-optimized table demo

CREATE TABLE [dbo]. [Tb1_im] (    [C1] [int] not NULL IDENTITY (PRIMARY) "KEY     nonclustered HASH with (bucket_count=1000000),     [C2] [nchar] (  COLLATE chinese_prc_stroke_90_bin2 not NULL     INDEX ix_c2 nonclustered HASH with (bucket_count=1000000),    [C3] [nvarchar] (COLLATE) chinese_prc_stroke_90_bin2 not NULL,    [C4] [nvarchar] (COLLATE) chinese_prc_stroke_90_bin2 not NULL,    INDEX ix_c3_c4 nonclustered HASH (C3,C4) with (bucket_count= 1000000),    INDEX ix_c2_c3  nonclustered (C2,C3)) with (memory_optimized = on, durability = Schema_and_data)

About data Durability

For memory-optimized tables, there are two types of persistence to choose from: schema_only (non-persistent table) and Schema_and_data (persistent table), schema_only option causes the data to be lost after the instance restarts, and for schema_and_data (persistent table), You can also set full or deferred persistence, and the deferred persistence option allows you to increase performance by not immediately writing logs to disk when a transaction commits, but at the cost of potentially losing data in the event of a failure.

SQL Server 2014 Memory table