Reference: Http://www.infoq.com/cn/news/2013/09/Compiled-Queries
Http://www.bianceng.cn/database/SQLServer/201502/48247.htm
The SQL Server 2014 Memory database introduces a new structure for traditional tables and stored procedures: memory optimized table and native stored procedure (natively compiled stored procedures).
Memory-Optimized tables:By default, the memory optimized table is fully durable (that is, durable memory optimized table), as is the case with traditional disk-based tables, and a fully durable transaction is also supported for atomic, consistent, isolated, and durable (ACID) Of The difference is that the primary storage for the entire table of the memory-optimized table is in memory, that is, the rows from the table are read from memory, and the row data is updated into memory. It's not like a traditional disk-based table that loads a database from a database Database page. The data for the memory-optimized table also maintains another copy on disk, but for persistent purposes only. During database recovery, the data in the memory-optimized table is again loaded from disk.
CREATE TABLE durabletbl (accountno INT not NULL PRIMARY KEY nonclustered HASH with (Bucket_count = 28713), CustName varcha R () is not NULL, Gender CHAR is not NULL, CustGroup varchar (4) is not NULL, ADDR varchar () NULL, Phone varchar (+) NULL)
With (Memory_optimized=on, Durability=schema_and_data)-- Persistent memory
With (Memory_optimized=on, durability=schema_only)- non-persistent memory
(in addition to the default persistent memory-optimized tables, the non-durable memory optimized table (non-persisted ram-optimized tables) is not logged and their data is not saved on disk.) This means that transactions on these tables do not require any disk IO, but the data cannot be recovered if the server crashes or fails over. ）
locally compiled stored proceduresNative compiled stored procedure (natively compiled stored procedures) is for traditional stored procedures and is generated after a natively compiled stored procedure, since native compilation refers to the process of converting a programming construct into native code, which consists of a processor directive, No further compilation or interpretation is required. Native compilation improves the speed of data access and the efficiency of query execution compared to traditional tsql. Therefore, it can improve the efficiency of query and business logic processing in stored procedure through natively compiled stored procedure. Syntax Create PROCEDURE dbo. Nativesp_online@saccount nvarchar (+ native_compilation), SCHEMABINDING, EXECUTE as Ownerasbegin ATOMIC with ( TRANSACTION Isolation level = SNAPSHOT, LANGUAGE = N ' 中文版 ') BEGIN Endendgo Note: 1, local compilation stored procedure cannot use subquery, cannot use union, cannot use non-memory optimized table
the in-memory database can contain both memory-optimized tables and locally compiled stored procedures, and can contain disk-based tables and traditional stored procedures
SQL server2014 Memory-optimized tables natively compiled stored procedures
